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.