Databases Reference
In-Depth Information
Now that we've found a good value for our sample data, here's how to create a prefix
index on the column:
mysql>
ALTER TABLE sakila.city_demo ADD KEY (city(7));
Prefix indexes can be a great way to make indexes smaller and faster, but they have
downsides too: MySQL cannot use prefix indexes for
ORDER BY
or
GROUP BY
queries, nor
can it use them as covering indexes.
A common case we've found to benefit from prefix indexes is when long hexadecimal
identifiers are used. We discussed more efficient techniques of storing such identifiers
in the previous chapter, but what if you're using a packaged solution that you can't
modify? We see this frequently with vBulletin and other applications that use MySQL
to store website sessions, keyed on long hex strings. Adding an index on the first eight
characters or so often boosts performance significantly, in a way that's completely
transparent to the application.
Sometimes suffix indexes make sense (e.g., for finding all email ad-
dresses from a certain domain). MySQL does not support reversed in-
dexes natively, but you can store a reversed string and index a prefix of
it. You can maintain the index with triggers; see
“Building your own
hash indexes” on page 154
.
Multicolumn Indexes
Multicolumn indexes are often very poorly understood. Common mistakes are to index
many or all of the columns separately, or to index columns in the wrong order.
We'll discuss column order in the next section. The first mistake, indexing many col-
umns separately, has a distinctive signature in
SHOW CREATE TABLE
:
CREATE TABLE t (
c1 INT,
c2 INT,
c3 INT,
KEY(c1),
KEY(c2),
KEY(c3)
);
This strategy of indexing often results when people give vague but authoritative-
sounding advice such as “create indexes on columns that appear in the
WHERE
clause.”
This advice is very wrong. It will result in one-star indexes at best. These indexes can
be many orders of magnitude slower than truly optimal indexes. Sometimes when you
can't design a three-star index, it's much better to ignore the
WHERE
clause and pay
attention to optimal row order or create a covering index instead.
Individual indexes on lots of columns won't help MySQL improve performance for
most queries. MySQL 5.0 and newer can cope a little with such poorly indexed tables
