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)
The filesort is unavoidable due to the
GROUP BY
. However:IN(SUBQUERY)
gets run on every row ofthe joined tables. Execute that subquery
before-hand as an independent query and then substitute the (now
static) results into the
IN()
function.EXISTS(SUBQUERY)
function.What are the warnings showing up in the
EXPLAIN
?I know this old, but it’s not a WordPress issue – it’s a plugin issue (as usual).
You must be using a plugin called EventCalendar – that’s what is causing this problem.
http://wpcal-archive.firetree.net/2008-January/002892.html