One Index Many Columns
A composite index spans several columns, for example on a column pair like customer and order date. The database sorts entries by the first column, then by the second within each first value, and so on. This left to right ordering is the whole story.
The Leftmost Rule
A composite index can be used only when the query constrains a leftmost prefix of its columns:
- Index on a b c serves filters on a, on a and b, and on a b c.
- It does not efficiently serve a filter on b alone or on c alone, because those are not a left prefix.
Think of a phone book sorted by last name then first name: great for finding a last name, useless for finding everyone with a given first name.
Ordering For Your Queries
Put the column used for equality filters first, and the column used for ranges last:
- Equality on the leading columns narrows the search to a contiguous block.
- A range on the final column can then be walked within that block.
A range in the middle stops later columns from being used for seeking.
Key idea
A composite index is sorted left to right, so it serves only leftmost prefixes of its columns, and ordering equality columns before range columns lets a query seek a contiguous block.