Reading MySQL EXPLAIN output

EXPLAIN is the single most useful tool for MySQL performance work. Prepend it to a SELECT, UPDATE, DELETE, INSERT, or REPLACE and MySQL returns the execution plan it intends to use — the strategy, the indexes, and an estimate of how much work each step does.

EXPLAIN SELECT *
          FROM users
         WHERE status = 'active'
      ORDER BY created_at DESC;

This note walks the four columns that matter (type, key, rows, Extra), explains the filtered column properly (it’s the most misread), and shows how the driving table cascades through JOINs.

The columns to actually read

Decoding the type column (best to worst)

1. system / const — single row by primary or unique key

   Lightning-fast. The optimizer knows the result is exactly one row before the query runs.

2. eq_ref — one row from this table per outer row

   Excellent. Used on the inner side of a join when the join column is a primary or unique key.

3. ref — non-unique index lookup, possibly many rows

   Good. MySQL is finding matching rows via a regular index. Fine as long as the index is selective.

4. range — index range scan (BETWEEN, >, <, IN)

   Good. The engine walks a portion of the index. Watch the rows estimate — a wide range over a low-selectivity column degrades fast.

5. index — full index scan

   Poor. The engine is walking the entire index tree. Cheaper than a table scan because the index is narrower, but still a lot of reads.

6. ALL — full table scan

   Bad. The engine reads every row from disk. On a million-row table this is a job, not a query. Almost always means a missing or unusable index on the WHERE columns.

Decoding the Extra column

Green flags (good):

• Using index — covering index hit. MySQL answered the query from the index alone, never touching the table data.
• Using index condition — Index Condition Pushdown. MySQL is filtering at the index level before fetching the row.

Red flags (need optimization):

• Using filesort — MySQL cannot use the index to sort and is doing an extra sort pass. Slow; may spill to disk.
• Using temporary — MySQL is materializing an intermediate result, usually because of complex GROUP BY or ORDER BY over a join. Major bottleneck.
• Using where combined with type: ALL — pulling every row off disk and filtering in memory.

The filtered column (most misread part of EXPLAIN)

filtered is the estimated percentage of the rows count that survive the row-level predicates. Higher is better — high filtered means the index narrowed down the work effectively and MySQL didn’t pull rows it would then discard.

Mental model: imagine hiring a recruiter to find 10 engineers.

The catch: filtered = 100% is great only when rows is also small. A full table scan over 5M rows that all match is technically 100% efficient and still terrible because you moved 5M rows. The real target is low rows, high filtered.

Why filtered matters more in JOINs

MySQL uses a nested loop join: for each row kept from the outer table, probe the inner table once. The number of probes is rows × filtered, not rows alone — filtered decides how many outer rows actually drive inner lookups.

Concrete example — find active users in New York who ordered today, joining users to orders:

The danger compounds across 3+ tables: if step 1 passes 10× too many rows, step 2 does 10× the lookups, step 3 does 100× the lookups, and the query freezes.

Picking the driving table

The driving table is the first table MySQL reads — the outer of the nested loop. It’s the top row of the EXPLAIN output. Every subsequent row is a probe driven by the rows passed from above.

The optimizer’s goal when picking the driving table: the table that will pass the fewest rows to the next step. It uses table statistics and predicate selectivity to guess.

When the optimizer guesses wrong (often because of stale statistics or unusual data distribution), the query slows down dramatically. Diagnosis: read EXPLAIN top to bottom, watch for an early row with a surprisingly high rows × filtered product.

The blunt fix is STRAIGHT_JOIN, which forces MySQL to read tables in the textual order:

SELECT *
  FROM users
  STRAIGHT_JOIN orders ON orders.user_id = users.id
 WHERE users.status = 'active';

Actionable fixes by symptom

A protocol for tuning a slow query

1. Run EXPLAIN, find the worst row

   Read top to bottom. The row with the highest rows × filtered product is where most of the work happens.

2. Diagnose by type and Extra

   type: ALL → missing index. Using filesort / Using temporary → sort or aggregation can’t ride an index. key: NULL → optimizer rejected its options.

3. Add or fix an index

   For equality + sort, build (equality_col, sort_col). For range + sort, the sort column must come before the range to be index-orderable.

4. Re-run EXPLAIN, then EXPLAIN ANALYZE

   EXPLAIN ANALYZE (MySQL 8.0.18+) actually runs the query and reports actual row counts and timing per iterator alongside the estimates. Large divergence between estimated and actual rows means stale statistics — run ANALYZE TABLE and re-check.

5. Verify with a real workload

   A plan that looks good in isolation can still degrade under concurrency. Measure latency at p99 with realistic data volume.

What to remember

The four columns are type, key, rows, Extra. The fifth, filtered, only makes sense in pair with rows — together they tell you how much real work the step does. EXPLAIN shows the optimizer’s plan, but the optimizer is constrained by the rules in 0005 execution order · MySQL ; understanding both layers is what makes the plan readable. For the specific case of “did this row exist?”, read the plan that 0006 EXISTS produces · MySQL and notice it never multiplies rows. And for ORM-generated queries that surface as EXPLAIN mysteries, see 0001 the JPA N+1 note · Java — that’s often where the offending query came from.