MySQL EXPLAIN Deep Dive: Read Plans Like a Pro, Fix Slow Queries in 60 Seconds

What EXPLAIN Does

EXPLAIN does not run your SQL; it only tells you how MySQL plans to execute it. For example:

EXPLAIN SELECT * FROM history_load WHERE device_code = 'DEV-1024';

The output is a table where each row represents an execution step. Using a 200-million-row table as an example, a bad query's plan looks like:

+------+-------------+------+------+----------+-------------+
| type | key         | rows | Extra            |
+------+-------------+------+------+----------+-------------+
| ALL  | NULL        | 2亿  | Using where      |
+------+-------------+------+------+----------+-------------+

The problem is obvious: full table scan, no index used, scanning 200 million rows. Such a query will inevitably time out.

Field Breakdown

id

Execution order: larger id runs first; same id runs top to bottom.

select_type

SIMPLE : single-table query

PRIMARY : outermost query

SUBQUERY : subquery

DERIVED : subquery in FROM clause

DEPENDENT SUBQUERY : row-by-row subquery; hitting this is usually a performance trap. The author has optimized several cases where DEPENDENT SUBQUERY caused second-level latency; rewriting to JOIN resolved it.

type (most important, best to worst)

system > const > eq_ref > ref > range > index > ALL

const / eq_ref : perfect, no action needed

ref : ordinary index equality match; target level for business queries

range : range query; acceptable if data volume is controlled

index : scans entire index tree; slightly better than full table scan

ALL : full table scan; a definite problem

For production tables over ten million rows, type must be at least ref or range. ALL means waiting for slow-query alerts.

Examples:

-- ref level
explain select * from history_load where device_code = 'DEV-1024';

-- range level
explain select * from history_load where collect_time between '2026-01-01' and '2026-01-31';

-- ALL level, whoever writes it steps on a landmine
explain select * from history_load where load_value > 5000;

possible_keys vs key

possible_keys

lists candidate indexes; key shows the one actually chosen. If possible_keys has values but key is NULL, the optimizer decided index access isn't worthwhile and prefers a full scan. If both are NULL, no usable index exists.

key_len

This field reveals how many columns of a composite index are used. For an index (device_code, collect_time, load_value): key_len=128: only first column used key_len=134: first two columns used

Checking key_len is the most direct way to verify composite index effectiveness.

rows

Estimated rows to scan (not exact). Practical thresholds:

< 1,000: fast

1,000 ~ 10,000: acceptable

10,000 ~ 100,000: needs optimization

> 100,000: expect timeout

Extra

Contains additional signals, some good, some warnings:

Using index : covering index, no table lookup → ✅ good

Using filesort : extra sorting required → 🔴 add index on sort column

Using temporary : temporary table used → 🔴 make GROUP BY and ORDER BY columns consistent

Using join buffer : join not using index → 🔴 add index on join columns

Real Case Study

Table with 200 million rows, query:

select * from history_load
where device_code = 'DEV-1024'
  and collect_time between '2026-07-01' and '2026-07-31'
order by collect_time desc
limit 20;

Initial EXPLAIN:

type=ALL, key=NULL, rows=2亿, Extra=Using filesort

Full table scan plus filesort — every possible pitfall hit.

Create composite index:

create index idx_device_time on history_load (device_code, collect_time desc);

EXPLAIN after index:

type=range, key=idx_device_time, rows=4520, Extra=Using index condition

Execution time dropped from 8 seconds to 15 milliseconds.

Daily Troubleshooting Checklist

After writing SQL, run EXPLAIN and watch four points:

type : reached ref or range? ALL means rework.

key : is it NULL? If yes, index not used.

rows : exceeds 100,000? If so, optimize.

Extra : any filesort or temporary? If present, fix.

Pass these four, and the SQL is unlikely to cause trouble. Miss any, and it will blow up sooner or later.