getting rid of filesort on WordPress MySQL query

Question

An instance of WordPress that I manage goes down about once a day due to this monster MySQL query taking far too long:

SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.* 
FROM wp_posts 
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id LEFT JOIN wp_ec3_schedule ec3_sch ON ec3_sch.post_id=id 
WHERE 1=1 AND wp_posts.ID NOT IN ( SELECT tr.object_id 
FROM wp_term_relationships AS tr INNER JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
WHERE tt.taxonomy = 'category' AND tt.term_id IN ('1050') ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND NOT EXISTS (SELECT * 
FROM wp_term_relationships JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id 
WHERE wp_term_relationships.object_id = wp_posts.ID AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN (533,3567) ) AND ec3_sch.post_id IS NULL GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10;

What do I have to do to get rid of the very slow filesort? I would think that the multicolumn type_status_date index would be fast enough.

The EXPLAIN EXTENDED output is below.

+----+--------------------+-----------------------+--------+-----------------------------------+------------------+---------+---------------------------------------------------------------------------------+------+----------------------------------------------+
| id | select_type        | table                 | type   | possible_keys                     | key              | key_len | ref                                                                             | rows | Extra                                        |
+----+--------------------+-----------------------+--------+-----------------------------------+------------------+---------+---------------------------------------------------------------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | wp_posts              | ref    | type_status_date                  | type_status_date | 124     | const,const                                                                     | 7034 | Using where; Using temporary; Using filesort | 
|  1 | PRIMARY            | wp_term_relationships | ref    | PRIMARY                           | PRIMARY          | 8       | bwog_wordpress_w.wp_posts.ID                                                    |  373 | Using index                                  | 
|  1 | PRIMARY            | wp_term_taxonomy      | eq_ref | PRIMARY                           | PRIMARY          | 8       | bwog_wordpress_w.wp_term_relationships.term_taxonomy_id                         |    1 | Using index                                  | 
|  1 | PRIMARY            | ec3_sch               | ref    | post_id_index                     | post_id_index    | 9       | bwog_wordpress_w.wp_posts.ID                                                    |    1 | Using where; Using index                     | 
|  3 | DEPENDENT SUBQUERY | wp_term_taxonomy      | range  | PRIMARY,term_id_taxonomy,taxonomy | term_id_taxonomy | 106     | NULL                                                                            |    2 | Using where                                  | 
|  3 | DEPENDENT SUBQUERY | wp_term_relationships | eq_ref | PRIMARY,term_taxonomy_id          | PRIMARY          | 16      | bwog_wordpress_w.wp_posts.ID,bwog_wordpress_w.wp_term_taxonomy.term_taxonomy_id |    1 | Using index                                  | 
|  2 | DEPENDENT SUBQUERY | tt                    | const  | PRIMARY,term_id_taxonomy,taxonomy | term_id_taxonomy | 106     | const,const                                                                     |    1 |                                              | 
|  2 | DEPENDENT SUBQUERY | tr                    | eq_ref | PRIMARY,term_taxonomy_id          | PRIMARY          | 16      | func,const                                                                      |    1 | Using index                                  | 
+----+--------------------+-----------------------+--------+-----------------------------------+------------------+---------+---------------------------------------------------------------------------------+------+----------------------------------------------+
8 rows in set, 2 warnings (0.05 sec)

And CREATE TABLE:

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `post_author` bigint(20) unsigned NOT NULL default '0',
  `post_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL default 'publish',
  `comment_status` varchar(20) NOT NULL default 'open',
  `ping_status` varchar(20) NOT NULL default 'open',
  `post_password` varchar(20) NOT NULL default '',
  `post_name` varchar(200) NOT NULL default '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL default '0',
  `guid` varchar(255) NOT NULL default '',
  `menu_order` int(11) NOT NULL default '0',
  `post_type` varchar(20) NOT NULL default 'post',
  `post_mime_type` varchar(100) NOT NULL default '',
  `comment_count` bigint(20) NOT NULL default '0',
  `robotsmeta` varchar(64) default NULL,
  PRIMARY KEY  (`ID`),
  KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_date` (`post_date`),
  FULLTEXT KEY `post_related` (`post_title`,`post_content`)
)

Warnings:

mysql> SHOW warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'bwog_wordpress_w.wp_posts.ID' of SELECT #3 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: select distinct sql_calc_found_rows `bwog_wordpress_w`.`wp_posts`.`ID` AS `ID`,`bwog_wordpress_w`.`wp_posts`.`post_author` AS `post_author`,`bwog_wordpress_w`.`wp_posts`.`post_date` AS `post_date`,`bwog_wordpress_w`.`wp_posts`.`post_date_gmt` AS `post_date_gmt`,`bwog_wordpress_w`.`wp_posts`.`post_content` AS `post_content`,`bwog_wordpress_w`.`wp_posts`.`post_title` AS `post_title`,`bwog_wordpress_w`.`wp_posts`.`post_excerpt` AS `post_excerpt`,`bwog_wordpress_w`.`wp_posts`.`post_status` AS `post_status`,`bwog_wordpress_w`.`wp_posts`.`comment_status` AS `comment_status`,`bwog_wordpress_w`.`wp_posts`.`ping_status` AS `ping_status`,`bwog_wordpress_w`.`wp_posts`.`post_password` AS `post_password`,`bwog_wordpress_w`.`wp_posts`.`post_name` AS `post_name`,`bwog_wordpress_w`.`wp_posts`.`to_ping` AS `to_ping`,`bwog_wordpress_w`.`wp_posts`.`pinged` AS `pinged`,`bwog_wordpress_w`.`wp_posts`.`post_modified` AS `post_modified`,`bwog_wordpress_w`.`wp_posts`.`post_modified_gmt` AS `post_modified_gmt`,`bwog_wordpress_w`.`wp_posts`.`post_content_filtered` AS `post_content_filtered`,`bwog_wordpress_w`.`wp_posts`.`post_parent` AS `post_parent`,`bwog_wordpress_w`.`wp_posts`.`guid` AS `guid`,`bwog_wordpress_w`.`wp_posts`.`menu_order` AS `menu_order`,`bwog_wordpress_w`.`wp_posts`.`post_type` AS `post_type`,`bwog_wordpress_w`.`wp_posts`.`post_mime_type` AS `post_mime_type`,`bwog_wordpress_w`.`wp_posts`.`comment_count` AS `comment_count`,`bwog_wordpress_w`.`wp_posts`.`robotsmeta` AS `robotsmeta` from `bwog_wordpress_w`.`wp_posts` left join `bwog_wordpress_w`.`wp_term_relationships` on((`bwog_wordpress_w`.`wp_term_relationships`.`object_id` = `bwog_wordpress_w`.`wp_posts`.`ID`)) left join `bwog_wordpress_w`.`wp_term_taxonomy` on((`bwog_wordpress_w`.`wp_term_taxonomy`.`term_taxonomy_id` = `bwog_wordpress_w`.`wp_term_relationships`.`term_taxonomy_id`)) left join `bwog_wordpress_w`.`wp_ec3_schedule` `ec3_sch` on((`bwog_wordpress_w`.`ec3_sch`.`post_id` = `bwog_wordpress_w`.`wp_posts`.`ID`)) where ((not(<in_optimizer>(`bwog_wordpress_w`.`wp_posts`.`ID`,<exists>(select 1 AS `Not_used` from `bwog_wordpress_w`.`wp_term_relationships` `tr` join `bwog_wordpress_w`.`wp_term_taxonomy` `tt` where ((`bwog_wordpress_w`.`tr`.`term_taxonomy_id` = '3572') and ('category' = _utf8'category') and (<cache>(`bwog_wordpress_w`.`wp_posts`.`ID`) = `bwog_wordpress_w`.`tr`.`object_id`)))))) and (`bwog_wordpress_w`.`wp_posts`.`post_type` = _utf8'post') and (`bwog_wordpress_w`.`wp_posts`.`post_status` = _utf8'publish') and (not(exists(select 1 AS `Not_used` from `bwog_wordpress_w`.`wp_term_relationships` join `bwog_wordpress_w`.`wp_term_taxonomy` where ((`bwog_wordpress_w`.`wp_term_relationships`.`term_taxonomy_id` = `bwog_wordpress_w`.`wp_term_taxonomy`.`term_taxonomy_id`) and (`bwog_wordpress_w`.`wp_term_relationships`.`object_id` = `bwog_wordpress_w`.`wp_posts`.`ID`) and (`bwog_wordpress_w`.`wp_term_taxonomy`.`taxonomy` = _utf8'category') and (`bwog_wordpress_w`.`wp_term_taxonomy`.`term_id` in (533,3567)))))) and isnull(`bwog_wordpress_w`.`ec3_sch`.`post_id`)) group by `bwog_wordpress_w`.`wp_posts`.`ID` order by `bwog_wordpress_w`.`wp_posts`.`post_date` desc limit 10,10

2 rows in set (0.00 sec)

Answer 1

The filesort is unavoidable due to the GROUP BY. However:

• IN(SUBQUERY) gets run on every row of the joined tables. Execute that subquery before-hand as an independent query and then substitute the (now static) results into the IN() function.
• Do something similar with the EXISTS(SUBQUERY) function.
• Consider using WordPress's super-cache plugin.

What are the warnings showing up in the EXPLAIN?