Reading EXPLAIN for one slow query until the plan matches the business path

A slow query is rarely “MySQL is bad.” More often the optimizer is faithfully executing a plan that does not match how the business filters data. The fastest way to see that mismatch is not another round of guesswork indexes—it is reading one EXPLAIN ANALYZE until every expensive node has a reason.

This article walks a single report query on a local MySQL 8 lab: about 50,000 customers and 200,000 orders. We measure a bad plan, name the node that hurts, add one composite index that matches the predicates, and confirm the new plan with numbers. Lab scripts live under content/labs/reading-explain-plans/.

The business query

Imagine an internal report: show the latest 50 paid orders created in October 2025, with customer email.

SELECT o.id, o.total_cents, o.created_at, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= '2025-10-01'
  AND o.created_at <  '2025-11-01'
ORDER BY o.created_at DESC
LIMIT 50;

Schema shape (simplified):

CREATE TABLE customers (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(191) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_customers_email (email)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  customer_id BIGINT NOT NULL,
  status VARCHAR(32) NOT NULL,
  total_cents INT NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_orders_customer (customer_id)
  -- no index on (status, created_at) yet
) ENGINE=InnoDB;

How selective is the filter? In this seed data, October + paid matches 3,000 rows—not the whole table, but enough that a bad access path is obvious.

Symptom: the plan before any index change

On MySQL 8.0.45, EXPLAIN FORMAT=TREE before the fix looked like this (trimmed):

-> Limit: 50 row(s)
    -> Nested loop inner join
        -> Sort: o.created_at DESC
            -> Filter: status = 'paid' AND created_at range
                -> Table scan on o
        -> Single-row index lookup on c using PRIMARY

EXPLAIN ANALYZE made the cost concrete:

Node	What it did	Actual
Table scan on orders	Read essentially the whole table	199,999 rows, ~54 ms scanning
Filter	Applied status + date after the scan	3,000 rows kept
Sort	Ordered those 3,000 by created_at	Contributed to ~71 ms total
Nested loop to customers	PK lookup per result row	50 loops after LIMIT
Whole query		~71.1 ms (actual time=71.1..71.1)

The surprising part is not the join. The join is a cheap primary-key lookup. The surprise is Table scan on o: for a query that only cares about one status and one month, MySQL still walked almost every order row, filtered in memory, sorted 3,000 survivors, then took 50.

That is the plan/business mismatch. The business path is “paid ∩ October ∩ top 50 by time.” The plan path was “read everything, then figure it out.”

How to read the tree without memorizing every operator

For day-to-day work, four questions are enough:

1. What is the first table access? Table scan, Index range scan, Index lookup?
2. How many rows does that access estimate vs actually produce? (rows= vs actual ... rows=)
3. Where does filtering happen—before or after a large scan? Filter above a table scan is a smell for missing indexes.
4. Is ORDER BY free (index order) or paid (explicit Sort)?

In the before plan:

• First access: table scan (bad for this filter).
• Actual rows at scan: ~200k.
• Filter sits above the scan.
• Sort is explicit, so the index was not providing order.

You do not need to recite optimizer internals to act on that.

Hypothesis

The predicates that define the business path are:

• equality on status
• range on created_at
• order by created_at DESC
• limit 50

A composite index that matches left-to-right is:

ALTER TABLE orders
  ADD INDEX idx_orders_status_created (status, created_at);

Why this order:

• status first because it is an equality; it narrows the index to one status slice.
• created_at second because it is a range and the sort key; MySQL can range-scan within paid and walk newest-first (reverse range scan).
• We do not lead with created_at alone here: a pure date index would still mix all statuses and force more filtering.

Covering every selected column is optional for this article. Getting the access path right already removes the full scan; the join still uses customers PK.

After: plan and timings

After adding idx_orders_status_created, EXPLAIN FORMAT=TREE became:

-> Limit: 50 row(s)
    -> Nested loop inner join
        -> Index range scan on o using idx_orders_status_created
             over (status = 'paid' AND created_at in October) (reverse)
        -> Single-row index lookup on c using PRIMARY

EXPLAIN ANALYZE summary:

Metric	Before	After
Access on orders	Table scan ~200k rows	Index range scan (reverse)
Rows examined on orders path	199,999 scanned → 3,000 match	50 rows from the index path under LIMIT
Explicit sort	Yes	No (index order)
Query actual time	~71.1 ms	~0.39 ms

On this laptop lab, that is roughly a 180× improvement for the same SQL text. Production deltas vary with cache, disk, and concurrency, but the shape of the win is stable: stop reading rows the business path will discard.

Why the reverse range scan matters

The after plan says (reverse) on the range scan. That is the optimizer using the index order to satisfy:

ORDER BY o.created_at DESC LIMIT 50

without a separate filesort of 3,000 rows. Combined with LIMIT 50, MySQL can stop early once 50 qualifying index entries are joined—visible in ANALYZE as about 50 rows from the orders side, not 3,000.

If the index had been only (status) or only (created_at), you would usually still see either residual filtering, a sort, or both.

What we did not do

• Did not rewrite the query into a subquery first. The SQL was already clear; the access path was wrong.
• Did not add five overlapping indexes. One composite matching equality + range + order was enough for this report.
• Did not trust EXPLAIN without ANALYZE. Estimated rows=199626 was directionally right, but actual time is what you take to a release review.

Reproduce the lab

Requirements: MySQL 8.x client/server and permission to create a database (the sample run used Homebrew MySQL 8.0.45 as root without a password).

# from repo root
chmod +x content/labs/reading-explain-plans/run-lab.sh
./content/labs/reading-explain-plans/run-lab.sh

# if your root user needs a password:
MYSQL_PASSWORD='...' ./content/labs/reading-explain-plans/run-lab.sh

The script rebuilds stackinside_explain_lab, prints BEFORE EXPLAIN ANALYZE, adds the index, then prints AFTER. Seed size is ~50k / ~200k rows; expect setup to take tens of seconds.

Clean up:

mysql -uroot -e 'DROP DATABASE stackinside_explain_lab;'

Regression guard

Indexes get dropped. Queries get copied into new services without the supporting DDL. Guard the contract, not the folklore:

1. Schema check in migration tests: idx_orders_status_created exists on orders.
2. Plan smoke in staging (periodic is enough):

EXPLAIN FORMAT=TREE
SELECT ... -- same report query

Fail the check if the tree contains Table scan on o for this query shape.

1. Latency budget on the report endpoint or job: alert if p95 climbs back toward the pre-index regime after a deploy.

A useful CI assertion is boring on purpose: this query’s plan must start from idx_orders_status_created. When someone “simplifies” indexes, the assertion fails before users do.

Summary

Step	Action
1	Write the business predicates in one sentence
2	Run EXPLAIN ANALYZE, not only EXPLAIN
3	Find the first large scan or sort that does not match those predicates
4	Add the smallest index (or rewrite) that fixes that node
5	Re-run ANALYZE and keep a regression check

In this lab the sentence was: paid orders in one month, newest 50, with email. The broken plan scanned ~200k order rows in ~71 ms. The fixed plan range-scanned the composite index and finished in ~0.4 ms. Reading the tree did not require a textbook—only the discipline to stop at the first node that clearly disagreed with the business path.