Databases Reference
In-Depth Information
There's an index on the
actor_id
column, so MySQL will use the index to find rows
whose
actor_id
is
5
. In other words, it performs a lookup on the values in the index
and returns any rows containing the specified value.
An index contains values from one or more columns in a table. If you index more than
one column, the column order is very important, because MySQL can only search
efficiently on a leftmost prefix of the index. Creating an index on two columns is not
the same as creating two separate single-column indexes, as you'll see.
If I Use an ORM, Do I Need to Care?
The short version: yes, you still need to learn about indexing, even if you rely on an
object-relational mapping (ORM) tool.
ORMs produce logically and syntactically correct queries (most of the time), but they
rarely produce index-friendly queries, unless you use them for only the most basic types
of queries, such as primary key lookups. You can't expect your ORM, no matter how
sophisticated, to handle the subtleties and complexities of indexing. Read the rest of
this chapter if you disagree! It's sometimes a hard job for an expert human to puzzle
through all of the possibilities, let alone an ORM.
Types of Indexes
There are many types of indexes, each designed to perform well for different purposes.
Indexes are implemented in the storage engine layer, not the server layer. Thus, they
are not standardized: indexing works slightly differently in each engine, and not all
engines support all types of indexes. Even when multiple engines support the same
index type, they might implement it differently under the hood.
That said, let's look at the index types MySQL currently supports, their benefits, and
their drawbacks.
B-Tree indexes
When people talk about an index without mentioning a type, they're probably referring
to a
B-Tree index
, which typically uses a B-Tree data structure to store its data.
2
Most
of MySQL's storage engines support this index type. The Archive engine is the excep-
tion: it didn't support indexes at all until MySQL 5.1, when it started to allow a single
indexed
AUTO_INCREMENT
column.
We use the term “B-Tree” for these indexes because that's what MySQL uses in
CREATE
TABLE
and other statements. However, storage engines might use different storage
structures internally. For example, the NDB Cluster storage engine uses a T-Tree data
2. Many storage engines actually use a B+Tree index, in which each leaf node contains a link to the next for
fast range traversals through nodes. Refer to computer science literature for a detailed explanation of
B-Tree indexes.
