EXPLAIN & Query Analysis
IntermediateEXPLAIN shows the execution plan: type (const, eq_ref, ref, range, ALL), key used, rows scanned, and Extra flags like Using filesort, Using temporary — key inputs for tuning.
Overview
EXPLAIN is MySQL's primary tool for understanding how the query optimiser executes a statement. It reveals which indexes are chosen, how many rows are estimated to be scanned, and whether expensive operations like filesorts or temporary tables are needed. Reading EXPLAIN output is the first step in every query-tuning workflow. MySQL 8.0 also introduced EXPLAIN ANALYZE, which actually executes the query and shows real timings alongside estimates — invaluable when estimates diverge from reality. Understanding the access-type ladder (ALL → index → range → ref → eq_ref → const/system) tells you exactly where performance is being lost and which index changes will help.
Reading the EXPLAIN Output
The most important columns in EXPLAIN are: `type` (access method), `key` (index chosen), `rows` (estimated row scans), `filtered` (% of rows passing WHERE after the index), and `Extra` (additional flags). The `type` column is the performance ladder — from worst to best: ALL (full table scan), index (full index scan), range (index range scan), ref (non-unique index lookup), eq_ref (unique index, one row per join), const/system (single-row match by primary key).
-- Sample schema
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
status VARCHAR(20),
created_at DATETIME,
INDEX idx_user (user_id),
INDEX idx_status_created (status, created_at)
);
-- 1. Full table scan — no usable index on 'amount'
EXPLAIN SELECT * FROM orders WHERE amount > 100;
-- type: ALL, key: NULL, rows: ~50000, Extra: Using where
-- 2. Range scan — leftmost prefix of composite index
EXPLAIN SELECT * FROM orders WHERE status = 'PENDING' AND created_at > '2024-01-01';
-- type: range, key: idx_status_created, rows: ~200, Extra: Using index condition
-- 3. eq_ref in a JOIN — primary key lookup
EXPLAIN
SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'PENDING';
-- orders: type: ref, key: idx_status_created
-- users: type: eq_ref, key: PRIMARYDangerous Extra Flags — Using filesort & Using temporary
`Using filesort` means MySQL had to sort result rows in memory (or on disk) because no index covers the ORDER BY columns. `Using temporary` means a temp table was created — common with GROUP BY on non-indexed columns or DISTINCT across joined tables. Both flags indicate expensive operations that should be eliminated by adding the right indexes or rewriting the query.
-- BAD: filesort because ORDER BY column is not in the index
EXPLAIN SELECT * FROM orders WHERE status = 'PENDING' ORDER BY user_id;
-- Extra: Using index condition; Using filesort
-- FIX: add status + user_id composite index to cover both WHERE and ORDER BY
ALTER TABLE orders ADD INDEX idx_status_user (status, user_id);
EXPLAIN SELECT * FROM orders WHERE status = 'PENDING' ORDER BY user_id;
-- type: ref, key: idx_status_user, Extra: Using index condition ← no filesort!
-- BAD: temporary table for GROUP BY on unindexed column
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Extra: Using temporary; Using filesort (pre-8.0 without skip-scan)
-- FIX: index on status alone
ALTER TABLE orders ADD INDEX idx_status (status);
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;
-- Extra: (none) — group by resolved via indexEXPLAIN ANALYZE — Real Execution Timings (MySQL 8.0+)
EXPLAIN ANALYZE actually runs the query and returns both estimated and actual values for rows, loops, and execution time at each step in the tree-format plan. This is critical when the optimiser's row estimates are wildly off (stale statistics, skewed data, complex JOINs). The output is in tree format — read it inside-out: innermost nodes execute first.
-- EXPLAIN ANALYZE runs the query — don't use on expensive writes without ROLLBACK
EXPLAIN ANALYZE
SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'SHIPPED'
ORDER BY o.created_at DESC
LIMIT 20;
/* Example output (simplified):
-> Limit: 20 row(s) (actual time=3.2..3.3 rows=20 loops=1)
-> Sort: o.created_at DESC (actual time=3.1..3.2 rows=20 loops=1)
-> Nested loop inner join (actual time=0.4..2.9 rows=340 loops=1)
-> Index lookup on o using idx_status_created (status='SHIPPED')
(estimated rows=300 actual rows=340 loops=1)
-> Single-row index lookup on u using PRIMARY (id=o.user_id)
(estimated rows=1 actual rows=1 loops=340)
Key: estimated rows=300 vs actual rows=340 → estimates are accurate here.
If estimated=10 but actual=340000, update statistics:
ANALYZE TABLE orders;
*/
-- Force statistics refresh
ANALYZE TABLE orders;Key Points to Remember
- 1`type: ALL` means a full table scan — always investigate; add an index or rewrite the query.
- 2Access type ladder (best → worst): const → eq_ref → ref → range → index → ALL.
- 3`Using filesort` and `Using temporary` in Extra are red flags for missing indexes on ORDER BY / GROUP BY columns.
- 4A composite index (a, b) covers `WHERE a = ? ORDER BY b` — no filesort needed if column order matches.
- 5EXPLAIN ANALYZE (MySQL 8.0+) reveals actual vs estimated row counts — use it when estimates look wrong.
- 6Run `ANALYZE TABLE` after bulk inserts to refresh the InnoDB statistics used by the optimiser.
Interview Questions
Sign in to ask AriaWhat does `type: ALL` mean in EXPLAIN and how do you fix it?
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
How would you eliminate "Using filesort" for a query with ORDER BY?
A query hits an index yet is still slow — what EXPLAIN columns would you check next?
The optimiser picks a wrong index. How do you investigate and fix it?
Ask Aria about EXPLAIN & Query Analysis
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.