A Phone Book Sorted by Surname Then First Name
Last timeWhat Happens on a Write
An index on two columns sorts by the first and breaks ties with the second. Every rule about which queries it serves follows from that one sentence.
Real queries filter on more than one column, and an index can hold more than
one. Which queries a composite index serves is the single most useful thing in
this course to be able to work out in your head, and it all comes from how the
keys are ordered.
The key is a pair
An index on customer and then date sorts by customer. Where two rows have the
same customer, it sorts those by date. That is the entire definition.
The phone book is the right picture and not a loose analogy: a book sorted by
surname, then by first name within each surname, has exactly this structure and
exactly these limitations.
| step | position | customer | date | what it is next to | what happened |
|---|---|---|---|---|---|
| 1 | 1 | A-114 | 2024-01-03 | the other A-114 rows | All rows of one customer are adjacent, because customer is the first thing sorted on. |
| 2 | 2 | A-114 | 2024-06-21 | the other A-114 rows | Within that customer, rows are in date order, because date breaks the tie. |
| 3 | 3 | A-114 | 2024-11-02 | the other A-114 rows | Three rows of one customer, contiguous and internally ordered by date. A query for this customer in that date range reads exactly this run. |
| 4 | 4 | B-207 | 2024-01-09 | the other B-207 rows | A new customer starts. Note what has happened to the dates: January follows November, because the date ordering only holds within a customer. |
| 5 | 7 | C-003 | 2024-01-15 | the other C-003 rows | January again. Every January row in the table is scattered across the index, once per customer, which is why a filter on date alone cannot use this index at all. |
The leftmost rule
From that layout, the rule follows without memorisation. A filter can narrow
the search only if it constrains the columns from the left, with no gaps.
The lesson stops here
5 more paragraphs to go
You have read the opening. The rest of the argument, the problems that check whether it landed, and the lines worth keeping at the end all come with a plan.
The first lesson of every course in the library reads the whole way through, free, so you can see exactly what the rest of them are.
See the planThe contentsThis is the reading half
Starting the course gives you your own copy of it. Every idea on every page has problems standing under it, marked with a reason rather than a tick, and any sentence you do not believe can be opened and argued with. None of that can happen on a page nobody owns.
The contents