•The best join combination for joining the tables is found by trying all possibilities. If all columns in ORDER BY  and GROUP BY  clauses come from the same table, that table is preferred first when joining. 

•If there is an ORDER BY  clause and a different GROUP BY  clause, or if the ORDER BY  or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created. 

•If you use the SQL_SMALL_RESULT  modifier, MySQL uses an in-memory temporary table. 

•Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size. 

•In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query. 

•Before each row is output, those that do not match the HAVING  clause are skipped. 

Some examples of queries that are very fast: 

SELECT COUNT(*) FROM tbl_name ; SELECT MIN( key_part1 ),MAX( key_part1 ) FROM tbl_name ; SELECT MAX( key_part2 ) FROM tbl_name WHERE key_part1 = constant ; SELECT ... FROM tbl_name ORDER BY key_part1 , key_part2 ,... LIMIT 10; SELECT ... FROM tbl_name ORDER BY key_part1  DESC, key_part2  DESC, ... LIMIT 10; 

MySQL resolves the following queries using only the index tree, assuming that the indexed columns are numeric: 

SELECT key_part1 , key_part2  FROM tbl_name  WHERE key_part1 = val ; SELECT COUNT(*) FROM tbl_name WHERE key_part1 = val1  AND key_part2 = val2 ; SELECT key_part2  FROM tbl_name  GROUP BY key_part1 ; 

The following queries use indexing to retrieve the rows in sorted order without a separate sorting pass: 

## 8.2.1.2 Range Optimization 

SELECT ... FROM tbl_name ORDER BY key_part1 , key_part2 ,... ; SELECT ... FROM tbl_name ORDER BY key_part1  DESC, key_part2  DESC, ... ; 

The range  access method uses a single index to retrieve a subset of table rows that are contained within one or several index value intervals. It can be used for a single-part or multiple-part index. The following sections describe conditions under which the optimizer uses range access. 

•Range Access Method for Single-Part Indexes 

•Range Access Method for Multiple-Part Indexes 

