Covering Indexes: Retrieving Data Without Accessing It

The basic use of an index is to retrieve rows as efficiently as possible. Generally speaking, finding a row with an index involves two steps internally: first, you query the index to get pointers to data and then you use the pointers to retrieve data. Indexes hold values, so why not use directly these values ? As indexes are smaller than data and are more likely to fit in memory, you can expect huge improvements from this optimization.

How can you check that the MySQL server only retrieves values from the index ? Is it possible to rewrite a query so that it uses a covering index ? And are you sure that using a covering index will always help get better performance ? To answer these questions we will look at the information of EXPLAIN. Then we will explain some rewriting techniques (for instance with queries with LIKE or dates) and show some benchmarks with good and bad use of covering indexes.

Another important use of covering indexes is sorting. Basically, even if you use an index to sort rows, which can be very fast, you still have to retrieve data from the rows, which can be expensive. Once again covering indexes can help you much.

We will end our discussion by examining the InnoDB storage engine : with its clustered indexes, primary keys play a special role. Keeping this design in mind can help use a covering index that would not be covering with another storage engine.

• Definition and features of an index
• Quick overview of some index algorithms : hash, b-tree, r-tree, t-tree
• Availability of index algorithms in the most used storage engines
• Definition and advantages of a covering index
• Using EXPLAIN to check if you are using a covering index
• Situations where the optimizer cannot use a covering index
• Examples of query rewrites
• Benchmarks with good and bad situations for covering indexes
• How to use a covering index to sort
• A special case : the InnoDB storage engine