title: Mysql slow query: INNER JOIN + ORDER BY causes filesort
body: <p>I'm trying to optimize this query:</p>
  
  <pre>
  SELECT `posts`.* FROM `posts` INNER JOIN `posts_tags` 
       ON `posts`.id = `posts_tags`.post_id 
       WHERE  (((`posts_tags`.tag_id = 1))) 
       ORDER BY posts.created_at DESC;
  </pre>
  
  <p>The size of tables is 38k rows, and 31k and mysql uses "filesort" so it gets pretty slow. I tried to use different indexes, no luck. </p>
  
  <pre>
  CREATE TABLE `posts` (
    `id` int(11) NOT NULL auto_increment,
    `created_at` datetime default NULL,
    PRIMARY KEY  (`id`),
    KEY `index_posts_on_created_at` (`created_at`),
    KEY `for_tags` (`trashed`,`published`,`clan_private`,`created_at`)
  ) ENGINE=InnoDB AUTO_INCREMENT=44390 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  
  CREATE TABLE `posts_tags` (
    `id` int(11) NOT NULL auto_increment,
    `post_id` int(11) default NULL,
    `tag_id` int(11) default NULL,
    `created_at` datetime default NULL,
    `updated_at` datetime default NULL,
    PRIMARY KEY  (`id`),
    KEY `index_posts_tags_on_post_id_and_tag_id` (`post_id`,`tag_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=63175 DEFAULT CHARSET=utf8
  </pre>
  
  <pre>
  +----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+
  | id | select_type | table      | type   | possible_keys            | key                      | key_len | ref                 | rows  | Extra                                                     |
  +----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+
  |  1 | SIMPLE      | posts_tags | index  | index_post_id_and_tag_id | index_post_id_and_tag_id | 10      | NULL                | 24159 | Using where; Using index; Using temporary; Using filesort | 
  |  1 | SIMPLE      | posts      | eq_ref | PRIMARY                  | PRIMARY                  | 4       | .posts_tags.post_id |     1 |                                                           | 
  +----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+
  2 rows in set (0.00 sec)
  </pre>
  
  <p>What kind of index I need to define to avoid mysql using filesort? Is it possible when order field is not in where clause?</p>
  
  <p>update:
  Profiling results:</p>
  
  <pre>
  mysql> show profile for query 1;
  +--------------------------------+----------+
  | Status                         | Duration |
  +--------------------------------+----------+
  | starting                       | 0.000027 | 
  | checking query cache for query | 0.037953 | 
  | Opening tables                 | 0.000028 | 
  | System lock                    | 0.010382 | 
  | Table lock                     | 0.023894 | 
  | init                           | 0.000057 | 
  | optimizing                     | 0.010030 | 
  | statistics                     | 0.000026 | 
  | preparing                      | 0.000018 | 
  | Creating tmp table             | 0.128619 | 
  | executing                      | 0.000008 | 
  | Copying to tmp table           | 1.819463 | 
  | Sorting result                 | 0.001092 | 
  | Sending data                   | 0.004239 | 
  | end                            | 0.000012 | 
  | removing tmp table             | 0.000885 | 
  | end                            | 0.000006 | 
  | end                            | 0.000005 | 
  | query end                      | 0.000006 | 
  | storing result in query cache  | 0.000005 | 
  | freeing items                  | 0.000021 | 
  | closing tables                 | 0.000013 | 
  | logging slow query             | 0.000004 | 
  | cleaning up                    | 0.000006 | 
  +--------------------------------+----------+
  </pre>
  
  <p><b>update2:</b></p>
  
  <p>Real query (some more boolean fields, more useless indexes)</p>
  
  <pre>SELECT `posts`.* FROM `posts` INNER JOIN `posts_tags` 
     ON `posts`.id = `posts_tags`.post_id 
     WHERE ((`posts_tags`.tag_id = 7971)) 
         AND (((posts.trashed = 0) 
         AND (`posts`.`published` = 1 
         AND `posts`.`clan_private` = 0)) 
         AND ((`posts_tags`.tag_id = 7971)))  
     ORDER BY created_at DESC LIMIT 0, 10; </pre>
  
  <p>Empty set (1.25 sec)</p>
  
  <p>Without ORDER BY — 0.01s.</p>
  
  <pre>
  
  +----+-------------+------------+--------+-----------------------------------------+-----------------------+---------+---------------------+-------+--------------------------+
  | id | select_type | table      | type   | possible_keys                           | key                   | key_len | ref                 | rows  | Extra                    |
  +----+-------------+------------+--------+-----------------------------------------+-----------------------+---------+---------------------+-------+--------------------------+
  |  1 | SIMPLE      | posts_tags | index  | index_posts_tags_on_post_id_and_tag_id  | index_posts_tags_...  | 10      | NULL                | 23988 | Using where; Using index | 
  |  1 | SIMPLE      | posts      | eq_ref | PRIMARY,index_posts_on_trashed_and_crea | PRIMARY               | 4       | .posts_tags.post_id |     1 | Using where              | 
  +----+-------------+------------+--------+-----------------------------------------+-----------------------+---------+---------------------+-------+--------------------------+
  
  </pre>
  
  <p><b>SOLUTION</b></p>
  
  <ol>
  <li>Query updated  to "ORDER BY posts_tags.created_at DESC" (two small changes in app code)</li>
  <li>Index added: index_posts_tags_on_created_at.</li>
  </ol>
  
  <p>That's all!</p>