IndexesIntermediate
How do you order columns in a composite index?
“Given a query, how do you decide the column order in a multi-column index?”
What this tests
- Leftmost prefix
- Equality-before-range
- Sort avoidance
Answers by level
Read the beginner answer first and notice what is missing.
Equality columns first, then at most one range column, then columns needed for ordering or covering. A B-tree is usable for a leftmost prefix, through equalities, stopping at the first range.
An index on (a, b, c) also serves (a) and (a, b), so those single-column indexes become redundant. Matching the ORDER BY to the trailing columns removes the Sort node.
Green flags · Red flags
Strong green flag · Explains why a range column ends prefix usability.
Green flags
- Equality-then-range rule
- Knows (a,b) subsumes (a)
- Connects order to Sort avoidance
Red flags
- Column order "as written"
- Range column first
Follow-up questions
F1
Index (country, city, age): can WHERE city = ? use it?
Scenario
A query filters user_id = ? and ranges on created_at and sorts by created_at. Design the index.