9.4 How do composite indexes work, and what is the leftmost-prefix rule?

Using indexes well

In a book: A phone book is sorted by surname, then first name. You can find "Rao, Asha" instantly, or every Rao. But you can't find everyone called "Asha": Ashas are scattered across every surname.

In the system: A composite index covers several columns, sorted by the first column, then by the second within each value of the first, and so on.

Figure: the index helps queries that start from the left column; it can't jump on the second column alone.

The leftmost-prefix rule: an index on (a, b, c) can be used to seek on a, on a and b, or on a, b and c, but not on b alone or c alone. For the bookstore:

• WHERE user_id = 7 → uses the index.
• WHERE user_id = 7 AND created_at > '2026-02-01' → uses both columns.
• WHERE user_id = 7 ORDER BY created_at DESC → uses the index and skips sorting, because rows are already in date order inside user 7. Perfect for the "My orders" page.
• WHERE created_at > '2026-02-01' → can't seek. In the test run, SQLite reported SCAN orders USING COVERING INDEX: it read through the whole index, which is better than the whole table but still a scan.

Column order rules of thumb:

1. Columns compared with equality (=) first.
2. Then the column used for a range or sort.
3. Among equality columns, think about which queries you need to serve, so one index can help several of them.

(Some databases can "skip scan" an index on its second column in special cases. Don't design around it.)

Memory code: "Phone books find surnames, not first names."