What is a composite index and what is the leftmost prefix rule?
A composite index covers several columns in a fixed order. It can be used for queries filtering on its leading columns, such as (a) or (a, b), but generally not for a filter on b alone.
Explain it simply
A phone directory is sorted by surname, then by first name. You can look up "Sharma", or "Sharma, Rahul". But finding everyone named "Rahul" regardless of surname forces you to read the whole directory, because the second column is only sorted within each surname.
Example
CREATE INDEX idx_city_name ON students(city, name);| Query filter | Uses the index well? |
|---|---|
WHERE city = 'Pune' | Yes, leading column |
WHERE city = 'Pune' AND name = 'Asha' | Yes, both columns |
WHERE name = 'Asha' | Usually no, skips the leading column |
WHERE city = 'Pune' ORDER BY name | Yes, already sorted |
Design tip: put columns used with equality first, and a range column last. An index on (city, joined_on) serves city = 'Pune' AND joined_on > '2026-01-01', while (joined_on, city) would not narrow as well.
Some databases, like MySQL 8 and Oracle, can sometimes use skip scans, but do not rely on it.
What to say in the interview
"A composite index is sorted by its first column, then the second within it, so queries must use the leftmost columns to benefit. I order columns by equality filters first, then ranges, based on the real queries."
Common mistakes
- Creating (a) and (a, b) indexes together, when (a, b) already covers queries on a.