Help optimizing MySQL slow query

Question

i'm running a wordpress which needs some mysql optimization, i have a slow query and i would like to get rid of "Using temporary; Using filesort"

query:

EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts 
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (1,3,4,5) ) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date 
DESC LIMIT 0, 10;

+----+-------------+-----------------------+------+--------------------------+------------------+---------+----------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------+------+--------------------------+------------------+---------+----------------+------+----------------------------------------------+
| 1 | SIMPLE | wp_posts | ref | PRIMARY,type_status_date | type_status_date | 62 | const | 4 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | wp_term_relationships | ref | PRIMARY,term_taxonomy_id | PRIMARY | 8 | wp.wp_posts.ID | 1 | Using where; Using index |
+----+-------------+-----------------------+------+--------------------------+------------------+---------+----------------+------+----------------------------------------------+

i have uploaded the dump files for the 2 test tables (4 categories and 10 posts)

http://pastebin.com/6zhVGQH7

http://pastebin.com/vUnkKqtP

i have tried a lot of things but nothing worked, tried to add indexes and force them, tried with a subquery and a lot more , from what i saw the major problems are on join line and on the order by post_date, mysql can't use type_status_date index for order by because the date column is not "the leftmost column", there is already a post_date index which is not used, even if i reorder the columns in the type_status_date this in not helping

Thanks

• Antti Rytsölä: Maybe split it in two queries? You could actually order by wp_posts.ID DESC as it's propably same as post_date. As this field is also in wp_term_relationships you could fetch the 10 biggest from wp_term_relationships and then fetch these from wp_posts.

Answer 1

I would like to get rid of "Using temporary; Using filesort"

One of the problems I see is that you're using different GROUP BY and ORDER BY clauses. From the manual on how MySQL uses temporary tables:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

As soon as you create a temporary table, it will need to be sorted according to your ORDER BY clause, indicated by 'using filesort'.

This execution plan at leasts uses the indexes to appropriately limit the number of rows found.

I would also look through the docs on ORDER BY optimization.

• user416096: i have read all the optimization docs from mysql (order by, group by, ofsset optimizations) but there are queries with order by and group by on different columns which are not generating temporary tables
• Derek Downey: Interesting...can you get me a pastebin of one such query EXPLAIN results and the SHOW CREATE TABLE of all tables involved in the query?

Answer 2

First, I would create the following indexes:

ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_type_date` (`post_type`, `post_date`);
ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_date` (`post_date`);
ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_id` (`ID`);
ALTER TABLE `wp_term_relationships` ADD INDEX `wp_term_relationship_idx_id` (`object_id`);

Few recommendations on query changes:

1. Avoid the SQL_CALC_FOUND_ROWS - try to run the COUNT and the query itself in 2 separate queries. In many cases, that may be more efficient.
2. Replace the join to wp_term_relationship can be replaced with an EXISTS. When a joined table isn’t used anywhere unless in the WHERE clause, it can be replaced with an EXISTS subquery, to avoid redundant grouping by the GROUP BY clause. In that case, you can remove the GROUP BY.
3. Replace the OR conditions with IN (not really a performance gain, but it's more readable).

Applied these recommendations and few other small changes:

SELECT
 SQL_CALC_FOUND_ROWS wp_posts.* 
 FROM
 wp_posts 
 WHERE
 1 = 1 
 AND (
 1 = 1
 ) 
 AND wp_posts.post_type = 'post' 
 AND (
 wp_posts.post_status IN (
 'publish', 'private'
 )
 ) 
 AND EXISTS (
 SELECT
 * 
 FROM
 wp_term_relationships 
 WHERE
 (
 wp_posts.ID = wp_term_relationships.object_id
 ) 
 AND wp_term_relationships.term_taxonomy_id IN (
 1, 3, 4, 5
 )
 ) 
 ORDER BY
 wp_posts.post_date DESC LIMIT 0,
 10