← SQL roadmap

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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Indexes & EXPLAIN” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.