Topic 26 of 52 · Advanced
Indexes & EXPLAIN
How B-tree indexes speed up queries and how to read a plan.
Concept
An index is a sorted B+tree on one or more columns that lets MySQL find rows without scanning the whole table. InnoDB's primary key is the clustered index — rows are stored in its order.
Composite indexes follow the leftmost-prefix rule: an index on (a, b, c) helps filters on a, a+b, a+b+c, but not b alone.
EXPLAIN shows the plan. Watch type (ALL = full scan, ref/range = good), key (index used), rows (estimate) and Extra (Using filesort/temporary are warnings).
Code (SQL · MySQL 8)
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);
EXPLAIN
SELECT * FROM orders
WHERE customer_id = 42 AND order_date >= '2026-01-01';
-- This can NOT use the index (function on column):
-- WHERE YEAR(order_date) = 2026
-- Rewrite as a range:
-- WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'Remember
- Indexes speed reads but slow writes.
- Leftmost-prefix rule for composite indexes.
- A covering index answers the query from the index alone.
Interview questions
Clustered vs non-clustered index?
A clustered index defines the physical row order (one per table, the PK in InnoDB). Secondary indexes store key + PK and need a lookup back to the row.
Why might a query ignore an index?
Functions on the column, leading wildcard LIKE '%x', implicit type casts, low selectivity, or not matching the leftmost prefix.
Lesson test
Take an 8-question test on “Indexes & EXPLAIN” — concepts, output prediction, bug spotting and interview questions.