Beyond the Primary Key
A secondary index lets you look up rows by a column other than the primary key, for example by email. A composite index covers several columns together, such as last name then first name, stored in that exact order.
Order Matters
A composite index on columns A then B can serve queries that filter on A alone, or on A and B together. It cannot efficiently serve a query that filters only on B, because B is sorted only within each value of A. This is the leftmost prefix rule.
Choosing Wisely
- Put the column used for equality filters first.
- Put a range column last, since the index stops being useful for columns after a range.
- A composite index can be covering, returning results from the index alone without touching the table.
Key idea
A secondary index speeds lookups on one column, while a composite index serves filters that match its leftmost prefix, so column order in the index is a deliberate design choice.