What kind of optimisation is done on the WHERE clause?

(Incomplete, MySQL does a lot of optimisations.)

The first issue about making a slow SELECT ... WHERE faster is to check if one could add an index. All references between different tables should usually be done with indexes. One can use the EXPLAIN command to check which indexes are used in a select. See section EXPLAIN syntax. Get information about a SELECT.. See section How does MySQL use indexes?

• Brace removal (all unnecessary braces are removed). ((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b) OR (a AND b AND c AND d)
• Constant folding. (a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5
• Constant condition removal (needed because of constant folding). (b>=5 AND b=5) OR (b=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6
• All constant expressions used by indexes are evaluated only once.
• CONST(*) on a single table without a WHERE is retrieved directly from the table. This is also done for any NOT NULL expression under the same conditions.
• Early detection of invalid constant expressions. Return zero rows if impossible select.
• HAVING is merged with WHERE if one doesn't use GROUP BY or group functions.
• For each sub join a simpler WHERE is constructed to get a fast WHERE evaluation for each sub join and also to skip records as soon as possible.
• Find all indexes that may be used. Use the index which finds least records. An index is used for the following cases: =, >, >=, <, <=, BETWEEN and a LIKE with a character prefix like 'something%'.
• Remove indexes that don't span all AND levels.
  • have all preceding index_parts specified.
  • index = 1 or A = 10 -> NULL (can't use index.)
  • index = 1 or A = 10 and index=2 -> index = 1 OR index = 2
  • index_part_1 = const and index_part_3 = const -> index_part_1 = const
• Read all constant tables. A constant table is:
  1. A table with 0 or 1 record.
  2. A table which uses only other const tables and constants on a full unique index.
  const_table.index = constant
  • const_table.index_part_1 = const_table2.column and const_table.index_part_2 = constant
• Find the best join combination to join the tables, by trying all possibilities :(. If all columns in ORDER BY and in GROUP come from the same table, then this table is preferred first when joining.
• If there is an order clause and a different group clause, or if the order or group contains columns from other tables than the first table in the join queue, a temporary table is created.
• For each table use a ranged index, if possible, to read records. Each table index is queried and if there is an index range that spans < 30% of the records then an index is used. If no such index can be found a quick table scan is used.
• Before each record is output, skip those that match the HAVING clause.

Go to the first, previous, next, last section, table of contents.