Indexing for High Performance - High Performance MySQL - page 187

Table 5-4. Speed of inserting a million rows with various index strategies

state_id only Both state_id and state_id_2

InnoDB, enough memory for both indexes 80 seconds 136 seconds

MyISAM, enough memory for only one index 72 seconds 470 seconds

As you can see, inserting new rows into the table with more indexes is slower. This is
true in general: adding new indexes might have a performance impact for INSERT ,
UPDATE , and DELETE operations, especially if a new index causes you to hit memory limits.
The solution for redundant and duplicate indexes is simply to drop them, but first you
need to identify them. You can write various complicated queries against the INFORMA
TION_SCHEMA tables, but there are two easier techniques. You can use the views in Shlomi
Noach's common_schema , a set of utility routines and views you can install into your
server ( http://code.google.com/p/common-schema/ ). This is faster and easier than writing
the queries yourself. Or you can use the pt-duplicate-key-checker tool included with
Percona Toolkit, which analyzes table structures and suggests indexes that are duplicate
or redundant. The external tool is probably a better choice for very large servers; queries
against the INFORMATION_SCHEMA tables can cause performance problems when there is
a lot of data or a large number of tables.
Be careful when determining which indexes are candidates for dropping or extending.
Recall that in InnoDB, an index on column (A) in our example table is really equivalent
to an index on (A, ID) because the primary key is appended to secondary index leaf
nodes. If you have a query such as WHERE A = 5 ORDER BY ID , the index will be very
helpful. But if you extend the index to (A, B) , then it really becomes (A, B, ID) and
the query will begin to use a filesort for the ORDER BY portion of the query. It's good to
validate your planned changes carefully with a tool such as pt-upgrade from the Percona
Toolkit.

Unused Indexes
In addition to duplicate and redundant indexes, you might have some indexes that the
server simply doesn't use. These are simply dead weight, and you should consider
dropping them. 16 There are two tools that can help you identify unused indexes. Per-
haps the easiest and most accurate is the INFORMATION_SCHEMA.INDEX_STATISTICS table
in Percona Server and MariaDB. Just enable the userstats server variable (it's disabled
by default) and let the server run for a while, and you'll be able to see how much each
index is used.
Alternatively, you can use the pt-index-usage tool included in Percona Toolkit. This
tool reads a log of queries and executes EXPLAIN with each one. When it completes, it

16. Some indexes function as unique constraints, so even if an index doesn't get used for queries, it might be
used to prevent duplicate values.