WordPress and WooCommerce very slow query

Question

After a migration to a new WooCommerce install, but we are experiencing very sow queries. Sometimes more than 1 minte.

Currently this query is fired. This is fired in 0.7253s

EXPLAIN SELECT t.*, tt.*, tr.object_id
FROM btwc_terms AS t
INNER JOIN btwc_term_taxonomy AS tt
ON t.term_id = tt.term_id
INNER JOIN btwc_term_relationships AS tr
ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN ('pwb-brand', 'product_type', 'product_visibility', 'product_cat', 'product_tag', 'product_shipping_class', 'pa_aangeraden-bedekking', 'pa_aantal-m2', 'pa_aantal-vakken-horizontaal', 'pa_aantal-vakken-verticaal', 'pa_accu-type', 'pa_accucapaciteit', 'pa_accuspanning', 'pa_achterkant', 'pa_afhalen', 'pa_afmeting', 'pa_afmeting-cm', 'pa_afwerking', 'pa_andere-namen', 'pa_anti-torsion', 'pa_antibacterieele-binnenvoerin', 'pa_antiperforatie', 'pa_antistatisch', 'pa_aswaarde', 'pa_behandeling', 'pa_bestandsdelen', 'pa_brandstof', 'pa_breedte', 'pa_breedte-achterzijde', 'pa_custom-html', 'pa_custom-stock-message', 'pa_diameter', 'pa_diepte', 'pa_draaggewicht', 'pa_eigenschappen', 'pa_elektrische-geleidbaarheid-e', 'pa_exclude-for-beslist', 'pa_extra-opties', 'pa_formaat-bodembedekker', 'pa_formaat-verpakking-lxbxh', 'pa_geslacht', 'pa_gestapeld', 'pa_gewicht', 'pa_google-gtin-aanwezig', 'pa_hoogte', 'pa_houtsoort', 'pa_inhoud', 'pa_inhoud-liters', 'pa_kabellengte', 'pa_keurmerk', 'pa_kies-uw-maat', 'pa_kies-uw-schoenmaat', 'pa_kleur', 'pa_kniptype', 'pa_korting-tekst', 'pa_kortingsfactor-10-staffelfac', 'pa_kwaliteit', 'pa_land-van-herkomst', 'pa_lengte', 'pa_lengte-buitenmaat', 'pa_lengte-links-buitenmaat', 'pa_lengte-rechts-buitenmaat', 'pa_levensduur', 'pa_leverancier', 'pa_levering', 'pa_levertijd', 'pa_maat', 'pa_marge-per-stap-marge-10', 'pa_materiaal', 'pa_meslengte', 'pa_nen-en-11772018', 'pa_oplaadtijd', 'pa_opvangbak-inhoud', 'pa_order-processing-time', 'pa_ovengedroogd', 'pa_ph-water-zone', 'pa_plaatdikte', 'pa_pre-order-text', 'pa_prijs-arbeidsloon', 'pa_prijs-excl-btw', 'pa_prijs-halve-vracht', 'pa_prijs-pallet', 'pa_prijs-per-kg', 'pa_prijs-transport-naar-winsum', 'pa_prijs-transportkosten-naar-k', 'pa_prijs-verpakking', 'pa_product-label', 'pa_productieprijs', 'pa_search-weight', 'pa_shipping-group', 'pa_shock-absorberend', 'pa_snijbreedte', 'pa_snijhoogte', 'pa_soort-laars', 'pa_soort-zool', 'pa_staffelfactor', 'pa_stamomtrek', 'pa_steel', 'pa_te-berekenen-productmarge', 'pa_toepassing', 'pa_type-houtpellets', 'pa_type-neus', 'pa_type-verpakking', 'pa_typenummer', 'pa_vanafprijs', 'pa_veiligheidsklasse', 'pa_vermogen', 'pa_verzenddatum-verbergen', 'pa_verzendeenheid', 'pa_verzendkosten', 'pa_verzendwijze', 'pa_vloeistof-dicht', 'pa_vochtpercentage-hout', 'pa_voeding', 'pa_voedingsstoffen', 'pa_voor-het-laatst-besteld', 'pa_voor-het-laatst-gecontroleer', 'pa_voorzien-van-reflecterende-e', 'pa_warehouse', 'pa_warmtewaarde', 'pa_werktijd', 'pa_wielen', 'pa_zitdiepte', 'pa_zithoogte')
AND tr.object_id IN (85865, 86033, 88961, 89108, 89585, 89602, 89638, 89657, 89688, 89767, 89813, 89832, 89846, 89869, 89903, 89940, 89996, 90034, 93044, 93056, 93070, 93084, 93115, 93127, 93141, 93153, 93278, 93329, 93341, 93427, 93667, 93693, 93712, 93731, 93966, 93981, 94022, 94051, 114762, 115589)
ORDER BY t.name ASC

And this is the trackback

require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/plugins/woocommerce/templates/single-product.php'), wc_get_template_part, load_template, require('/plugins/woocommerce/templates/content-single-product.php'), do_action('woocommerce_after_single_product_summary'), WP_Hook->do_action, WP_Hook->apply_filters, woocommerce_output_related_products, woocommerce_related_products, array_map, wc_get_product, WC_Product_Factory->get_product, WC_Product->__construct, WC_Data_Store->read, WC_Product_Data_Store_CPT->read, WC_Product_Variable_Data_Store_CPT->read_attributes, wc_get_object_terms, get_the_terms, wp_get_object_terms, get_terms, WP_Term_Query->query, WP_Term_Query->get_terms

These are the EXPLAIN results

1   SIMPLE  t   ALL PRIMARY NULL    NULL    NULL    1   Using temporary; Using filesort 
1   SIMPLE  tt  ALL PRIMARY,term_id_taxonomy,taxonomy   NULL    NULL    NULL    1   Using where; Using join buffer (flat, BNL join) 
1   SIMPLE  tr  range   PRIMARY,term_taxonomy_id    PRIMARY 8   NULL    1   Using where; Using index; Using join buffer (incre...   

Answer 1

Here is the WordPress schema FYI.

I've recently been digging into this term / taxonomy subsystem of WordPress. Much of the query slowness is due to the very long IN (val, val, val) lists rather than poor indexing. ANALYZE FORMAT=JSON shows lots of iterations in various query-plan steps.

Based on my current knowledge it looks like improving these queries' performance will take WordPress code changes.

The good news: Implementing a persistent WordPress Object Cache with redis or memcached mitigates the performance problems. There are many of these SELECT t.*, tt.* ... queries (really, are they kidding, SELECT *, WTF?) in the code base. Most of them are used to populate the object cache, which then gets used in place of these queries.

Conclusion: If you run WooCommerce you need a persistent object cache.

<rant>WooCommerce is a commercial software product for selling stuff online. It relies heavily on this term / taxonomy subsystem to handle the attributes (like T-shirt size or pipe diameter or whatever). And it's not a high-performance subsystem. Maybe Woo should tell their customers they need an object cache? </rant>