Skip to content

Covering index

A covering index is a database optimization technique where an index contains all the data columns required by a specific query^[600-developer-mysql.md]. This allows the database engine to retrieve the results directly from the index structure without accessing the main data table^[600-developer-mysql.md].

How it works

In a standard query execution, the database uses an index to locate rows but must then perform a "lookup" to the data pages to fetch the full row contents^[600-developer-mysql.md]. With a covering index, this extra step is bypassed because the index itself "covers" all fields being queried^[600-developer-mysql.md].

This behavior is linked to the underlying B+ tree structure used for indexes, which typically has a height of 3 and can store millions of records^[600-developer-mysql.md].

Example

The following query illustrates a scenario optimized by a covering index:

SELECT column_1, column_2 FROM demo_table WHERE column_3 = 'value';

In this case, a composite index on (column_3, column_1, column_2) would satisfy the query entirely from the index^[600-developer-mysql.md].

  • [[B+ tree]]
  • [[Database index]]
  • [[Row lookup]]

Sources

^[600-developer-mysql.md]