Mysql – Help optimizing MySQL slow query

MySQLoptimizationorder-byquery

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

Best Answer

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.

Related Question