EXPLAIN as a Design Tool for WooCommerce Order Queries

The orders screen is fine on the Processing tab and takes four seconds on the default view. Support types part of a customer email into the search box and it takes six. The slow query log stays quiet, because the threshold on this server is ten seconds and no single query looks unreasonable on its own. MySQL is reading two hundred thousand rows to return twenty, with the indexes it was given.

EXPLAIN is the cheapest way to see the difference, and it is also the cheapest way to design an index you have not added yet, because it tells you what the plan already does before you pay for anything new. On a store that takes orders all day the plan matters more than the schema, since forty orders a minute means forty writes to every index on the orders table.

The numbers below come from one store with 240,000 orders, running WordPress 6.8, WooCommerce 10.x and MySQL 8.0.36 on InnoDB with utf8mb4, with Redis handling the object cache. A persistent object cache does not help an admin list screen whose filter changes on every click, so all of this lands on MySQL. The plans are MySQL 8.0.36 output; MariaDB prints the same Extra phrases, though its JSON output differs.

What the orders screen actually runs

The list is not a single-table query. On the legacy post store it starts in wp_posts, joins wp_postmeta as soon as a customer, address or search filter is present, and adds GROUP BY wp_posts.ID because a meta join can duplicate rows. On HPOS the columns change and the problem does not: wp_wc_orders holds the status, type and dates, while customer filters reach into wp_wc_orders_meta or wp_wc_orders_addresses.

Take the search clause out of the legacy query and this is what remains:

SELECT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta
        ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
  AND wp_posts.post_type = 'shop_order'
  AND ( wp_posts.post_status = 'wc-processing' )
  AND ( wp_postmeta.meta_key = '_customer_user'
        AND wp_postmeta.meta_value = '1841' )
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20;

Four clauses decide the plan. The join brings in a second access path. The GROUP BY makes MySQL fold duplicates through an intermediate table. ORDER BY post_date DESC asks for an order that no index keyed on status can deliver. And LIMIT 20 separates a plan that reads twenty rows from one that reads the whole result set before sorting it.

Change that one status to the default view and the predicate becomes an IN list covering every built-in status. That is the shape that hurts first. Seven values together cover most of the table, so the filter is not selective, and the date column behind the sort is not in a position where the index can help with it. Put that statement under EXPLAIN, not the one-status tab that is already fast.

Reading EXPLAIN: five columns and one phrase

Prefix the statement with EXPLAIN and read five columns. The rest of the output is noise until the plan is right.

type

How rows are found. ALL is a full table scan, index a full index scan, range an index range, ref an equality lookup. ALL on a 240,000-row orders table means the status filter is applied after every order has been read.

possible_keys

The indexes the optimiser considered. An index missing from this column cannot help any part of the statement, and nothing further down the output will change that.

key

The index it chose, or NULL for none. A key you did not expect is often the optimiser taking a narrow index for the filter and accepting a sort in exchange.

rows

Estimated rows to examine, not rows returned. Compare it against the LIMIT. A plan that inspects six figures to return twenty is the whole complaint.

Extra

Where the sort and the temporary table appear. Using filesort means MySQL sorted the result after reading it. Using temporary means it materialised an intermediate table, caused by a GROUP BY over a joined result. Using index condition is good news: the filter ran inside the storage engine.

Using filesort does not mean a file on disk. MySQL sorts in memory when the result fits. It means the sort was not free, and on this screen the sort is the expensive half, so look at it before you tune the filter.

The composite index that serves a filter plus a sort

If you were designing the table from scratch, the shape you want leads with the equality columns and ends with the column you sort by:

-- The pattern: equality, equality, sort.
ALTER TABLE wp_wc_orders
  ADD INDEX type_status_created ( type, status, date_created_gmt );

Check what you already have first, because the answer is often that the index already exists. HPOS creates wp_wc_orders with type_status_date ( type, status, date_created_gmt ), and WordPress core creates wp_posts with type_status_date ( post_type, post_status, post_date, ID ). Both stores ship the index that serves a one-status filter and a date sort, and the real work is finding out why the plan is not using it.

SHOW INDEX FROM wp_wc_orders;
SHOW INDEX FROM wp_posts;

Both shapes below come from the legacy post store, where the status-first index is the only usable one.

The index appears in key in both columns, which is the point. With seven status values the index can only produce seven separate runs of dates, and merging those into one descending stream is a sort. With a single value it walks the index in the order the query asked for and stops after twenty rows.

When the optimiser refuses the index even for a single status, a second condition has changed the shape: a date range on the same column the query sorts by, or a join whose driving table it prefers.

Column order decides which half of the query the index serves

The rule people repeat is equality first, then range, then sort. It is correct, and it is incomplete in a way that matters on this screen.

A B-tree index is ordered by its columns in sequence, so a scan comes out in the query's order only when every column ahead of the sort column is pinned to one value. post_type = 'shop_order' pins the first. post_status = 'wc-processing' pins the second, so post_date is already in order and the sort disappears. Replace that equality with post_status IN ( 'wc-processing', 'wc-on-hold' ) and the second column is no longer pinned, so the dates arrive in two runs and MySQL has to sort them together.

That turns the rule into a choice. A status-first index, which both schemas ship, serves the tabs that staff work through all day. A date-first index, ( post_type, post_date ), serves the default view where the status list covers everything, because MySQL can walk it in date order and check the status on the way past. Core creates no such index on wp_posts, while HPOS also ships a plain date_created index. That asymmetry is why the same default view can be quick on one store and painful on the other.

What an index cannot fix, and what it costs to add

Two warnings before anybody runs an ALTER TABLE.

The first is the write path. An index is not free storage. It is a second data structure that every insert, update and delete has to maintain. On this store, forty orders a minute means forty inserts into wp_posts or wp_wc_orders, each one touching every index on that table. An admin screen that a handful of staff open twenty times a day cannot justify an index that taxes checkout all day. Add one when the plan improves by an order of magnitude, not by twenty percent.

Building the index is its own event. MySQL reads every row and writes every index entry, which on these 240,000 orders took just under four minutes and competed with normal traffic for I/O the whole time.

The second warning is that some predicates cannot use an index at all. The orders search box does not compare a column. It runs a set of LIKE comparisons against meta values such as _billing_email and _billing_address_index. A pattern with a leading wildcard, which is what a search for part of an email address produces, gives a B-tree nothing to seek to, so MySQL reads the candidate rows and tests each one. An index on wp_postmeta ( meta_key, meta_value ) helps the equality filters, where the value is known in full, and does nothing for the wildcard search. If order search is the slow screen, the fix is a different retrieval path, not another index.

ALTER TABLE wp_postmeta
  ADD INDEX meta_key_value ( meta_key(32), meta_value(32) );

The prefix lengths are deliberate, because meta_value is a longtext and cannot go into an index whole. HPOS already carries an equivalent on wp_wc_orders_meta. Both narrow the rows the engine touches for an equality filter, and both still read the row to confirm a value longer than the prefix.

Where a single index plan stops working

This is the limit. A plan belongs to a query, and the query belongs to whatever the person on the screen just did. The store manager works the Processing tab all morning. The support agent searches by email. The accountant pages through the default view. A plugin pulls the same list over REST with a date range. If the query shape differs per user, no single index serves all of them.

Efforts to serve one shape can spoil another. An index added for the default view is one more structure to maintain on every insert, and the optimiser may start choosing it for a filtered tab where it is the wrong choice. One screen gets faster while another gets slower.

On most stores the two shapes worth buying for are the status tabs and the search, because those are the screens a human waits on. The default view can afford a sort when its date range is bounded, and the REST integration can be asked to pass a range.

What to do on Monday

1. Print the real statement. A temporary add_filter( 'query', ... ) that logs SQL containing post_type = 'shop_order' gives you the exact query in one screen load.
2. Run EXPLAIN on that statement and read Extra before anything else. If Using filesort is there and the row count is large, you have found the cost.
3. Run SHOW INDEX on both tables before writing any ALTER. The composite index for one status plus a date sort is probably already in the schema.
4. If the default view is the slow one, decide between a date-first index and accepting the sort, then measure the write side for a day.
5. Do not add an index for a wildcard meta search. Change how the search retrieves rows.
6. Write the two shapes you decided to serve into the ticket with the before and after EXPLAIN output, so the next person knows what the index is for.