InnoDB indexes are B+trees: balanced trees where every leaf sits at the same depth and leaves are linked for fast range scans. A lookup is O(log n) — a handful of page reads even on huge tables.
A composite index sorts rows by , then , then . You can use it only for a of the columns: , , or — but alone or , because those columns aren't sorted until is fixed.
(a, b, c)abca(a, b)(a, b, c)b(b, c)aCREATE INDEX idx_abc ON events (user_id, status, created_at);
-- USES the index (leftmost prefix, range on the last column)
SELECT * FROM events
WHERE user_id = 7 AND status = 'open' AND created_at > '2026-01-01';
-- Does NOT use it: skips the leftmost column
SELECT * FROM events WHERE status = 'open';
Order columns most-selective / equality-first, range column last: once you hit a range (>, BETWEEN), columns after it can't be used for seeking.
If an index contains every column the query reads, InnoDB answers from the index alone — no trip back to the clustered index. EXPLAIN shows Using index.
CREATE INDEX idx_cover ON events (user_id, status, id);
EXPLAIN SELECT id FROM events WHERE user_id = 7 AND status = 'open';
-- Extra: Using index <- covering, no clustered-index lookup
Composite and covering indexes are how you turn a slow multi-condition query into a single index seek. Getting column order wrong — or expecting a query on the second column to use the index — is the most common reason an index that "exists" still doesn't get used.
A library of IT interview questions with detailed answers — from Junior to Senior.
Donate