Skip to main content

What is a covering index (covering index)?

SQLMiddle~1 min read
Suggest an edit

A covering index is an index that contains all the data a query needs, so the database can get the result from the index alone, without touching the table.

The principle

Normally an index stores keys plus a reference to the table row. But if a query only uses columns that are already in the index, the DBMS can pull everything straight from the index, with no "lookup" against the table. This makes SELECT much faster.

Example

sql
CREATE INDEX idx_orders_customer_amount ON orders(customer_id, amount);

Now this query

sql
SELECT customer_id, amount FROM orders WHERE customer_id = 5;

runs off the index alone, it already "covers" every field it needs.

Details

  • Speeds up SELECT, since it skips touching the table.

  • Effective for frequently used filters and selections.

  • Some DBMSes (e.g. MySQL) let you explicitly add extra fields to an index:

    sql
    CREATE INDEX idx_orders_customer ON orders(customer_id) INCLUDE(amount);

Here, amount doesn't take part in the sort order, but it's stored in the index to cover queries.

The gist

A covering index means "everything needed is already inside the index", so the query runs faster, without extra reads against the table.

Short Answer

Interview ready
Premium

A concise answer to help you respond confidently on this topic during an interview.

Suggest an edit