## Use of Indexes to Satisfy ORDER BY 

•Influencing ORDER BY Optimization 

•ORDER BY Execution Plan Information Available 

In some cases, MySQL may use an index to satisfy an ORDER BY  clause and avoid the extra sorting involved in performing a filesort  operation. 

The index may also be used even if the ORDER BY  does not match the index exactly, as long as all unused portions of the index and all extra ORDER BY  columns are constants in the WHERE  clause. If the index does not contain all columns accessed by the query, the index is used only if index access is cheaper than other access methods. 

Assuming that there is an index on ( key_part1 , key_part2 ) , the following queries may use the index to resolve the ORDER BY  part. Whether the optimizer actually does so depends on whether reading the index is more efficient than a table scan if columns not in the index must also be read. 

•In this query, the index on ( key_part1 , key_part2 )  enables the optimizer to avoid sorting: 

SELECT * FROM t1 ORDER BY key_part1 , key_part2 ; 

However, the query uses SELECT * , which may select more columns than key_part1  and key_part2 . In that case, scanning an entire index and looking up table rows to find columns not in the index may be more expensive than scanning the table and sorting the results. If so, the optimizer probably does not use the index. If SELECT *  selects only the index columns, the index is used and sorting avoided. 

If t1  is an InnoDB  table, the table primary key is implicitly part of the index, and the index can be used to resolve the ORDER BY  for this query: 

SELECT pk , key_part1 , key_part2  FROM t1 ORDER BY key_part1 , key_part2 ; 

•In this query, key_part1  is constant, so all rows accessed through the index are in key_part2  order, and an index on ( key_part1 , key_part2 )  avoids sorting if the WHERE  clause is selective enough to make an index range scan cheaper than a table scan: 

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

•In the next two queries, whether the index is used is similar to the same queries without DESC  shown previously: 

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

•Two columns in an ORDER BY  can sort in the same direction (both ASC , or both DESC ) or in opposite directions (one ASC , one DESC ). A condition for index use is that the index must have the same homogeneity, but need not have the same actual direction. 

If a query mixes ASC  and DESC , the optimizer can use an index on the columns if the index also uses corresponding mixed ascending and descending columns: 

