I've got a table with roughly 100.000 blog posts, associated with a table with 50 feeds with an 1:n relationship. After I query both tables having a choose statement, purchased with a datetime area from the posts table, MySQL always uses filesort, leading to very slow query occasions (>1 second). Here's the schema from the posts table (simplified):

+---------------------+--------------+------+-----+---------+----------------+

 Area                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                         

 link                 varchar(255)  NO    MUL  NULL                    

 author               varchar(255)  NO         NULL                    

 title                varchar(255)  NO         NULL                    

 excerpt              text          NO         NULL                    

 lengthy_excerpt         text          NO         NULL                    

 user_offtopic_count  int(11)       NO    MUL                         

+---------------------+--------------+------+-----+---------+----------------+

And here's the feed table:

+-------------+--------------+------+-----+---------+----------------+

 Area        Type          Null  Key  Default  Extra          

+-------------+--------------+------+-----+---------+----------------+

 id           int(11)       NO    PRI  NULL     auto_increment 

 type         int(11)       NO    MUL                         

 title        varchar(255)  NO         NULL                    

 website      varchar(255)  NO         NULL                    

 url          varchar(255)  NO         NULL                    

+-------------+--------------+------+-----+---------+----------------+

And here's the query that can take >1 second to complete. Please be aware the publish_date area comes with an index, but MySQL is not utilizing it to sort the posts table:

Choose

    `postings`.`id`,

    UNIX_TIMESTAMP(posts.publish_date) as publish_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` < 10 AND

    `postings`.`is_active` = 1

ORDER BY

    `postings`.`post_date` desc

LIMIT

    15

Caused by the explain extended command about this query implies that MySQL is applying filesort:

+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+

 id  choose_type  table     type    possible_secrets                          key        key_len  ref                       rows   Extra                       

+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+

  1  SIMPLE       posts  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.posts.feed_id      1  Using where                 

+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+

After I take away the order by part, MySQL stops using filesort. Please tell me for those who have any ideas regarding how to optimize this question to obtain MySQL to sort and choose the information by utilizing indexes. I've already attempted a couple of items like developing a combined index on all where/order by fields, as recommended with a couple of blog posts, but this did not work either.