What Indexes Cost

After the last page, a fair question forms: if one line of SQL buys a 1000× speedup, why doesn't the engine simply index every column of every table? Engines are built by people who love speed — if indexes were free, they would. They don't, because indexes are not free. Every index is a promise the database must keep on every write, forever.

This page is the honest invoice. The craft of indexing is not knowing how to create one — you learned that in three words. It is reading the bill before buying, and knowing when to stop.

Writes Pay for Reads

Consider what the bookings table is now carrying. It has the automatic index on its primary key, the index the UNIQUE (screening_id, seat) constraint built, and our new idx_bookings_customer. Suppose Lora later adds a fourth, on booked_at, for date reports. Now watch what one perfectly ordinary write has to do:

INSERT INTO bookings (customer_id, screening_id, seat)
VALUES (214, 88, 'G12');

The row goes into the table — that part was always there. But the promise of an index is that it is always current, so the write cannot stop there. The primary-key index files the new booking_id in its sorted place. The (screening_id, seat) index checks for a duplicate and files the new pair. idx_bookings_customer files another entry under 214. The booked_at index files the timestamp. One INSERT, five structures updated — the table plus four filings, each into its own sorted order.

A library ran into this problem long before software did. A library with a card catalog doesn't file one card per new book — it files one card per catalog: one in the author catalog, one in the title catalog, one in the subject catalog. Every catalog makes some search fast, and every catalog demands its card for every new book, including catalogs nobody has opened in years. Analogy mapped; back to tables.

That is the trade in one sentence: writes pay for reads. Every index makes some read faster and every write slower. At the Marquee the till slows a little so the reports can fly — usually a fine trade, because bookings are read far more often than they are written. But it is a trade, not a gift, and no benchmark is needed to see it: five filings simply cost more than one.

Space Is Real

The second line on the invoice is storage. An index is a copy of its column's values plus a pointer per row — real bytes on a real disk, roughly proportional to the table itself. A table wearing many indexes can end up spending more disk on its indexes than on its own rows. No tuning advice belongs at this altitude; just know the copy is physical, not free.

Unused Indexes Are Pure Cost

Now combine the two costs with a hard truth: an index only earns its keep when queries actually use it. An index that no query ever touches still files its entry on every INSERT, still adjusts on every UPDATE of its column, still occupies its disk — forever, for nothing. It is the subject catalog nobody opens: pure cost, zero return.

This is why professionals index observed pain, not imagined queries. The temptation, right after learning CREATE INDEX, is to sprinkle indexes over every column that might someday be searched. Resist it. Last page's question — how many rows must this touch? — identifies the queries that genuinely hurt; those columns earn indexes. The hypothetical ones can wait until they stop being hypothetical, because an index can be added in one line the day the pain appears.

One reassurance makes the whole subject low-stakes: dropping an index destroys no data. The index is derived from the table — the same store-facts-derive-conclusions principle from Chapter 3 — so it can always be rebuilt from what the table already knows. Deleting one loses speed, never facts. Index decisions are reversible, which is precisely what makes "add it when it hurts" a safe strategy.

The Default Posture

So here is the posture to carry out of this chapter. Your keys are already indexed — primary keys and UNIQUE constraints saw to that, which covers by-id lookups and the relationships JOINs travel along. Add an index where a WHERE or a JOIN measurably hurts, using the rows-touched question as your test. Then stop. Some indexes, chosen; not all indexes, hoarded.

"Measurably" is doing quiet work in that sentence — it assumes you can check what the engine is actually doing, rather than guessing. The next page hands you exactly that tool: a way to ask the engine, before running anything, which route it plans to take.