EXPLAIN lezen bij een WooCommerce-query: de veldgids

De query die een dinsdagochtend opvrat

Een bureau waar we mee werken had een WooCommerce-shop waar de /shop-pagina veertien seconden nodig had om te renderen. Ze hadden al een Redis object cache toegevoegd, een page cache, en een CDN. Niets hielp, want de pagina ging bij elk variatiefilter opnieuw naar de database. De slow log liet één query zien, herhaald, tegen wp_posts dat vier keer gejoind werd met wp_postmeta.

Ze stuurden ons het EXPLAIN-resultaat. Vier rijen terug, een stuk of twaalf kolommen breed, en het team bleef hangen op de join-volgorde. Die join-volgorde was prima. Het antwoord stond drie kolommen verderop: type, rows en Extra. Dit stuk is de veldgids die we die ochtend graag binnen in het deksel van de laptop hadden geplakt, geschreven voor iedereen die een WordPress-, WooCommerce- of Magento-database in productie onderhoudt.

type, in begrijpelijke taal

De kolom type vertelt je hoe MySQL van plan is om rijen in die tabel te vinden. Het is veruit de meest diagnostische kolom op de regel. Lees de gedocumenteerde hiërarchie van goedkoop naar duur en de rest van het plan valt op zijn plek.

• system / const: één rij, gevonden via primary key. In de praktijk gratis.
• eq_ref: één rij per rij uit de vorige tabel, via een unieke index. De normale kostprijs van een gezonde join.
• ref: meerdere rijen per lookup, via een niet-unieke index. Prima voor selectieve kolommen, lelijk als de kolom twaalf unieke waarden heeft over vier miljoen rijen.
• range: een index range scan. Redelijk voor datumvensters of ID-ranges. Verdacht als de range open is op een kolom met hoge cardinaliteit.
• index: een volledige scan van een index. Goedkoper dan ALL omdat de index smaller is dan de rij, maar je raakt nog steeds elke rij aan.
• ALL: een full table scan. Op een tabel van tweehonderd rijen prima. Op wp_postmeta met vier miljoen rijen verdwijnen hier middagen in.

Vuistregel: elke regel met type ALL of index op een tabel boven de honderdduizend rijen is een hypothese die je moet weerleggen. Soms heeft de planner gelijk en is een scan echt goedkoper dan de index-lookup. Vaker heeft hij het mis, omdat de index ontbreekt, de kolom in een functie zit, of de statistieken verouderd zijn.

rows, en waarom de optimizer gokt

De kolom rows is de schatting van de optimizer voor het aantal rijen dat hij verwacht te lezen uit die tabel, voor deze stap. Het woord schatting telt. MySQL sampelt indexpagina's om tot dat getal te komen, en op een verouderde of scheve tabel kan dat een ordegrootte ernaast zitten, beide kanten op.

Drie gewoonten helpen:

• Vermenigvuldig de rows over alle regels van het plan. Dat product is de bovengrens van het aantal row reads dat deze query inhoudt. Als dat in de miljoenen uitkomt en je hebt geen LIMIT, dan schaalt de query mee met de catalogus, niet met wat de pagina daadwerkelijk toont.
• Als rows verdacht rond oogt, vaak precies de tabelgrootte, dan heeft de planner het opgegeven en is teruggevallen op 'ik lees alles wel'. Dat is bijna altijd een ontbrekende of onbruikbare index.
• Draai ANALYZE TABLE wp_postmeta; voor je een plan vertrouwt. Statistieken verlopen, vooral na een grote import of een Black Friday-weekend.

Extra, waar de waarheid zit

De kolom Extra is vrije tekst en bevat wat de planner niet in gestructureerde kolommen kwijt kon. Het is ook de plek waar de meeste echte performance-problemen hardop benoemd worden. De zinnen die je het vaakst tegenkomt bij een WordPress- of WooCommerce-query:

• Using where: na het lezen van de rijen wordt nog een filter toegepast. Normaal, maar let op als het in combinatie met ALL staat. Dan heeft MySQL elke rij gelezen en het grootste deel daarna weggegooid.
• Using index: de query werd volledig vanuit de index beantwoord, geen row read nodig. Dit is wat je wilt.
• Using index condition: index condition pushdown is actief. De storage engine filtert al voor de rijen omhoog worden gegeven. Goed teken bij een covering-achtige index.
• Using filesort: de ORDER BY kan niet vanuit de index bediend worden. MySQL sorteert in geheugen of op disk. Bij een productoverzicht gesorteerd op menu_order en daarna post_title is dit de standaardprijs van de WooCommerce-sortering.
• Using temporary: er wordt een tussentabel opgebouwd, meestal voor GROUP BY of DISTINCT. Bij UNION-queries op meertalige WPML-setups verschijnt dit voortdurend.
• Using join buffer (Block Nested Loop): geen bruikbare index voor de join. De planner buffert rijen en doet een in-memory cross. Ruikt naar een ontbrekende index op een postmeta-key.

Twee daarvan samen, Using temporary; Using filesort op dezelfde regel, is hét signaal 'deze query heeft een herschrijving nodig, geen index'. Je kunt het verbloemen met een grotere sort buffer, maar de rekensom haalt je uiteindelijk in.

Doorloop op een echte WooCommerce-query

Hier is een uitgeklede versie van de trage query van dat bureau. Sorteren op prijs oplopend op een /shop-pagina, gefilterd op één productcategorie:

EXPLAIN
SELECT p.ID
FROM wp_posts p
INNER JOIN wp_term_relationships tr ON tr.object_id = p.ID
INNER JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
INNER JOIN wp_postmeta pm ON pm.post_id = p.ID AND pm.meta_key = '_price'
WHERE p.post_type = 'product'
  AND p.post_status = 'publish'
  AND tt.taxonomy = 'product_cat'
  AND tt.term_id = 42
ORDER BY CAST(pm.meta_value AS DECIMAL(10,2)) ASC
LIMIT 12 OFFSET 0;

Het plan kwam terug met vier rijen. De regel die ertoe deed, las als volgt:

table: pm
type: ref
key: post_id
rows: 1
Extra: Using where; Using filesort

Type ref en rows 1 zien er prima uit. De Extra-kolom niet. Using filesort op een CAST-expressie betekent dat MySQL voor elke kandidaatrij de geconverteerde decimal moet materialiseren voor hij kan sorteren. Er is geen index op de cast, en die kan er ook niet komen zoals het schema er nu uitziet.

De fix was een generated column op wp_postmeta met een numerieke index, gevuld vanuit _price. Daarna las dezelfde EXPLAIN-regel Using index en rendde de /shop-pagina in minder dan vierhonderd milliseconden. De query veranderde niet. Het plan wel, omdat de planner eindelijk iets had om tegen te sorteren.

Van plan naar fix

Een losse checklist voor de volgende trage WooCommerce- of WordPress-query waar je naar staart:

1. Zoek de regel met de hoogste waarde voor rows. Daar begin je.
2. Kijk naar type op die regel. Als het ALL of index is, vraag dan waarom.
3. Lees de Extra-kolom hardop. Als er filesort of temporary in staat, zit de kostprijs in de sortering of groepering, niet in de read.
4. Controleer of key matcht met wat je verwachtte. possible_keys vertelt je wat de planner had kunnen gebruiken; key vertelt je wat hij koos. Vaak verschillen ze.
5. Draai ANALYZE TABLE op de grootste tabel in het plan. Re-EXPLAIN. Als het plan verandert, lagen de statistieken aan de basis, niet het schema.

De MySQL EXPLAIN reference is de canonieke kaart van elke waarde die deze kolommen kunnen bevatten, en de MariaDB-variant documenteert de kleine maar reële verschillen voor shops op MariaDB 10.x, wat de meeste managed hosts nog steeds als standaard hebben.

Het kleinste wat je vandaag kunt doen

Pak de traagste URL op een WordPress- of WooCommerce-site die je onderhoudt. Open de slow log, kopieer de query die voor die URL afgaat, zet er EXPLAIN voor, en lees de drie kolommen hierboven voor je één regel PHP aanraakt. Negen van de tien keer benoemt de fix zichzelf.

Toen we de MySQL editor van Pier bouwden, liepen we precies tegen deze loop aan, alt-tabbend tussen een desktop SQL-client en een Slack-thread om te discussiëren over wat het plan nou betekende. De plan-view annoteert elke regel inline en toont de EXPLAIN-diff voor en na het toevoegen van een index, met volledige version history op elke schemawijziging tegen de legacy site.