Reading EXPLAIN on a WooCommerce query: a field guide

The query that ate a Tuesday morning

An agency we work with had a WooCommerce shop where the /shop page took fourteen seconds to render. They had already added Redis object cache, a page cache, and a CDN. None of it helped, because the page was hitting the database fresh on every variation filter. The slow log showed one query, repeated, against wp_posts joined to wp_postmeta four times.

They sent us the EXPLAIN. Four rows back, a dozen columns wide, and the team kept zeroing in on the join order. The join order was fine. The answer was three columns over: type, rows, and Extra. This post is the field guide we wish we had taped to the inside of the laptop lid that morning, written for anyone who maintains a WordPress, WooCommerce, or Magento database in production.

type, in plain language

The type column tells you how MySQL plans to find rows in that table. It is the single most diagnostic column on the line. Read the documented hierarchy from cheapest to most expensive and the rest of the plan starts to make sense.

• system / const: one row, found by primary key. Effectively free.
• eq_ref: one row per row from the previous table, via a unique index. The normal cost of a healthy join.
• ref: multiple rows for each lookup, via a non-unique index. Fine for selective columns, ugly when the column has twelve distinct values across four million rows.
• range: an index range scan. Reasonable for date windows or ID ranges. Suspicious when the range is open-ended on a high-cardinality column.
• index: a full scan of an index. Cheaper than ALL because the index is narrower than the row, but you are still touching every row.
• ALL: a full table scan. On a two-hundred-row table, fine. On wp_postmeta with four million rows, this is where afternoons disappear.

A rule of thumb: any line whose type is ALL or index on a table over a hundred thousand rows is a hypothesis you need to disprove. Sometimes the planner is right and a scan is genuinely cheaper than the index lookup. More often it is wrong because the index is missing, the column is wrapped in a function, or the statistics are stale.

rows, and why the optimizer is guessing

The rows column is the optimizer's estimate of how many rows it expects to read from that table for this step. The word estimate matters. MySQL samples index pages to come up with the number, and on a stale or skewed table it can be off by an order of magnitude in either direction.

Three habits help:

• Multiply the rows across all lines of the plan. That product is the upper bound on row reads this query implies. If it lands in the millions and you have not added a LIMIT, the query will scale with the catalog, not with what the page actually shows.
• If rows looks suspiciously round, often the table size itself, the planner gave up and fell back to "I will read everything." That is almost always a missing or unusable index.
• Run ANALYZE TABLE wp_postmeta; before you trust a plan. Statistics drift, especially after a big import or a Black Friday weekend.

Extra, where the truth lives

The Extra column is free text and holds the things the planner could not say with structured columns. It is also where most of the actual performance problems are named out loud. The phrases you will see most on a WordPress or WooCommerce query:

• Using where: a filter is applied after rows are read. Normal, but pay attention when paired with ALL. It means MySQL read every row and then threw most of them away.
• Using index: the query was satisfied entirely from the index, no row read needed. This is what you want.
• Using index condition: index condition pushdown is in play. The storage engine is filtering before passing rows up. A good sign on a covering-ish index.
• Using filesort: ORDER BY cannot be served from the index. MySQL is sorting in memory or on disk. On a product listing sorted by menu_order then post_title, this is the common cost of WooCommerce's default sort.
• Using temporary: an intermediate table is being built, usually for GROUP BY or DISTINCT. With UNION queries on multi-language WPML setups, this appears constantly.
• Using join buffer (Block Nested Loop): no usable index for the join. The planner is buffering rows and doing an in-memory cross. Smells like a missing index on a postmeta key.

Two of those together, Using temporary; Using filesort on the same line, is the canonical "this query needs a rewrite, not an index" signal. You can paper over it with a bigger sort buffer, but the math will catch up with you.

A walkthrough on a real WooCommerce query

Here is a stripped-down version of the agency's slow query. Sort by price ascending on a /shop page filtered to one product category:

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;

The plan came back with four rows. The one that mattered read like this:

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

type ref and rows 1 look fine. The Extra column does not. Using filesort on a CAST expression means MySQL has to materialize the converted decimal for every candidate row before it can sort. There is no index on the cast, and there cannot be one in the schema as it stands.

The fix was a generated column on wp_postmeta with a numeric index, populated from _price. After that, the same EXPLAIN line read Using index and the /shop page rendered in under four hundred milliseconds. The query did not change. The plan did, because the planner finally had something it could sort against.

From plan to fix

A loose checklist for the next slow WooCommerce or WordPress query you stare at:

1. Find the line with the highest rows value. Start there.
2. Look at type on that line. If it is ALL or index, ask why.
3. Read the Extra column out loud. If it contains filesort or temporary, the cost is in the sort or group, not the read.
4. Check whether key matches what you expected. possible_keys tells you what the planner could have used; key tells you what it picked. They are often different.
5. Run ANALYZE TABLE on the largest table in the plan. Re-EXPLAIN. If the plan changes, statistics were the problem, not the schema.

The MySQL EXPLAIN reference is the canonical map of every value these columns can hold, and the MariaDB version documents the small but real differences for shops running on MariaDB 10.x, which most managed hosts still default to.

The smallest thing to do today

Pick the slowest URL on a WordPress or WooCommerce site you maintain. Open the slow log, copy the query that fires for that URL, prefix it with EXPLAIN, and read the three columns above before you change a single line of PHP. Nine times out of ten, the fix names itself.

When we built Pier's MySQL editor we kept running into this exact loop, alt-tabbing between a desktop SQL client and a Slack thread to argue about what the plan meant. The plan view annotates each row inline and shows the EXPLAIN diff before and after an index is added, with full version history on every schema change against the legacy site.