CODEWITHSUNDEEP
mysqlsqljoinindexing
alexanderg
alexanderg

Reputation: 177

MySQL query uses filesort despite index when ordering

Edit: I made query it simpler just to test:

select *
from table1 where date >= '2012-02-02' order by date, col_2 desc

I have composite index on date and col_2 however when I do explain on my query it shows:

+----+-------------+------------------+-------+--------------------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table            | type  | possible_keys            | key             | key_len | ref  | rows | Extra                       |
+----+-------------+------------------+-------+--------------------------+-----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | table1           | range | col_2_date, date         | col_2_date      | 4       | NULL | 4643 | Using where; Using filesort |
+----+-------------+------------------+-------+--------------------------+-----------------+---------+------+------+-----------------------------+

Why does mySQL use filesort if I have index on the columns col_2 and date and how could I prevent it?

Upvotes: 2

Views: 159

Answers (1)

alexanderg
alexanderg

Reputation: 177

The answer is to order your results in the same order youve created your index ..e.g. if index is (col_1, col_2) then use ... order by col_1 desc, col_2 desc or ... order by col_1 asc, col_2 asc and not ... order by col_1 asc, col_2 desc or order by col_2, col_1 for example.

Upvotes: 1

Related Questions