Covering Indexes: Retrieving Data Without Accessing It

Performance Tuning and Benchmarks
Location: Ballroom F
Average rating: ****.
(4.17, 6 ratings)

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:
  • 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
Photo of Stephane Combaudon

Stephane Combaudon

SQLI

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.

Comments

Rehan Iftikhar
04/21/2009 3:56pm PDT

excellent, best of the day for me so far

Co-presented By:

O'Reilly Media MySQL/Sun Microsystems
  • Kickfire
  • Virident
  • Infobright, Inc
  • JasperSoft
  • Intel
  • Advanced Micro Devices
  • BIRT Exchange by Actuate
  • Calpont
  • Canonical
  • Continuent
  • Dolphin Interconnect Solutions
  • Facebook
  • HiT Software, Inc.
  • IBM
  • iDashboards
  • Oracle
  • Pentaho
  • R1Soft
  • Schooner Information Technology
  • SQLstream
  • Ticketmaster
  • Zmanda, Inc.
  • Linux Journal

Sponsorship Opportunities

For information on exhibition and sponsorship opportunities at the conference, contact Sharon Cordesse at scordesse@oreilly.com

Download the MySQL Sponsor/Exhibitor Prospectus

Media Partner Opportunities

Download the Media & Promotional Partner Brochure (PDF) for information on trade opportunities with O'Reilly conferences or contact mediapartners@ oreilly.com

Press and Media

For media-related inquiries, contact Maureen Jennings at maureen@oreilly.com

MySQL Conference Newsletter

To stay abreast of conference news and to receive email notification when registration opens, please sign up for the MySQL Conference newsletter.

Contact Us

View a complete list of MySQL contacts.