Be able to explain where a query's time actually goes, what an index is and when it cannot help, why the database's plan is built on a guess, how two tables get joined, how to read the plan you are given, what every index costs, and which move to reach for when something is slow

Why a Query Is Slow

A slow query is almost never slow for a mysterious reason. It is reading far more rows than it needs to, and the whole subject is about seeing which rows and why.

8 lessons, written and corrected before you arrived. Reading them here needs no account. The first reads the whole way through; the others open and then stop, because a page nobody owns cannot tell who is reading it. Starting the course gives you your own copy, where every idea has problems standing under it and you can ask about any sentence.

Start reading

01What a Query Is Actually SpendingA query's time is almost entirely the number of rows the database had to look at, multiplied by how expensive it was to reach each one. Everything else is detail.
02What an Index Actually Isopening onlyAn index is a second copy of a few columns, kept in order, with a pointer back to each row. Everything it can and cannot do follows from that one sentence.
03The Conditions No Index Can Rescueopening onlyAn index is useful only when the thing being compared is the thing that was stored in order. A surprising number of ordinary-looking conditions quietly break that, and one does not break it at all.
04Every Plan Rests on an Estimateopening onlyThe database chooses a plan by predicting how many rows each step will produce. Those predictions come from a small sample, and when one of them is badly wrong the plan is wrong with it.
05The Three Ways to Joinopening onlyThere are only three ways to match rows in one table against rows in another, each wins in a different situation, and most slow joins are the wrong one of the three.
06How to Read a Planopening onlyA plan looks like an intimidating wall of text and is really a small tree with four numbers per node. Knowing which four and in which direction to read turns it into a diagnosis.
07The Bill for Every Indexopening onlyAn index makes some reads fast and makes every write to its table slower, takes space, competes for memory and has to be maintained forever. The bill is real and usually unexamined.
08A Method Instead of a Guessopening onlyA 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.