0
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

How can I improve this query ? I have many posts and they are taking like 2secs each.

Thanks

I also found this extra info that I think would help...

Why do you think modifying WordPress core table is a good idea? – Krzysiek Dróżdż♦ Jun 12 '15 at 4:21 I really don't think it's a good idea but a necessary one if running wordpress with the amount of posts and combined with the limitations of mysql not having a descending index function. Those file sorts caused by the order by operations are a deal breaker for us in regards to site performance. – Ranknoodle Jun 15 '15 at 2:34 But these operations are slow since you're doing it wrong. In some projects we had similar issue, but came to very different solution, that didn't modify core tables. We've created our own table and used it as indexing/search table. So every slow query was searching only based on this one table (no joins needed). (And we had much more data, AFAIR) – Krzysiek Dróżdż♦ Jun 15 '15 at 5:06 Hi KRZYSIEK can you explain a little more on indexing search tables that you created? For example the slow query outline in the original question, I would create a table to store the post ID,reverse_post_id,post_type etc and only query against that? – Ranknoodle Jun 15 '15 at 16:31 Send me an e-mail, I'll try to elaborate on that method. – Krzysiek Dróżdż♦ Jun 15 '15 at 16:34

But no idea on the method he used.

  • Can you add explain plan to it ? – JYOTI RAJAI Jan 27 at 1:14
  • Please post EXPLAIN (your query); for each of the queries so we can see what the optimizer will try to do with your request. In your second query you have a line - AND wp_posts.ID NOT IN (391534) - would this force looking at every row other than this single wp_posts.ID ? How many rows of output would this generate? – Wilson Hauck Jan 28 at 13:13
  • Do you have a link to Krzysiek Dróżdż's comments? – Rick James Jan 29 at 4:40
  • Krzysiek Dróżdż's link comments: wordpress.stackexchange.com/questions/189736/… – Wed Jan 29 at 6:41
0

'publish' is a string

+ is an _arithmetic operator

So 'publish' + _pad_term_counts() is probably not anything useful.

How long does each SELECT take?

Please provide SHOW CREATE TABLE and EXPLAIN SELECT and how big the tables are.

Descending index -- Not a problem; MySQL simply scans through the data in reverse order.

However, no index is useful for the one ORDER BY you mentioned -- This is because the GROUP BY is needed first. Can that be removed?

| improve this answer | |
  • thanks for help @Rick James but this just looks like a lot more research is needed – Wed Jan 30 at 0:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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