Any way to avoid a filesort when order by is different to where clause?
- by Julian
I have an incredibly simple query (table type InnoDb) and EXPLAIN says that MySQL must do an extra pass to find out how to retrieve the rows in sorted order.
SELECT * FROM `comments`
WHERE (commentable_id = 1976)
ORDER BY created_at desc LIMIT 0, 5
exact explain output:
table select_type type extra possible_keys key key length ref rows
comments simple ref using where; using filesort common_lookups common_lookups 5 const 89
commentable_id is indexed. Comments has nothing trick in it, just a content field.
The manual suggests that if the order by is different to the where, there is no way filesort can be avoided.
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
I also tried order by id as well as it's equivalent but makes no difference, even if I add id as an index (which I understand is not required as id is indexed implicitly in MySQL).
thanks in advance for any ideas!
