EXPLAIN shows the plan the optimizer chose — how it will access each table — without running the query. You read it to confirm indexes are used and few rows are examined.
EXPLAIN shows the plan the optimizer chose — how it will access each table — without running the query. You read it to confirm indexes are used and few rows are examined.
const / eq_ref / ref (index lookups, good) → range → index (full index scan) → ALL (full table scan, the red flag).NULL = none).Using index (covering, great), Using filesort / Using temporary (sorting/grouping in memory or on disk — often optimizable).EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at;
-- +------+------+------+---------+--------+----------------+
-- | type | key | rows | Extra |
-- | ALL | NULL | 900k | Using where; Using filesort | <- scans everything, sorts
-- +------+------+------+---------+--------+----------------+
-- Add an index that covers the filter AND the sort order:
CREATE INDEX idx_cust_created ON orders (customer_id, created_at);
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at;
-- | type | key | rows | Extra |
-- | ref | idx_cust_created | 30 | Using where | <- seeks 30 rows, no filesort
The index turned an ALL scan of 900k rows into a ref seek of 30, and because created_at is the second column, the rows come out pre-sorted — the filesort disappears.
Use EXPLAIN ANALYZE (MySQL 8) to also see actual timing and row counts, catching cases where the estimate is wrong.
EXPLAIN is how you replace guessing with evidence. Spotting type: ALL, a huge rows, or Using filesort tells you exactly where an index is missing — the fastest path from "the page is slow" to a targeted fix.
A library of IT interview questions with detailed answers — from Junior to Senior.
Donate