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!