Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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,
   tt.parent,
   tt.count,
   tm.*
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.

share|improve this question
    
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 '12 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 '12 at 21:51
    
Can you run that query from the mysql client and see how many records are returned? –  Stephen Nov 7 '12 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 '12 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. –  ypercube Nov 8 '12 at 14:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.