Indexing for High Performance - High Performance MySQL - page 186

For example, if you have an index on an integer column and you extend it with a long
VARCHAR column, it might become significantly slower. This is especially true if your
queries use the index as a covering index, or if it's a MyISAM table and you perform a
lot of range scans on it (because of MyISAM's prefix compression).
Consider the userinfo table, which we described previously in “Inserting rows in pri-
mary key order with InnoDB” on page 173 . This table contains 1,000,000 rows, and
for each state_id there are about 20,000 records. There is an index on state_id , which
is useful for the following query. We refer to this query as Q1:
mysql> SELECT count(*) FROM userinfo WHERE state_id=5;
A simple benchmark shows an execution rate of almost 115 queries per second (QPS)
for this query. We also have a related query that retrieves several columns instead of
just counting rows. This is Q2:
mysql> SELECT state_id, city, address FROM userinfo WHERE state_id=5;
For this query, the result is less than 10 QPS. 15 The simple solution to improve its
performance is to extend the index to (state_id, city, address) , so the index will
cover the query:
mysql> ALTER TABLE userinfo DROP KEY state_id,
-> ADD KEY state_id_2 (state_id, city, address);
After extending the index, Q2 runs faster, but Q1 runs more slowly. If we really care
about making both queries fast, we should leave both indexes, even though the single-
column index is redundant. Table 5-3 shows detailed results for both queries and in-
dexing strategies, with MyISAM and InnoDB storage engines. Note that InnoDB's
performance doesn't degrade as much for Q1 with only the state_id_2 index, because
InnoDB doesn't use key compression.

Table 5-3. Benchmark results in QPS for SELECT queries with various index strategies

state_id only state_id_2 only Both state_id and state_id_2

MyISAM, Q1 114.96 25.40 112.19

MyISAM, Q2 9.97 16.34 16.37

InnoDB, Q1 108.55 100.33 107.97

InnoDB, Q2 12.12 28.04 28.06

The drawback of having two indexes is the maintenance cost. Table 5-4 shows how
long it takes to insert a million rows into the table.

15. We've used an in-memory example here. When the table is bigger and the workload becomes I/O-bound,
the difference between the numbers will be much larger. It's not uncommon for COUNT() queries to become
100 or more times faster with a covering index.