Mysql – List population of provices (SQL Query)

Good start so far. Here are some notes:

1. a movie can be released on different dates in different cities
2. please add your primary keys, so we can see them
3. are images for movies? people? why no order column?
4. movies schedules are more complex than you've got so far. You will want to calculate show times based on some recurrence rules (see stackoverflow for recurrence posts)
5. what is 'location' in theatres? the address?

Regarding your questions at the bottom, don't loop. You can almost always do everything using SQL and its set logic. Make sure you understand joins:

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Yes, the same concept could be used. What you have done is re-implement table partition, but in user space. Most industrial-strength RDBMSs will have this built in. The provided functionality often includes additional abilities, such as efficiently adding and removing partitions at run time without applicaiton changes. By choosing to roll your own you miss out on these additional features. Additionally you complicate some things, such as surrogate ID uniqueness checking, aggregate queries across your whole user community and DRI referencing the "user" table.

Be aware that your sub-tables are very unlikely to be well balanced. There aren't many Mr. Aardvark or Ms. Zymology in the world but a lot of Smiths and Jones.

The reason key lookups are faster on smaller tables is because the indexes have fewer levels, assuming you have B-Tree indexes. Therefore the DBMS has to read fewer pages to get from the index's root node to its leaf & data pages. The index on your 676 sub-tables is likely to be only one or two levels deep and so incur only one or two page reads to read a key's row. In contrast a full B-Tree built on 1M rows may be three or four levels deep and require that many page reads per lookup. Built-in partitioning can give you similar benefits if you define your index as partitioned, too.

This is a good reason to keep your index keys compact, if you have a choice. For example, if your user name is 30 characters long this will take 30 bytes to store and you will get a certain number on a page. If instead you calculate an integer hash (4 bytes) of the user name and index that, there will be 30/4 = 7-ish times more rows per index page and the index will likely have fewer levels. (Of course you will have to account for potential hash collisions.) Similarly limiting the amount of free space you have in the index to allow for inserts will help increase density.