Part 5 of 8 · 3 min read
Composite indexes and column order
One index over several columns, and why the order of the columns decides what it can do.
Think of a phone book sorted by last name, then first name. It finds everyone named Sharma, and it finds Asha Sharma. It cannot find everyone named Asha, because the Ashas are scattered across the book.
The book calls the most common multi-column index a concatenated index: several fields appended into one key, in the order given in the definition. The phone book is its example, and it adds that the index is useless for a search by the second field alone.
Composite index
A composite index is an index on several columns together, sorted by the first column, then by the second within equal first values, and so on.
Example: an index on (user_id, created_at) lists the orders of user 1 by time, then the orders of user 2 by time.
The rule for using it is called the leftmost prefix rule: the index helps queries that fix a prefix of its columns, from the left.
A worked example. Index on (user_id, created_at). The orders table has 10 million rows and 1 million users.
| Query | Uses the index? | Why |
|---|---|---|
WHERE user_id = 7 |
Yes | Prefix of one column |
WHERE user_id = 7 AND created_at > '2026-10-01' |
Yes | Prefix plus a range on the next column |
WHERE user_id = 7 ORDER BY created_at DESC LIMIT 20 |
Yes, and no extra sort | Rows of user 7 are already in time order |
WHERE created_at > '2026-10-01' |
No | The first column is not fixed |
The third query is the common one: "the latest 20 orders of a user". With the composite index, the database goes to user 7's section, reads backwards from the end and stops after 20 entries. With only an index on user_id, it would find all 10 of the user's orders (or 10,000 for a big customer), fetch them all, sort them and take 20.
Choosing the order of columns:
- Put columns tested for equality first.
- Put the column used for range or sort after them.
- Among equalities, put the more selective one first when queries differ, but remember the prefix rule: a query that never fixes the first column cannot use the index.
Example: for WHERE status = 'paid' AND user_id = 7 ORDER BY created_at, the index (user_id, status, created_at) or (status, user_id, created_at) both serve it, but (user_id, ...) also serves queries that give only the user, which are more common.
A rule against waste: an index on (user_id, created_at) makes a separate index on (user_id) redundant. The first already serves queries on its prefix.
Another case in the book, the multi-dimensional query. A search for restaurants inside a map rectangle needs a range on latitude and a range on longitude at once. A standard tree index can give a range on one of them but not both together. Section 6 returns to this.
Common mistake
Creating an index on each column separately and expecting them to work as a pair. Most databases can combine two single-column indexes, but usually far less efficiently than one composite index designed for the query.
Check yourself:
- For an index on
(a, b, c), which of these can it serve:a = 1,b = 2,a = 1 AND c = 3? - Why does
(user_id, created_at)serve "latest 20 orders of a user" without a sort?