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.
Summary of the session:
Stephane has been working in the LAMP environment for over 5 years, first as a developer and then as a technical leader. He now trains DBAs who are new to MySQL how to take the full control over their databases and tries to promote open source sofware at SQLI in Paris.
Comments on this page are now closed.
For information on exhibition and sponsorship opportunities at the conference, contact Sharon Cordesse at firstname.lastname@example.org
Download the MySQL Sponsor/Exhibitor Prospectus
For media-related inquiries, contact Maureen Jennings at email@example.com
To stay abreast of conference news and to receive email notification when registration opens, please sign up for the MySQL Conference newsletter.
View a complete list of MySQL contacts.