Database Reference
In-Depth Information
So what to do? Happily, there's a solution to the
long-lost chicken recipe, and its answer lies in the
use of compound indexes.
The two indexes you've created so far are single-
key indexes: they both order just one key from each
recipe. You're going to build yet another index for
The Cookbook Omega, but this time, instead of
using one key per index, you'll use two. Indexes
that use more than one key like this are called
com-
pound indexes
.
This compound index uses both ingredients and
recipe name,
in that order
. You'll notate the index
like this:
ingredient-name
. Part of this index would
look like what you see in figure 7.1
The value of this index for a human is obvious.
You can now search by ingredient and probably find
the recipe you want, even if you only remember the
initial part of the name. For a machine, it's still valu-
able for this use case and will keep the database
from having to scan every recipe name listed for
that ingredient. This compound index would be
especially useful if, as with The Cookbook Omega,
there were several hundred (or thousand) chicken recipes. Can you see why?
One thing to notice: with compound indexes, order matters. Imagine the reverse
compound index on
name-ingredient
. Would this index be interchangeable with the
compound index we just explored?
Definitely not. With the new index, once you have the recipe name, your search is
already limited to a single recipe, a single page in your cookbook. So if this index were
used on a search for the recipe Cashew Marinade and the ingredient Bananas, then
the index could confirm that no such recipe exists. But this use case is the opposite
one: you know the ingredient, but not the recipe name.
The cookbook now has three indexes: one on
recipe
name, one on
ingredient
,
and one on
ingredient-name
. This means that you can safely eliminate the single-key
index on
ingredient
. Why? Because a search on a single ingredient can use the index
on
ingredient-name
. That is, if you know the ingredient, you can traverse this com-
pound index to get a list of all page numbers containing said ingredient. Look again
at the sample entries for this index to see why this is so.
The goal of this section was to present an extended metaphor for readers who
need a better mental model of indexes. From this metaphor, you can derive a few sim-
ple rules of thumb:
Figure 7.1
A compound index
inside a cookbook
