indexing
Composite Indexes & Leftmost Prefix
A single index on multiple columns can serve many queries — but only if the WHERE clause uses the columns from the left.
A composite index covers several columns in one structure, and used well it can serve a whole family of queries with a single index instead of many. The catch — and it is the single most-tested fact about composite indexes — is that column order matters enormously, governed by the leftmost-prefix rule.
A phone book sorted by last name, first name, middle initial. Useful for "Smith, John" — not for "anyone named John".
Key Concepts
1
A composite index on (a, b, c) sorts rows first by a, then by b within each a, then by c. That ordering means the index can be used for queries that filter on a leftmost prefix of the columns: a alone, a and b, or a, b, and c together. It cannot efficiently serve a query that filters only on b, or only on c, or on b and c without a — because without a value for a the index's sort order gives no help in locating those rows, just as a phone book sorted by last-then-first name is useless for finding everyone with a given first name. A subtlety: a range condition on a column "uses up" the index for everything to its right, so with (a, b, c) and a query a = ? AND b > ? AND c = ?, the index helps with a and the b range but cannot also seek on c.
(a, b, c)abca = ? AND b > ? AND c = ?
2
The practical guidance interviewers want is how to order the columns: put the most selective and most frequently equality-filtered columns first, place range-filtered columns last, and order to match your common query patterns and ORDER BY clauses. Done thoughtfully, one well-ordered composite index replaces several single-column ones, speeding reads while keeping the write and storage cost of indexing down.
ORDER BY