Indexing for High Performance - High Performance MySQL - page 185

Redundant and Duplicate Indexes
MySQL allows you to create multiple indexes on the same column; it does not “notice”
and protect you from your mistake. MySQL has to maintain each duplicate index sep-
arately, and the query optimizer will consider each of them when it optimizes queries.
This can impact performance.
Duplicate indexes are indexes of the same type, created on the same set of columns in
the same order. You should try to avoid creating them, and you should remove them
if you find them.
Sometimes you can create duplicate indexes without knowing it. For example, look at
the following code:
CREATE TABLE test (
ID INT NOT NULL PRIMARY KEY,
A INT NOT NULL,
B INT NOT NULL,
UNIQUE(ID),
INDEX(ID)
) ENGINE=InnoDB;
An inexperienced user might think this identifies the column's role as a primary key,
adds a UNIQUE constraint, and adds an index for queries to use. In fact, MySQL imple-
ments UNIQUE constraints and PRIMARY KEY constraints with indexes, so this actually
creates three indexes on the same column! There is typically no reason to do this, unless
you want to have different types of indexes on the same column to satisfy different
kinds of queries. 14
Redundant indexes are a bit different from duplicated indexes. If there is an index on
(A, B) , another index on (A) would be redundant because it is a prefix of the first index.
That is, the index on (A, B) can also be used as an index on (A) alone. (This type of
redundancy applies only to B-Tree indexes.) However, an index on (B, A) would not
be redundant, and neither would an index on (B) , because B is not a leftmost prefix of
(A, B) . Furthermore, indexes of different types (such as hash or full-text indexes) are
not redundant to B-Tree indexes, no matter what columns they cover.
Redundant indexes usually appear when people add indexes to a table. For example,
someone might add an index on (A, B) instead of extending an existing index on (A)
to cover (A, B) . Another way this could happen is by changing the index to cover (A,
ID) . The ID column is the primary key, so it's already included if you're using InnoDB.
In most cases you don't want redundant indexes, and to avoid them you should extend
existing indexes rather than add new ones. Still, there are times when you'll need re-
dundant indexes for performance reasons. Extending an existing index might make it
much larger and reduce performance for some queries.
14. An index is not necessarily a duplicate if it's a different type of index; there are often good reasons to have
KEY( col ) and FULLTEXT KEY( col ) .