0

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)

enter image description here

thanks

6
  • Hey ! Can you share explain plan of both the queries ? Jan 27, 2020 at 13:12
  • i dont know how to change it to be good i am looking for guidance
    – Wed
    Jan 27, 2020 at 13:20
  • I am asking for EXPLAIN of this queries. The query execution plan of mysql server. Jan 27, 2020 at 13:21
  • simp,y write explain before each select, run it and post the result
    – nbk
    Jan 27, 2020 at 17:28
  • i added the extra info from EXPLAIN.
    – Wed
    Jan 27, 2020 at 18:31

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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