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