A Method Instead of a Guess
Last timeWhat Every Index Costs
A slow query is fixed by a short ordered procedure, not by adding indexes until something improves. Two numbers from the plan decide almost everything that follows.
Everything so far has been description. This lesson is the procedure: what to do,
in what order, when a query is slow and somebody is waiting.
The order matters more than any individual technique. The common failure is not
ignorance about indexes but changing something plausible, seeing no improvement,
changing something else, and repeating, until the table carries half a dozen
speculative indexes, the writes are slower, and the query is still slow.
Start with what the database already knows
Ask the system what it did. Every major database will report the plan it chose,
the rows each step actually produced, and the time each step consumed. That one
output answers the three questions the diagnosis needs: where the time
went, how many rows were really touched, and whether the estimates were close.
Reading it takes a minute and replaces an hour of speculation.
Two numbers in it decide almost everything that follows.
The first is rows examined against rows returned. A step reading nine million
rows to produce forty is doing something far more expensive than it needs to, and
the remedy is in the access path, meaning an index or a condition rewritten so
one can be used.
The second is estimated rows against actual rows. A step the planner expected to
produce two hundred rows that actually produced ninety thousand chose well for a
query it was told it was running, and was told wrong. No index will fix that, but
the statistics will.
| effort, one to four | possible gain, one to fo | ongoing cost, one to fou | how often it applies, on | |
|---|---|---|---|---|
| ask for fewer rows | 1 | 4 | 1 | 4 |
| rewrite the condition | 2 | 4 | 1 | 3 |
| add or widen an index | 2 | 3 | 3 | 4 |
| fix the statistics | 1 | 3 | 1 | 2 |
| change the data shape | 4 | 4 | 4 | 2 |
Five moves, and that is the whole list
The order in the table is close to the order to try them in. Asking for fewer
rows is free and often available, since a query returning fifty thousand rows to
a page showing twenty is not a database problem. Changing the data shape is last
because it is the only move that changes what other code can assume.
The lesson stops here
4 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