Covering Index Optimization

Construct indexes that satisfy query requirements without visiting the base table.

In this lesson, you will learn how to design indexes that fulfill query requirements entirely from the index structure, completely bypassing the need to read the base table data.

The Problem: The "Bookmark Lookup" Penalty

In a standard B-Tree index, the leaf nodes contain the index key and a pointer (the "row ID") to the actual record in the base table. When you run a query like SELECT email FROM users WHERE last_name = 'Smith', the database navigates the index to find 'Smith', retrieves the row ID, and then performs a secondary lookup—called a Bookmark Lookup—to fetch the email from the actual table storage. This extra I/O operation is expensive, especially as tables grow in size.

The Solution: The Covering Index

A Covering Index is a composite index that contains all the columns referenced in your query (both in the WHERE clause and the SELECT list). When the database engine sees that every requested column exists within the index tree, it realizes it has enough information to satisfy the query without ever touching the base table.

Consider this table:

CREATE TABLE users (
    id INT PRIMARY KEY,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    email VARCHAR(100)
);

If your workload frequently runs:

SELECT email FROM users WHERE last_name = 'Smith';

A standard index on last_name isn't enough. However, if you create a composite index:

CREATE INDEX idx_lastname_email ON users(last_name, email);

Now, the database performs an Index-Only Scan. It finds the last_name in the B-Tree, and because the email is physically stored right next to it in that same leaf node, it returns the result immediately.

When to Use It

Use covering indexes for high-frequency "hot" queries that only need a subset of columns. Be careful, however; adding too many columns to an index increases the size of the index tree and impacts INSERT/UPDATE performance, as those indexes must also be maintained. Aim for a balance where you cover the most critical, performance-sensitive read operations without bloating every index on the table.

Key Takeaway: A covering index eliminates expensive table lookups by including all requested columns in the index structure, allowing the database to satisfy the query entirely through an index-only scan.