Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
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. –  anttir Nov 9 '11 at 10:41
add comment

1 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.

share|improve this answer
    
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 –  user416096 Nov 9 '11 at 14:06
    
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? –  Derek Downey Nov 9 '11 at 14:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.