How MySQL optmizes ORDER BY

3,581 0
Summary

MySQL frequently optimizes ORDER BY and GROUP BY operations by directly utilizing existing indexes, thereby eliminating the need for extra sorting. This optimization can occur even if the ORDER BY clause doesn't perfectly align with the index, as long as any non-indexed portions are covered by the WHERE clause. However, index usage for sorting is bypassed in several scenarios, such as when ordering by different index keys, discontinuous key parts, mixing ASC and DESC directions, or when the search key differs from the ORDER BY key. Developers can confirm whether an index is used for sorting by examining the EXPLAIN output; the presence of "Using filesort" in the Extra column indicates that MySQL could not leverage an index for the ORDER BY clause.

In some situations, MySQL will just use an index to fulfill the requirement of an ORDER BY or GROUP BY statement without extra sorting.

Although ORDER BY will not have the exact match with index, index can still be used as long as the portion that is not included in the index is included in the where clause.

The following queries will all use index to process 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 some situations, MySQL will not use index to fulfill ORDER BY. For example:

1> For different index keys

SELECT * FROM t1 ORDER BY key1, key2;

2> For discontinuous key parts

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

3> Using ASC and DESC at the same time

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

4> The index key used for searching is different from the one in ORDER BY

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

Actually we can check whether MySQL uses index in searching by executing EXPLAIN SELECT ...ORDER BY. If the value for Extra is Using filesort, then it means that MySQL cannot use index.

Source : http://blog.csdn.net/apache6/article/details/2812410

MYSQL OPTIMIZATION INDEX ORDER BY

  RELATED

  COMMENTS

0

No comment for this article.