Minimizing data access with covering indexes

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.

Summary of the session:

• Indexing basics : features and goals of indexes
• Index types : hash, b-tree
• Index and data layouts for MyISAM and InnoDB
• Accessing data on disk, on RAM
• Covering indexes : definition, why and when using it
• Situations where you cannot use a covering index
• Case studies