title: MySQL performance optimization: order by datetime field
body: <p>I have a table with roughly 100.000 blog postings, linked to a table with 50 feeds via an 1:n relationship. When I query both tables with a select statement, ordered by a datetime field of the postings table, MySQL always uses filesort, resulting in very slow query times (>1 second). Here's the schema of the <code>postings</code> table (simplified):</p>
  
  <pre><code>+---------------------+--------------+------+-----+---------+----------------+
  | Field               | Type         | Null | Key | Default | Extra          |
  +---------------------+--------------+------+-----+---------+----------------+
  | id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
  | feed_id             | int(11)      | NO   | MUL | NULL    |                |
  | crawl_date          | datetime     | NO   |     | NULL    |                |
  | is_active           | tinyint(1)   | NO   | MUL | 0       |                |
  | link                | varchar(255) | NO   | MUL | NULL    |                |
  | author              | varchar(255) | NO   |     | NULL    |                |
  | title               | varchar(255) | NO   |     | NULL    |                |
  | excerpt             | text         | NO   |     | NULL    |                |
  | long_excerpt        | text         | NO   |     | NULL    |                |
  | user_offtopic_count | int(11)      | NO   | MUL | 0       |                |
  +---------------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>And here's the <code>feed</code> table:</p>
  
  <pre><code>+-------------+--------------+------+-----+---------+----------------+
  | Field       | Type         | Null | Key | Default | Extra          |
  +-------------+--------------+------+-----+---------+----------------+
  | id          | int(11)      | NO   | PRI | NULL    | auto_increment |
  | type        | int(11)      | NO   | MUL | 0       |                |
  | title       | varchar(255) | NO   |     | NULL    |                |
  | website     | varchar(255) | NO   |     | NULL    |                |
  | url         | varchar(255) | NO   |     | NULL    |                |
  +-------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>And here's the query that takes >1 second to execute. Please note that the <code>post_date</code> field has an index, but MySQL isn't using it to sort the postings table:</p>
  
  <pre><code>SELECT 
      `postings`.`id`, 
      UNIX_TIMESTAMP(postings.post_date) as post_date, 
      `postings`.`link`, 
      `postings`.`title`, 
      `postings`.`author`, 
      `postings`.`excerpt`, 
      `postings`.`long_excerpt`, 
      `feeds`.`title` AS feed_title, 
      `feeds`.`website` AS feed_website
  FROM 
      (`postings`)
  JOIN 
      `feeds` 
  ON 
      `feeds`.`id` = `postings`.`feed_id`
  WHERE 
      `feeds`.`type` = 1 AND 
      `postings`.`user_offtopic_count` &lt; 10 AND 
      `postings`.`is_active` = 1
  ORDER BY 
      `postings`.`post_date` desc
  LIMIT 
      15  
  </code></pre>
  
  <p>The result of the <code>explain extended</code> command on this query shows that MySQL is using filesort:</p>
  
  <pre><code>+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
  | id | select_type | table    | type   | possible_keys                         | key       | key_len | ref                      | rows  | Extra                       |
  +----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
  |  1 | SIMPLE      | postings | ref    | feed_id,is_active,user_offtopic_count | is_active | 1       | const                    | 30996 | Using where; Using filesort |
  |  1 | SIMPLE      | feeds    | eq_ref | PRIMARY,type                          | PRIMARY   | 4       | feedian.postings.feed_id |     1 | Using where                 |
  +----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
  </code></pre>
  
  <p>When I remove the <code>order by</code> part, MySQL stops using filesort. Please let me know if you have any ideas on how to optimize this query to get MySQL to sort and select the data by using indexes. I have already tried a few things such as creating a combined index on all where/order by fields, as suggested by a few blog postings, but this didn't work either.</p>