Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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

share|improve this answer
    
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. –  QmunkE Jun 27 '12 at 14:13
    
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 Jun 27 '12 at 14:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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