Optimizing SELECT Statements •Influencing ORDER BY Optimization •ORDER BY Execution Plan Information Available •In this query, the index on  ( key_part1 ,  key_part2 )  enables the optimizer to avoid sorting: •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: •In the next two queries, whether the index is used is similar to the same queries without  DESC  shown previously: •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. 1680 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: SELECT  pk ,  key_part1 ,  key_part2  FROM t1 ORDER BY  key_part1 ,  key_part2 ; 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: 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. SELECT * FROM t1 ORDER BY  key_part1 ,  key_part2 ; 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. 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. 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. Use of Indexes to Satisfy ORDER BY SELECT * FROM t1 WHERE  key_part1  =  constant ORDER BY  key_part2 ; SELECT * FROM t1 ORDER BY  key_part1  DESC,  key_part2  DESC; SELECT * FROM t1 WHERE  key_part1  =  constant ORDER BY  key_part2  DESC;