Master MySQL EXPLAIN: Full Field Breakdown of type, key, rows, Extra for Real‑World Tuning

What is EXPLAIN?

EXPLAIN is described as MySQL’s SQL health‑check report. It does not execute the query but pre‑parses the execution logic and tells whether an index is used, which index, how many rows are scanned, and whether there are table scans, sorting, temporary tables, or other bottlenecks.

Why must you master EXPLAIN?

Most developers (about 90%) optimize SQL by guessing, assuming that adding an index always speeds up a query or that pagination slowness is caused by data volume. The article stresses that every online SQL performance issue must be diagnosed with EXPLAIN, not guessed.

Where to use EXPLAIN

All complex SQL before release

Slow‑query alerts or interface time‑out investigations

Verification after adding indexes or rewriting SQL

Validation of composite, covering, and pagination‑sorting queries

Detection of implicit conversion, left‑most‑match failures, and OR‑index issues

Core EXPLAIN fields (12 total, 6 highlighted)

1. id – execution order identifier

Same id: statements run top‑to‑bottom; larger id runs earlier. Used to judge order in multi‑table joins and subqueries.

2. select_type – query type

Common values: SIMPLE (no join/subquery, optimal), PRIMARY (main query), SUBQUERY , DERIVED (derived temporary table, poorer performance).

3. type – index access level (most important)

Performance ranking (best → worst): system > const > eq_ref > ref > range > index > ALL . The article states that production SQL must have type ≥ ref and must not show index or ALL.

4. possible_keys – theoretically usable indexes

Shows candidate indexes; a non‑NULL value does not guarantee usage.

5. key – actually used index

If key is not NULL, the index is truly hit; NULL means index failure and a full table scan.

6. key_len – length of the used index

Longer key_len indicates more columns of a composite index are used, reflecting higher index utilization.

7. ref – index match source

Displays whether the match comes from a constant, column, or function; useful for routine optimization.

8. rows – estimated rows scanned

Smaller numbers mean better performance. Deep pagination, full scans, or index failures can cause rows to explode to hundreds of thousands or millions.

9. Extra – detailed optimization flags

Using index : covering index, no table lookup – optimal.

Using where : normal post‑index filtering.

Using filesort : external file sort, index not used for ordering – performance problem.

Using temporary : temporary table for grouping/sorting – severe performance hit.

Using index condition : index condition push‑down, moderate benefit.

NULL : normal index usage with possible back‑table lookup.

Hands‑on examples (user_order table)

Example 1 – optimal index hit (ref + Using index)

EXPLAIN SELECT user_id, status FROM user_order WHERE user_id = 1001 AND status = 1;

Result: type=ref, key is the composite index, Extra=Using index → precise index hit, covering index, no back‑table, excellent performance.

Example 2 – index failure (ALL)

EXPLAIN SELECT * FROM user_order WHERE status = 1;

Result: type=ALL, key=NULL → left‑most match broken, full table scan, must be optimized.

Example 3 – filesort appears

EXPLAIN SELECT * FROM user_order WHERE user_id = 1001 ORDER BY create_time;

Result: Extra=Using filesort → index ordering not used, extra sorting will choke high‑concurrency workloads.

Example 4 – deep pagination rows explode

EXPLAIN SELECT * FROM user_order ORDER BY create_time LIMIT 80000, 20;

Result: huge rows value → massive data scan, deep pagination issue; recommends cursor pagination or delayed association.

Enterprise‑level SQL diagnostic workflow (10‑second rule)

Check type: if ALL or index, index is failing.

Check key: ensure a real index is used.

Check rows: large values indicate deep pagination or full scans.

Check Extra: look for Using filesort, Using temporary, or back‑table issues.

Apply targeted optimizations to indexes, SQL syntax, or pagination logic.

Common pitfalls

Assuming possible_keys means the index is used – only key matters.

Believing type=range is always safe – large ranges or many IN values can still cause heavy scans.

Ignoring Using filesort – harmless on tiny data but disastrous at million‑QPS scale.

Focusing only on index usage and neglecting rows – high rows can make a query slow even with an index.

Misinterpreting Using where as an error – it is normal post‑index filtering.

Key takeaways

EXPLAIN is the sole standard for SQL tuning; never rely on guesswork. type priority: production queries must be ref or better. key non‑NULL confirms index usage; NULL means failure.

Smaller rows equals stronger performance; deep pagination is a rows‑problem. Extra flags are gold: Using index is optimal; filesort and temporary must be eliminated.

Follow the diagnostic order: type → key → rows → Extra for systematic optimization.