Any way to avoid a filesort when order by is different to where clause?

Question

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!

To Mark -- here's SHOW CREATE TABLE

CREATE TABLE `comments` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) default NULL,
  `commentable_type` varchar(255) default NULL,
  `commentable_id` int(11) default NULL,
  `content` text,
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  `hidden` tinyint(1) default '0',
  `public` tinyint(1) default '1',
  `access_point` int(11) default '0',
  `item_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `created_at` (`created_at`),
  KEY `common_lookups` (`commentable_id`,`commentable_type`,`hidden`,`created_at`,`public`),
  KEY `index_comments_on_item_id` (`item_id`),
  KEY `index_comments_on_item_id_and_created_at` (`item_id`,`created_at`),
  KEY `index_comments_on_user_id` (`user_id`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31803 DEFAULT CHARSET=latin1

Answer 1

Try adding a combined index on (commentable_id, created_at).

Answer 2

Note that the MySQL term filesort doesn't necessarily mean it writes to the disk. It just means it's going to sort without using an index. If the result set is small enough, MySQL will sort it in memory, which is orders of magnitude faster than disk I/O.

You can increase the amount of memory MySQL allocates for in-memory filesorts using the sort_buffer_size server variable. In MySQL 5.1, the default sort buffer size is 2MB, and the maximum you can allocate is 4GB.

---

update: Regarding Jonathan Leffler's comment about measuring how long the sorting takes, you can learn how to use SHOW PROFILE FOR QUERY which will give you the breakdown of how long each phase of query execution takes.