Lesson 5 of 11 · 24 min
Composite indexes and column order
By the end of this lesson you can design a multi-column index for a given query, apply the leftmost-prefix rule, put equality columns before range and sort columns, and recognise a plan that avoids a Sort thanks to the index.
A composite index (multi-column index) stores entries sorted by the first column, then, within equal values of the first column, by the second column, and so on. A phone book sorted by (last name, first name) is the classic picture: all the Al-Harbis are together, and among them the first names are in alphabetical order.
That sort order explains everything about composite indexes:
- With
(user_id, occurred_at), all of one user's events sit next to each other, in time order.WHERE user_id = 42jumps to that block;AND occurred_at >= '2025-03-01'narrows to a slice inside the block;ORDER BY occurred_at DESC LIMIT 10just reads the end of the block. WHERE occurred_at >= '2025-03-01'alone gets little help: one day's events are scattered across the blocks of 18,500 different users.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons