Why Queries Get Slow

The Marquee has grown. Two screens, a loyal crowd of regulars, and a bookings table that has quietly collected forty thousand rows. One evening a regular opens the website's "your bookings" page and waits four seconds for it to load — a page that answered instantly last spring. Lora checks: nothing is broken. No error, no crash, every booking present and correct. The database is doing exactly what it was asked.

That last sentence is the whole mystery, and this page unpacks it. Slowness is not bad luck and not decay; it has a mechanism, and once you can see the mechanism you can predict it, explain it, and — starting next page — fix it. So before we sell the cure, let's watch the disease do its honest work.

The Full Scan

Here is the query behind the slow page. The website asks the bookings table for every row belonging to customer 214 — one of Lora's regulars — so it can show her what she has booked:

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

Chapter 3 taught how WHERE thinks: one true-or-false test per row, keep or discard. But it never asked which rows get tested. The answer, unless something better exists, is all of them. The engine reads the first row of bookings, tests it, reads the second, tests it, and continues to row forty thousand. This method has a name: the full scan — read everything, test everything, keep what passes.

Picture an unnumbered cloakroom: a guest hands you a ticket, and because the hangers have no numbers, finding her coat means checking every hanger until you spot it. The coats didn't get heavier over the year — there are just more hangers. That is the full scan: the table is the rack, each row is a coat, and the WHERE test is you squinting at every single ticket. Analogy mapped; back to the real terms.

The result of our query is twelve rows — customer 214 has twelve bookings. To find those twelve, the engine performed forty thousand checks. The answer is correct, the method is honest, and the arithmetic is brutal: 39,988 of those checks existed only to be discarded.

Worse, the cost is linear: it grows in step with the table. Double the rows, double the checks, double the wait. Nothing about the query has to change for it to get slower — the table growing underneath it is enough.

Why It Doesn't Hurt at First

Here is why nobody notices the full scan early on. When the Marquee opened, bookings held eight hundred rows, and a modern machine tests eight hundred rows faster than you can blink. The full scan was invisible — not absent, invisible. Every query the reader has written since Chapter 3 was full-scanning happily, and it never mattered.

Growth is what changes the verdict, all by itself. "It was fine last month, and we didn't touch anything" is the classic report, and it is entirely accurate: nobody touched anything. The query is the same, the schema is the same, the machine is the same. The only variable that moved is the row count, and for a linear method the row count is the whole bill.

Where It Hurts

The pain shows up in three familiar places. The first is the one we just watched: a WHERE on a column with no help — customer_id, an email, a date — forces the test-every-row treatment. Lookups by primary key never seem to suffer this way, and the next page explains the suspiciously convenient reason why.

The second place is JOINs. A JOIN matches rows across tables, and matching done by brute force means comparing each booking against the customers table row by row. Multiply two tables' row counts and the checks pile up far faster than either table grows alone.

The third is ORDER BY. Back in Chapter 3 we said order is never free, and here the price tag finally arrives: "the five most recent bookings" sorts all forty thousand rows just to keep five. The LIMIT trims the answer, not the work.

Measurement Beats Vibes

All of this compresses into one question, and it is the best performance instinct this book can hand you: how many rows must the engine touch to answer? Not "is the server fast", not "is SQL slow" — how many rows get touched. Our query touches forty thousand to deliver twelve. That ratio is the slowness, stated as a number.

The question also points straight at the fix. If the engine could somehow touch only the twelve rows that matter, the same query on the same machine would answer in a blink. Something would have to know, in advance, where those twelve rows live. That something is an index, it is one line of SQL away, and it is the next page. The page after next shows how to stop guessing entirely and ask the engine itself how many rows it plans to touch.