For every question you have asked so far, the gardener has walked the flower bed. She has stopped at every flower, read every tag, and returned only the ones you wanted. This is fine when the flower bed holds ten. When it holds a hundred, it is an hour for one small question. When it holds a thousand, it is a whole afternoon.

Today she takes a different tool from her belt. At the garden gate she nails up a small wooden card — a directory. On it is written, in the gardener's careful hand, every color that grows in the flower bed and the plots where those colors live.

The next time you ask for pink flowers, she reads the card, walks straight to those plots, and comes back with what you asked for. She does not touch the flowers she did not need.

Today the flower bed is bigger. Thirty plots, not ten. The gardener has been asked a simple thing — which of them are pink? — and she does what she has always done. She starts at the first plot, reads the tag (id, species, color, height), moves on. She stops at every flower, one after the next, right through to the thirtieth.

Watch her walking-path below. It touches every plot in the flower bed, whether the flower there is pink or not. Twelve of the thirty are pink; the other eighteen she reads and passes over. Every question you have asked her, in every chapter so far, has been answered this way.

The gardener has been thinking. If she is going to be asked which are pink? often, she does not need to walk every flower each time. She needs a directory — a small card at the gate that already knows where the pink flowers live.

She draws one up. On the left of the card she writes every color that grows in the flower bed. Beside each color she writes the plot numbers of the flowers of that color. Then she nails the card to the garden gate.

The next time you ask for pink flowers, she reads the card first. It tells her: pink lives at plots 3, 5, 9, 10, 11, 13, 17, 18, 22, 24, 26, 30. She walks to those twelve plots, and to no others.

This is the mental picture for the rest of the chapter. Everything else — the syntax, the query plans, the tradeoffs — is a variation on this one image: a small wooden card at the gate, consulted before the walk.

The directory is not free. The gardener must draft it, and she must keep it up to date. In SQL she writes it like this: CREATE INDEX idx_flower_color ON flower_bed(color);. That one line tells the database — build a small card, sorted by color, listing every flower. The card lives at the garden gate from that moment on.

Run the statement below. The flower bed itself does not change. What changes is what stands at the gate: a small wooden card, drafted and nailed up. From now on, queries that ask which are pink? will consult it.

The gardener has drafted the directory on the previous page. Ask her the same question you asked on Page 3 — which are pink? — and watch what she does now. She goes first to the gate, reads the card, and it tells her exactly where to go. She walks to those twelve plots, and to no others.

If you dropped the directory (from Page 5's chip), the walk goes back to the way it was: she checks every flower, one at a time. The flower bed is the same. What has changed is what stands at the gate.

Before the gardener starts walking, you can ask her: how would you answer this? The database has a keyword for this — EXPLAIN QUERY PLAN — and its answer is exactly what you'd hope: a plan, in one line, of what she intends to do.

Run the statement below. If a directory exists that can help, she says so — "SEARCH USING INDEX". If none can help, she says "SCAN" — meaning the honest walk of every plot. Try adding and dropping the index; watch the plan change.

Not every question benefits from a directory. Some ask about the whole flower bed — how many flowers came home today? — and no card at the gate can shortcut the walk. For those, the gardener still tips the basket out and reads every tag.

But three specific shapes of question do benefit. If the column named in your question is the column at the top of a directory, the gardener consults the card first and touches only the matching plots. These are the three shapes:

WHERE is the one you already know. WHERE color = 'pink' jumps straight to the pink section of the color directory; nothing else is walked.

JOIN ... ON is a kind of WHERE across two tables. When the gardener pairs bees with flowers on b.flower_id = f.id, an index on the joined column lets her find each partner without checking every candidate. In Chapter IV she paired ten with four; in a real garden of thousands, this is the difference between an afternoon and an instant.

ORDER BY is a subtler win. A directory is already sorted by its indexed column. If you ask for flowers ORDER BY height, and a height directory exists, she doesn't need to sort — the walk order is the answer. The gate delivers the flowers in sorted order for free.

One more that comes without asking: the PRIMARY KEY column has a directory built the moment you draft the table. Every WHERE id = 5 is fast, always, without you drafting anything.

Until now the gardener has drafted directories with one column at the top of the card — colors, or species, or heights. A composite index is a directory with two columns at the top. Instead of listing plots by color alone, she lists them by color and then height: pink flowers grouped together, then within pink, sorted from shortest to tallest.

Here is what such a card looks like:

Notice the structure: colors are the outer grouping, heights the inner. That order is what the parentheses (color, height) declare. Now run the composite index below and try the three chips — you'll see why this shape helps some queries and not others.

Try the three chips above. The first two get the short walk — the gardener finds her answer in the directory. The third one falls back to a full scan of all thirty plots. Why?

Because a composite index is sorted left to right. The directory is organised by color first; within each color, sorted by height. If you ask about color — even color alone — the gardener can find her section. If you ask about height alone, she cannot: heights are scattered across every color-section of the card. She would have to read every entry to find them.

This is the leftmost prefix rule. A composite index on (color, height) helps queries that filter on color, or on color AND height. It does not help queries that filter on height alone. If you need that too, you draft a second directory — CREATE INDEX ... ON flower_bed(height).

A directory has limits. Some questions, no matter how many cards hang at the gate, still send the gardener walking every plot. Three shapes of question defeat the directory reliably.

Wildcards on the left — LIKE '%pink%' asks "any color that contains the letters p-i-n-k anywhere." The directory is sorted alphabetically; if the pattern doesn't start with a known letter, there's no place on the card to jump to. She'd have to read every entry. LIKE 'p%' (wildcard on the right) is fine — it starts with a fixed letter and the directory can jump straight to the "p" section.

Functions on the column — WHERE UPPER(color) = 'PINK' asks the gardener to first transform every entry, then compare. The directory lists colors as they are, not as they might become after being uppercased. She has no way to jump to a transformed value. Any function around the column — UPPER(), LOWER(), SUBSTR(), CAST() — defeats the directory the same way.

Negations — WHERE color != 'pink' asks "everything except pink." The directory can tell her precisely where the pinks are, but her question is about all the rest — eighteen of thirty flowers in this flower bed. Even a perfect directory saves her only twelve plots of walking. In a larger flower bed the ratio is worse.

The lesson is not that these queries are wrong. It's that when you write them, no directory in the world will speed them up. If a query is slow and you can rewrite it to remove a wildcard, a function, or a negation, that usually helps more than adding another directory.

A directory at the gate looks like a pure win — quicker walks, no downside. This is not true. The card was cheap to draft once; it is not cheap to keep true.

Every time a flower is planted, moved, or pulled, the gardener must go to the gate and update every directory. INSERT means adding a new plot number to the color card, and to the species card, and to any composite card. UPDATE that changes a color means erasing the flower's plot number from one section of the color card and writing it into another. DELETE means erasing every mention across every card. Each directory adds a small tax to every write.

The cards also take room at the gate. In a small garden of thirty flowers this is nothing. In a large one — a table of a million rows indexed on ten columns — the directories together can be larger than the flower bed they describe.

The rule that follows is simple and often broken: draft a directory only when a real query needs it. Don't index a column because you might, one day, want to look it up. Index the columns you actually filter, join, or sort on. Remove the ones you no longer use.

The tax on the previous page is easier to feel than to describe. Below, the gardener plants a new flower. The flower bed itself changes in an instant. But every directory at the gate has to be updated too — you can watch her pen touch each card in turn. Add or remove directories and plant again; the walk to the gate grows and shrinks with them.

Everything Chapter VII taught, laid out on a single card. The gardener consults it when she's forgotten which side of an ON clause the primary key goes on, or whether it was USING INDEX or USING KEY the plan called it.

A directory helps queries that filter, join, or sort on the indexed column (WHERE, JOIN ... ON, ORDER BY). It doesn't help queries that wrap the column in a function, use a leading wildcard, or negate. It doesn't help SELECT * alone. And it costs a small tax on every write.

The flower_bed is still on the table — thirty flowers in four colors, one with an unrecorded height. The engine remembers every directory you've drafted this chapter; if you've dropped them all, you're starting fresh.

Try any statement below. Draft a directory on a column you haven't tried. Run an EXPLAIN before and after to see the plan change. Drop what you no longer need.

In the garden of the Directory at the Gate, the gardener has taken a new tool from her belt. Where before she walked every plot for every question, she now drafts a small wooden card at the gate — a directory — and consults it first. The walks that used to take an afternoon now take a minute. But every card at the gate is a card she must keep up to date, and no card at all is right for a flower bed nobody queries.

After this garden, one more remains in Autumn: it is called Looking at Neighbors. There each flower, standing in its own row, will learn to see how it compares to the flowers around it — the tallest of its color, the next in bloom, its rank among the purples. Beyond Autumn lies Winter, one garden alone, where risky changes are wrapped so they can be undone.

For today, the last directory is nailed to the gate; the pen is set down; the gardener walks back to the house with less to do than the day before.