Databases for Beginners
by Sergey Okinchuk
Backups: the Only Real UndoTopic 34

Chapter Eight · Speed

Making It Fast — Indexes

By now the Marquee's data is shared, structured, and trustworthy — and, at forty thousand bookings, slow. This chapter shows why queries degrade as tables grow, then introduces the index: a sorted structure that turns forty thousand checks into a few hops, without changing a single query. It ends with the honest part — what every index costs on every write, and how to ask the engine itself which route it chose.

4 topics

Seven chapters in, the Marquee's database does everything the spreadsheet couldn't: one copy of the truth, rules enforced on every write, double-bookings impossible. Then the cinema grows, the bookings table reaches forty thousand rows, and a page that was instant last month takes four seconds. Nothing is wrong. Every answer is still correct. The database is simply doing what it was asked in the most patient way imaginable: checking every single row, because nothing told it a better way exists.

This chapter is about that better way. First the mechanism of slowness itself, so the cure makes sense — the full scan, and why growth alone degrades it. Then the index: what it physically is, why the query doesn't change, and the phone-book rule that predicts when it helps. Then the bill, because indexes are paid for by every write. And finally a first conversation with the query planner, the part of the engine that has been quietly choosing routes all along.

Find "Night Bus" in a phone book of 10,000 entries — two ways to search
Flip every page
The full scan · check every entry, one by one · about 10,000 checks · doubles when the book doubles
Thumb-cut to N
The index · the alphabetical order does the work · about 3 hops · barely notices when the book doubles

Topics in This Chapter

Topic 34
Why Queries Get Slow
Forty thousand bookings, a four-second page, and nothing broken. The full scan, why growth alone degrades a query that never changed, and the one question that predicts speed before any tool is opened.
Speed
Topic 35
What an Index Is
A separate, sorted structure the database keeps next to the table — values in order, each pointing at its row. CREATE INDEX, the query that doesn't change at all, the phone-book rule, and the Chapter 6 constraint that was an index all along.
Speed
Topic 36
What Indexes Cost
If indexes were free, engines would index everything. They don't — every index is a promise kept on every write. Who pays, when, and how much, plus the professional posture: index observed pain, not imagined queries.
Speed
Topic 37
A Peek at the Query Planner
EXPLAIN asks the engine how it would run your query, and the answer comes back in its own words: Seq Scan or Index Scan. Two words of literacy, why a scan is sometimes the smarter route, and where the deep dive begins.
Speed