Need help with indexes on 2 slow wordpress query

Question

Here are my my 2 slow query i want to improve.

SELECT object_id, term_taxonomy_id
FROM wp_term_relationships
INNER JOIN wp_posts
ON object_id = ID
WHERE term_taxonomy_id IN (525627,516360,525519,535782,517555,525186,517572,549564,1,517754,541497,541472,525476,549563,517633,524859,702393,541604,543483,524646,525001,550518,541516,525244,549565,517376,535783,524642,25,533395,533537,525475,2,705306,524684,525065,939122,541603,525523,533491,541590,702713,550724,525243,533634,525122,541498,549586,546982,21,524643,541478,525435,535784,541471,516611,535781,541638,516142,533416,546984,524999,533453,524682,704994,516579,516189,524644,517378,525185,541508,517634,705305,524858,517632,541637,517699,525064,517573,772367,516609,517375,525474,507436,524918,517635,541929,22,54,53,705119,524685,524683,516577,536343,191228,524915,524917,516298,541573,546983,515904,541601,56,517377,524645,517707,515905,516297,515903,517708,533635,516296,516578,517750,517554,516016,525123,533538,541625,525187,705307,55,191226,19,24,516299,541466,524916,772366,555654,516612,541503,191227,550302,991853,920642,191229,535829,525582,525524,524919,524720,525841,517636,541504,525184,525520,541562,525433,541563,516610)
AND post_type IN ('post')
AND post_status = 'publish' +
_pad_term_counts()
Theme   259514  2.0440

SELECT wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1
AND wp_posts.ID NOT IN (391534)
AND ( wp_term_relationships.term_taxonomy_id IN (2,516296,517375,517376,517377,517378,517554,517555,517572,517573,517632,517633,517634,517635,517636,517699,517707,517708,517750,517754,524858,524859,524915,524916,524917,524918,524919,524999,525001,525064,525065,525185,525186,525187,525519,525520,525523,525524,525582,525841,533395,533416,533453,535782,535783,535784,535829,536343,549563,549564,549565,549586,550302,550518,550724,555654,702393,702713,704994,705119,705305,705306,705307,772366,772367,920642,939122,991853) )
AND wp_posts.post_type = 'post'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 6

So i was thinking that adding indexes on wp_term_relationships(object_id, term_taxonomy_id) and wp_posts(post_type, post_status, id, post_date) could improve this but how?

Any idea regarding how i should approach this ?

Here is the explain select:

explain select object_id, term_taxonomy_id from wp_term_relationships;
+------+-------------+-----------------------+-------+---------------+---------+---------+------+---------+-------------+
| id   | select_type | table                 | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+------+-------------+-----------------------+-------+---------------+---------+---------+------+---------+-------------+
|    1 | SIMPLE      | wp_term_relationships | index | NULL          | PRIMARY | 16      | NULL | 1326055 | Using index |
+------+-------------+-----------------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.000 sec)

thanks

Answer 1

Try Below Index :

CREATE INDEX IDX_CMB ON wp_posts(ID,post_type,post_date);

Basically, this index will used for indexing GROUP BY clause WHERE clause, create this index & check explain plan & execution time.