Ordered By Columns
A composite index on columns a, b, c sorts rows first by a, then by b within equal a, then by c. This ordering means the index is only useful when your query constrains a leftmost prefix of those columns.
The Prefix Rule
The index helps queries that filter on:
- column a alone
- columns a and b together
- columns a, b, and c together
It does not efficiently serve a query that filters only on b or only on c, because those values are scattered throughout the index. Skipping the leading column breaks the sorted order the engine relies on.
Practical Consequences
- Put the column used in the most queries, or the most selective equality column, first.
- A range predicate on a middle column stops the prefix from extending to later columns for further seeking.
- One well ordered composite index can replace several single column indexes.
Key idea
A composite index is sorted left to right, so it only serves queries that constrain a leftmost prefix of its columns starting from the first one.