I've got a very large database in WordPress with lots of products in WooCommerce along with quite a bit of taxonomies. When querying the products on the front end it takes 1 minute or more.

Here is the query from mysql slow query log:

# Query_time: 66.741373  Lock_time: 0.000068 Rows_sent: 0  Rows_examined: 0
use wordpress;
SET timestamp=1352321640;
SELECT t.term_id,
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
LEFT JOIN wp_woocommerce_termmeta AS tm ON (t.term_id = tm.woocommerce_term_id
                                        AND tm.meta_key = 'order_pa_colorist')
WHERE tt.taxonomy IN ('pa_colorist')
ORDER BY CAST(tm.meta_value AS SIGNED) ASC, t.name ASC;

When I use the 'top' command while I'm loading one of the pages below it says mysql is using 99.9% of the CPU

The link that is having the issue is: http://ec2-54-243-161-225.compute-1.amazonaws.com/shop/dark-horse/ Any product on this page.

Any solution on speeding up this query? I don't mind to modify core WordPress files.

  • Uhm, you better well mind modifying core WordPress files. That's not the solution. Going off on a whim here, but any core files you've modified thus-far, make sure you revert everything back to their original files - as it would make troubleshooting this any further moot.
    – Zach
    Nov 7, 2012 at 21:38
  • That wasn't my intention on that comment, I noticed on some older stacks that people where modifying a line that fixed their problem. I didn't try it as I know modifying it can have major effect.
    – Stephen
    Nov 7, 2012 at 21:51
  • Can you run that query from the mysql client and see how many records are returned?
    – Steve
    Nov 7, 2012 at 22:42
  • 1
    Can you explain what the query is trying to do in english rather than SQL as in the intention, and if possible, the code that is creating said query? There isn't enough information here to diagnose and debug if all we can see is the resulting query, but not the code that actually does the query
    – Tom J Nowell
    Nov 7, 2012 at 23:49
  • 1
    I think this should be moved to either SO or DBA.StackExchange.com I suggest you flag your question for moderation attention. In the mean time, get the EXPLAIN output and the tables' definitions (SHOW CREATE TABLE ...) and edit. The solution may be as simple as adding indexes on the tables. Nov 8, 2012 at 14:28


Your Answer

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

Browse other questions tagged or ask your own question.