Indexing for High Performance - High Performance MySQL - page 178

the index can give you the data you want? An index that contains (or “covers”) all the
data needed to satisfy a query is called a covering index .
Covering indexes can be a very powerful tool and can dramatically improve perfor-
mance. Consider the benefits of reading only the index instead of the data:
• Index entries are usually much smaller than the full row size, so MySQL can access
significantly less data if it reads only the index. This is very important for cached
workloads, where much of the response time comes from copying the data. It is
also helpful for I/O-bound workloads, because the indexes are smaller than the
data and fit in memory better. (This is especially true for MyISAM, which can pack
indexes to make them even smaller.)
• Indexes are sorted by their index values (at least within the page), so I/O-bound
range accesses will need to do less I/O compared to fetching each row from a
random disk location. For some storage engines, such as MyISAM and Percona
XtraDB, you can even OPTIMIZE the table to get fully sorted indexes, which will let
simple range queries use completely sequential index accesses.
• Some storage engines, such as MyISAM, cache only the index in MySQL's memory.
Because the operating system caches the data for MyISAM, accessing it typically
requires a system call. This might cause a huge performance impact, especially for
cached workloads where the system call is the most expensive part of data access.
• Covering indexes are especially helpful for InnoDB tables, because of InnoDB's
clustered indexes. InnoDB's secondary indexes hold the row's primary key values
at their leaf nodes. Thus, a secondary index that covers a query avoids another
index lookup in the primary key.
In all of these scenarios, it is typically much less expensive to satisfy a query from an
index instead of looking up the rows.
A covering index can't be just any kind of index. The index must store the values from
the columns it contains. Hash, spatial, and full-text indexes don't store these values,
so MySQL can use only B-Tree indexes to cover queries. And again, different storage
engines implement covering indexes differently, and not all storage engines support
them (at the time of this writing, the Memory storage engine doesn't).
When you issue a query that is covered by an index (an index-covered query ), you'll see
“Using index” in the Extra column in EXPLAIN . 10 For example, the sakila.inventory
table has a multicolumn index on (store_id, film_id) . MySQL can use this index for
a query that accesses only those two columns, such as the following:
mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
*************************** 1. row ***************************
id: 1
10. It's easy to confuse “Using index” in the Extra column with “index” in the type column. However, they
are completely different. The type column has nothing to do with covering indexes; it shows the query's
access type, or how the query will find rows. The MySQL manual calls this the “join type.”