Bookmark and Share
MySQL Query Processing Rules
(Publish Date: 2009-8-3 12:52pm, Total Visits: 2246, Today: 1, This Week: 1, This Month: 11)

1. MySQL will not use an index if it decides that it would be faster to simply scan the entire table.

   Generally, if an index tells MySQL to access roughly 30 percent of the rows in a table, it abandon the index and simply perform a full table scan.

2. If multiple indexes may be used to satisfy a query, MySQL will use the most restrictive one

   that is, the one which results in the fewest rows being fetched.

3. If the columns you are selecting are all part of an index, MySQL may read all the data you need directly from the index and never touch (or lock) the table itself.

4. When joining several tables, MySQL will read data from the table that is likely to return the fewest rows first.

   The order in which you specify the tables may not be the same order in which MySQL uses them. This also affects the order
   in which the rows are ultimately returned to you, so be sure to use an ORDERBY clause in your query if you need the rows in a particular order.

5. When MySQL uses indexes (examples)

 -- Using >, >=, =, <, <=, IF NULL and BETWEEN on a key.
       SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
       SELECT * FROM table_name WHERE key_part1 IS NULL;

 -- When you use a LIKE that doesn't start with a wildcard.
       SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'

 -- Retrieving rows from other tables when performing joins.
       SELECT * from t1,t2 where t1.col=t2.key_part

 -- Find the MAX() or MIN() value for a specific index.
       SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

 -- ORDER BY or GROUP BY on a prefix of a key.
       SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3

 -- When all columns used in the query are part of one key.
       SELECT key_part3 FROM table_name WHERE key_part1=1

6. When MySQL doesn't use an index (examples)

 -- Indexes are NOT used if MySQL can calculate that it will probably be faster to scan the whole table.
    For example if key_part1 is evenly distributed between 1 and 100, it's not good to use an index in the following query:
       SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90

 -- If you are using HEAP tables and you don't search on all key parts with =

 -- When you use ORDER BY on a HEAP table

 -- If you are not using the first key part
       SELECT * FROM table_name WHERE key_part2=1;

 -- If you are using LIKE that starts with a wildcard
       SELECT * FROM table_name WHERE key_part1 LIKE '%jani%';

 -- When you search on one index and do an ORDER BY on another
       SELECT * from table_name WHERE key_part1 = # ORDER BY key2;