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
