getting rid of filesort on WordPress MySQL query

Posted by Hans on Stack Overflow See other posts from Stack Overflow or by Hans
Published on 2010-05-08T00:09:07Z Indexed on 2010/05/08 0:18 UTC
Read the original article Hit count: 610

Filed under:
|
|
|

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`)
)

© Stack Overflow or respective owner

Related posts about Wordpress

Related posts about mysql