MySQL Manual Version 5.0.20-MySQL Optimization (4) (1)_PHP Tutorial

7.2.9 How MySQL optimizes ORDER BY

In some cases, MySQL can directly use the index to satisfy an ORDER BY or GROUP BY clause without doing additional sorting.

Even though the ORDER BY does not exactly match the order of the index, the index can still be used, as long as the unused index portion and all additional ORDER BY fields are used in the WHERE clause Included. The following queries will use indexes to solve the ORDER BY or GROUP BY part:

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;

SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2;

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1

WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

In other cases, MySQL cannot use the index to satisfy ORDER BY, although it will use Index to find records matching the WHERE clause. These situations are as follows:

Do ORDER BY on different index keys:

SELECT * FROM t1 ORDER BY key1, key2;

Order BY on non-consecutive index key parts:

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

Use both ASC and DESC:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

The index key used to search for records is not the same as the one used for ORDER BY:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

There are many tables being joined together, and not all the fields in the ORDER BY in the records read are From the first non-constant table (that is, the join type of the first table in the results of the EXPLAIN analysis is not const).