QueryKings

Reading MySQL EXPLAIN: a working walkthrough

Most slow-query work starts and ends with EXPLAIN, yet the output is easy to misread. This walkthrough covers how we actually read a plan in a tuning engagement: which columns matter, which are noise, and the handful of patterns that account for most production slowness.

Start with EXPLAIN ANALYZE, not EXPLAIN

On MySQL 8.0.18+, prefer:

EXPLAIN ANALYZE SELECT ...;

Plain EXPLAIN shows the optimizer's estimates. EXPLAIN ANALYZE runs the query and shows actual row counts and per-operator timings. The single most common tuning mistake is trusting an estimate that is off by three orders of magnitude — stale statistics on a large table will do that. When actual rows diverge badly from estimates, run ANALYZE TABLE before touching anything else, then re-check.

EXPLAIN ANALYZE executes the statement, so on production use it for SELECTs only, and be careful with anything long-running.

The columns that matter

In the classic tabular output, four columns carry nearly all the signal:

  • type — the access method. ALL means a full table scan; index means a full index scan (often just as bad); range, ref, and eq_ref are what you usually want; const is ideal. Most tuning work is turning an ALL into a ref or range.
  • key — the index actually chosen. Compare against possible_keys; when an obviously right index sits in possible_keys but is not chosen, look at datatype mismatches, character-set differences on join columns, or a leading-column mismatch.
  • rows — the optimizer's per-step row estimate. Multiply the rows values down a join chain to see the work the plan implies. A five-table join with rows of 10 each is 100,000 combinations before filtering.
  • Extra — where the traps live, covered next.

Extra: the trap column

Three values deserve immediate attention:

  • Using filesort — the result needs an explicit sort. Not always fatal for small result sets, but on a paginated listing sorted by created_at it usually means the query sorts thousands of rows to return twenty. The fix is almost always an index whose column order matches the WHERE equality columns followed by the ORDER BY columns.
  • Using temporary — an intermediate table, typically from GROUP BY on a non-indexed expression or a DISTINCT across joins. In-memory is tolerable; spilling to disk is not. Check Created_tmp_disk_tables.
  • Using index — the good one: a covering index served the query without touching the row data. Deliberately widening an index to cover a hot query is one of the highest-return changes available.

Joins: read the order, not the SQL

MySQL executes joins in the order EXPLAIN lists them, not the order you wrote them. The first row is the driving table. Two rules of thumb:

  1. The driving table should be the one your WHERE clause filters hardest.
  2. Every subsequent table should join via ref or eq_ref on an indexed column. A join step with type: ALL means MySQL scans that entire table per row of everything above it — the classic nested-loop blowup that turns a 200ms query into a 90-second one.

If the optimizer picks a bad driving table even with correct statistics, look at whether a low-selectivity index is misleading it, and only then consider hints (JOIN ORDER, STRAIGHT_JOIN) — hints are a last resort because they freeze today's assumptions into tomorrow's data distribution.

A worked example

A tenant-scoped listing endpoint of ours-to-fix looked like this:

SELECT * FROM orders
WHERE account_id = ? AND status = 'open'
ORDER BY created_at DESC LIMIT 20;

EXPLAIN ANALYZE showed type: ref on an (account_id) index — superficially fine — but actual rows read were 48,000 with Using filesort. The index found the account, then MySQL read every one of that account's orders, filtered by status, and sorted. The fix:

ALTER TABLE orders ADD INDEX idx_account_status_created
  (account_id, status, created_at);

Equality columns first, then the sort column. Rows read dropped to 20, the filesort disappeared, and p99 went from 1.8s to 4ms. Nothing exotic — just reading what the plan actually said instead of stopping at "it uses an index."

Make it routine

Plans drift as data grows: an index that was selective at one million rows may not be at one hundred million rows. Capture slow queries continuously (long_query_time around 100–500ms with log_slow_extra on 8.0, or the Performance Schema digest tables), and re-EXPLAIN the top digests quarterly. The queries that hurt are rarely the ones anyone remembers writing.