Today's Question:  What does your personal desk look like?        GIVE A SHOUT

How MySQL optmizes ORDER BY

  Peter        2012-11-13 11:01:05       2,805        0    

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 

Share on Facebook  Share on Twitter  Share on Weibo  Share on Reddit 

  RELATED


  0 COMMENT


No comment for this article.