How can I tell if an index is being used to sort in MySQL?

Question

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.

Answer 1

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