What an Index Is

An index is a separate, sorted structure the database maintains next to a table: one column's values kept in order, each with a pointer back to its row. That is the whole definition, and it is enough to end the last page's misery — because in a sorted structure, finding customer 214 takes a few hops instead of forty thousand checks.

One line of SQL builds it. The four-second "your bookings" page drops to milliseconds, and — this is the part that surprises everyone — the query itself does not change by a single character. This page is the book's speed set-piece: what the structure is, why sorted means findable, what the primary key has been hiding all along, and one rule that lets you predict an index's behavior before you build it.

Sorted Means Findable

Start with the object this chapter is named after in spirit: the phone book. Ten thousand names, and yet nobody has ever found "Novak" by flipping every page. You cut straight to N with your thumb, open somewhere close, see you are at "Ne", jump forward a little, and land. Three, four moves. The names didn't cooperate; the ordering did.

Here is why order changes everything. In a sorted list, one look tells you which half of the list your target is in — so you can discard the other half without reading it. Halve forty thousand and you get twenty thousand; keep halving and you reach a single entry in about sixteen steps. Sixteen looks instead of forty thousand. And that is the pessimistic version: a real index is even shallower, because each hop narrows by hundreds at a time rather than by half. A handful of hops, done. (The exact structure engines use has a name, the B-tree, and its internals belong to a deep-dive course — the sorted-list picture is the honest simplified version.)

An index is that phone book, built for one column of your table. Take every customer_id in bookings, keep them in sorted order, and next to each one store a pointer to the row it came from. The table itself does not move — the rows stay exactly where they were. The index is a companion structure, and the database keeps it current automatically: every new booking gets its entry filed in the right sorted spot the moment the row is written.

Creating One

Here is the line that fixes the Marquee's slow page. It asks the database to build an index named idx_bookings_customer, on the bookings table, over the customer_id column:

CREATE INDEX idx_bookings_customer
ON bookings (customer_id);

The database reads the table once, builds the sorted structure, and from then on maintains it on every write. The name is yours to choose; the habit of naming it idx_table_column just keeps things findable later.

Now the remarkable part. Here is the query the website runs after the index exists:

SELECT screening_id, seat, booked_at
FROM bookings
WHERE customer_id = 214;

Identical. You do not mention the index, point at it, or ask for it. Back in Chapter 3 we compared a query to ordering from a menu: you name the dish, the kitchen decides how to cook it. That promise pays off here — the kitchen just acquired a faster recipe, and your order didn't change. The engine sees the WHERE on customer_id, notices a sorted structure for exactly that column, hops through it to 214, and follows twelve pointers to twelve rows.

Count the work: a handful of hops plus twelve row fetches, instead of forty thousand checks. Same machine, same data, same query — routinely a 1000× difference. That is why CREATE INDEX is the highest-impact three words in this field.

What the Primary Key Had All Along

A confession is owed from Chapter 2. Primary keys come pre-indexed: the moment you declare one, the engine builds an index on it without being asked. It has to — to refuse a duplicate id instantly, it needs a fast way to check whether that id already exists, and a fast way to find one value is precisely what an index is.

This explains two things at once. It explains why looking up a booking by booking_id was always instant, at eight hundred rows and at forty thousand alike. And it explains why the Marquee's pain arrived on customer_id first: it is a plain column, not a key, so nothing had ever built an index for it. Fast-by-id, slow-by-everything-else is the signature of a table wearing only its automatic key index.

Multi-Column Indexes and the Phone-Book Rule

An index can cover two columns, and the phone book has been demonstrating how for a century. A phone book is sorted by surname, then by first name within each surname: all the Novaks together, and inside the Novak block, Ruth before Boris. One ordering, two columns deep.

An index on (screening_id, seat) is sorted the same way: all of screening 88's entries together, and within them the seats in order. That makes it excellent for two kinds of question — both columns, or the left column alone:

SELECT booking_id FROM bookings
WHERE screening_id = 88 AND seat = 'G7';

SELECT COUNT(*) FROM bookings
WHERE screening_id = 88;

Both fly, for phone-book reasons: finding "Novak, Ruth" is instant, and so is finding the whole Novak block. But now try the right column alone — "everyone whose first name is Anna". The phone book is useless for that; the Annas are scattered through every surname, and you are back to flipping every page. Same here:

SELECT booking_id FROM bookings
WHERE seat = 'G7';

Seat G7 bookings are scattered across every screening block, so the engine full-scans as if the index didn't exist. This is the leftmost rule: a multi-column index helps queries that pin down its columns starting from the left. Both columns, yes; the left alone, yes; the right alone, nothing. It is the one rule that lets you predict an index's usefulness before creating it — and if a question like "who is in seat G7 across all screenings" ever became a daily one, the fix would be a second index on seat, the way a second directory sorted by street would fix the phone book.

And now the reveal this chapter has been saving. In Chapter 6 you put a line in the bookings table — UNIQUE (screening_id, seat) — so no seat could ever be sold twice for the same screening. To enforce that instantly, the engine built a sorted structure over exactly those two columns. You have owned this index since Chapter 6. The constraint that made double-booking impossible and the index that makes seat lookups fast are one and the same object: safety and speed, one structure.