The index was there all along: how one NULL safe join made an 18 second page

The orders screen of one of the online stores I run had become slow. Not dramatically slow most of the time, about 1.8 seconds per load, the kind of number staff complain about quietly. But one tab, "Stock short", the list of orders we can't ship yet because something is out of stock, took 18.5 seconds. That's the tab the warehouse team opens first thing in the morning.

The database is tiny. About 3,800 orders, 4,100 order lines, and 1,466 rows in the table that holds stock per warehouse. Nothing that size should take a second and a half to query, let alone eighteen. When a query is slow on small data, the data isn't the problem. The plan is.

The query

The "stock short" check joins each order line to its stock row. A product can have variants (sizes, colours) or not, so the join compares the variant column NULL safely:

JOIN product_warehouse pw
  ON  pw.product_id = i.product_id
  AND pw.warehouse_id = i.warehouse_id
  AND pw.product_variant_id <=> i.product_variant_id

<=> is MySQL's NULL safe equals. Plain = would never match a line without a variant, because NULL = NULL is not true. So <=> is correct here. It's also where the trouble started.

EXPLAIN said everything was fine

Plain EXPLAIN showed an index lookup on pw with an estimate of about 1.5 rows per loop. Nothing wrong with that. If I'd stopped there I would have gone looking somewhere else.

EXPLAIN ANALYZE actually runs the query and reports what happened next to what the optimizer expected:

-> Index lookup on pw using product_variant_id
   (cost=0.365 rows=1.46) (actual time=0.006..0.517 rows=334 loops=3344)

Expected 1.46 rows. Got 334. Three thousand times over. That's about 1.1 million rows read and thrown away to answer a question about 4,000 order lines.

Why

MySQL chose the single column index on product_variant_id. That index is fine for lines that have a variant. But 71% of our order lines don't, and a third of the stock rows don't either. For every one of those lines, a NULL safe lookup on that index matches the entire NULL bucket, every stock row without a variant across every product, and then filters it down afterwards by product and warehouse.

The optimizer's estimate assumed values spread evenly. NULLs aren't spread evenly. They pile up in one bucket, and the join walked into it on almost every loop.

Why 18 seconds and not 1.6

The slow query took 1.6 seconds on its own. The page took 18.5 because the controller applied its filters eleven times per request, for the list, the tab badges and the summary numbers, and nothing remembered the result. On a normal load only one of those calls ran the stock check, which explains the 1.8 seconds. On the "Stock short" tab every one of them did.

The fix

Two small changes.

First, a composite index on (product_id, warehouse_id, product_variant_id). With product_id leading, the lookup narrows to one product in one warehouse before it ever reaches the NULL column, so the NULL bucket stops mattering. The migration checks whether the index exists before creating it and has a real rollback.

Second, I memoised the stock check inside the controller for the duration of a request. It has one call site and runs under PHP FPM, which builds a fresh controller per request, so there's no way for the cache to leak between users.

Result

The counts on every tab stayed exactly the same before and after, which is the check I care about most: the index changed the plan, not the answer.

What I took from it

If a query is slow on small tables, run EXPLAIN ANALYZE, not EXPLAIN, and compare the estimated rows with the actual rows on every line. A large gap is the optimizer telling you it guessed wrong, and the line where it happens is usually the whole story.

And be suspicious of nullable columns in join conditions. NULL safe comparison is often the right thing to write. It just makes NULL a value like any other, including the most common value in the column, and an index on that column alone will happily send you through all of them.