EXPLAIN and reading query plans without fear

Which indexes it uses, how many rows it estimates, whether it sorts or scans. Your first tool when something is slow.

Running EXPLAIN

EXPLAIN
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

MySQL 8.0.18+ also supports EXPLAIN ANALYZE, which runs the query and shows actual timings:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';

Key columns in EXPLAIN output

Classic EXPLAIN (tabular) columns worth knowing:

type – join/access method; ALL = full table scan (often bad on big tables); ref, eq_ref, range are usually better
possible_keys – indexes MySQL considered
key – index actually chosen (NULL = none)
rows – estimated rows examined (lower is better)
Extra – e.g. Using where, Using index (covering), Using filesort, Using temporary

Reading a simple example

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Good sign: type is ref, key is idx_orders_customer, rows is small. Bad sign on a million-row table: type is ALL, key is NULL – full scan. Add or fix an index on customer_id.

JOIN order and nested loops

EXPLAIN returns one row per table in the query. Read in order: MySQL picks a driving table, then joins others. Unexpected order or huge row estimates on an inner table hint at missing indexes on join columns.

EXPLAIN
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at >= '2026-01-01';

If filtering by placed_at is selective, a composite index (placed_at, customer_id) or (customer_id, placed_at) depending on typical queries may help – EXPLAIN both variants.

Filesort and temporary tables

Using filesort means MySQL sorts after fetching rows – not always a file, but extra work. Using temporary often appears with GROUP BY or DISTINCT. Acceptable on small sets; investigate when rows are large.

Matching index order to WHERE + ORDER BY can remove filesort – see the index strategy tutorial.

When indexes are ignored

MySQL may skip an index when:

The table is tiny (scan is cheaper)
Column wrapped in a function: WHERE YEAR(placed_at) = 2026 – rewrite to range on bare column
Low selectivity – e.g. Indexing a boolean status alone on a skewed column
Type mismatch – comparing string to numeric column prevents index use

-- Bad for index on placed_at
WHERE DATE(placed_at) = '2026-07-01'

-- Better
WHERE placed_at >= '2026-07-01' AND placed_at < '2026-07-02'

Practical workflow

EXPLAIN before adding indexes blindly
Compare before/after when changing schema or query
Use EXPLAIN ANALYZE on staging for real timings
Enable slow query log in production to find candidates (advanced tutorials goes deeper)

The next tutorial covers migration habits – changing schema safely as your app evolves.