Databases Reference
In-Depth Information
Notice that the index sorts the values according to the order of the columns given in
the index in the
CREATE TABLE
statement. Look at the last two entries: there are two
people with the same name but different birth dates, and they're sorted by birth date.
B-Tree indexes work well for lookups by the full
key value, a key range, or a key prefix. They are useful only if the lookup uses a leftmost
prefix of the index.
3
The index we showed in the previous section will be useful for the
following kinds of queries:
Match the full value
A match on the full key value specifies values for all columns in the index. For
example, this index can help you find a person named Cuba Allen who was born
on 1960-01-01.
Match a leftmost prefix
This index can help you find all people with the last name Allen. This uses only
the first column in the index.
Match a column prefix
You can match on the first part of a column's value. This index can help you find
all people whose last names begin with J. This uses only the first column in the
index.
Match a range of values
This index can help you find people whose last names are between Allen and Bar-
rymore. This also uses only the first column.
Match one part exactly and match a range on another part
This index can help you find everyone whose last name is Allen and whose first
name starts with the letter K (Kim, Karl, etc.). This is an exact match on
last_
name
and a range query on
first_name
.
Index-only queries
B-Tree indexes can normally support index-only queries, which are queries that
access only the index, not the row storage. We discuss this optimization in
“Cov-
ering Indexes” on page 177
.
Types of queries that can use a B-Tree index.
Because the tree's nodes are sorted, they can be used for both lookups (finding values)
and
ORDER BY
queries (finding values in sorted order). In general, if a B-Tree can help
you find a row in a particular way, it can help you sort rows by the same criteria. So,
our index will be helpful for
ORDER BY
clauses that match all the types of lookups we
just listed.
Here are some limitations of B-Tree indexes:
3. This is MySQL-specific, and even version-specific. Some other databases can use nonleading index parts,
though it's usually more efficient to use a complete prefix. MySQL might offer this option in the future;
we show workarounds later in the chapter.
