13

I have a query with an ORDER BY clause which uses a column which is the last column on an index which is being used in the WHERE clause, essentially of the form:

SELECT
  cols
FROM
  tables
WHERE
  col_1 = x
  AND col_2 = y
  AND col_3 = z
ORDER BY col_4

and the index is created on columns (col_1, col_2, col_3, col_4) in that order.

When I profile the query over 99% of the time is spent in the "Sorting result" state. col_4 is a timestamp column if that makes any difference. I understand that ORDER BY can only use an index under certain circumstances, but I'm still a bit mystified as to precisely when the optimiser would do so.

Improve this question

2 Answers 2

Reset to default
17

You should append EXPLAIN EXTENDED before query and see result yourself.

It should have an entry for

  • possible_keys

If this column is NULL, there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to check whether it refers to some column or columns that would be suitable for indexing.

and

  • Keys

The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value.

For more information you can refer this Explain output and Explain join_types

Improve this answer
2
  • I understand, my question specifically, is to do with the ORDER BY clause. My query is using the index specified to restrict the rows returned but given the performance of the sort I'm guessing it isn't using it for the ORDER BY clause.
    – Chris Cooper
    Commented Jun 27, 2012 at 14:13
  • 1
    In some cases, MySQL can use an index to satisfy an ORDER BY clause without doing any extra sorting. The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause. The following queries use the index to resolve the ORDER BY part refer this dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
    – Mahesh Patil
    Commented Jun 27, 2012 at 14:28
4

You use EXPLAIN on your query (explain select-or-whatever-your-query-is).

possible_keys show the ones that could be used, key the one actually used.

Improve this answer
1
  • 1
    To add to that: explain format=json select ... is even more powerful. It includes used_key_parts, which tells you whether a composite key was used partially or fully, and index_condition will tell you which part of the query is responsible which index.
    – okdewit
    Commented Apr 5, 2019 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.