Also, MySQL is not the strongest DB in the world when it comes to index usage. It has many limitations not found in the professional products (in particular, in SQL Server and Oracle). You often have to pick and choose your indexes VERY carefully with MySQL in order to get optimal performance.
One set of rules:
-- If you have a composite index (example: an index on both LastName and FirstName), then MySQL can only take advantage of it IN THE ORDER that the fields of the index appear. In this example, if you used
WHERE FirstName = 'John' and did not mention LastName in the WHERE clause, then MySQL could *NOT* use that index.
-- MySQL can not use LIKE with an index unless you use
WHERE field LIKE 'startswith%'. That is, you can only use the % wildcard on the END of the LIKE. If you did
WHERE field LIKE '%contains%' then MySQL won't even try to use the index.
Both of these can put severe restraints on your index usage, and there are more quirky MySQL-only rules. So about the best you can do is create a design and then test it. Luckily, MySQL provides the EXPLAIN statement to help you understand what it does when it runs a query.
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.