✏️ Explanatory Question
Level: Advanced — The #1 tool for diagnosing slow queries; interviewers love this one.
The EXPLAIN statement shows how MySQL executes a query — the "execution plan." It reveals which indexes are used, the join order, how many rows are examined, and the access method, without actually running the query's full workload.
SELECT (or INSERT/UPDATE/DELETE) with EXPLAIN. For even more detail, use EXPLAIN ANALYZE (MySQL 8.0.18+), which actually runs the query and shows real timing.
| Column | What It Tells You |
|---|---|
type |
Join/access type — the most important field (see below) |
key |
The index actually used (NULL = no index) |
possible_keys |
Indexes MySQL considered |
rows |
Estimated rows examined (lower is better) |
Extra |
Notes like "Using index", "Using filesort", "Using temporary" |
type Column — Best to Worst| type | Meaning | Quality |
|---|---|---|
system / const |
Single-row lookup (PK/unique) | Excellent |
eq_ref |
One matching row per join | Very good |
ref |
Indexed non-unique lookup | Good |
range |
Index range scan | Acceptable |
index |
Full index scan | Poor |
ALL |
Full table scan | Worst |
type = ALL (full table scan), key = NULL (no index used), a high rows value, or Using filesort / Using temporary in the Extra column.
-- Basic execution plan
EXPLAIN SELECT * FROM employees WHERE dept_id = 10;
-- More readable formats
EXPLAIN FORMAT=JSON
SELECT * FROM employees WHERE dept_id = 10;
-- Actually run it and show real timings (MySQL 8.0.18+)
EXPLAIN ANALYZE
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 50000;