Covering Index

Answering queries without ever visiting the actual table.

The idea

A database Index works like the index at the back of a book. If you want to find users named "Alice", the Index tells you exactly which "page" (disk block) Alice's full row is stored on. But fetching that page from the main table takes an extra disk read. What if the query only asks for SELECT name, email FROM users WHERE name='Alice'? If we attach the email directly into the Index itself, the database can return the answer instantly from the index without ever touching the main table. This is called an Index-Only Scan.

Step 1: A user queries for Alice's email using a standard Index.

How it works (INCLUDE clause)

In modern SQL databases (PostgreSQL, SQL Server), you can create an index that sorts by one column, but secretly "carries" extra columns along for the ride using the INCLUDE keyword.

-- 1. Standard Index (Requires fetching from Table)
CREATE INDEX idx_name ON users(name);

-- 2. Covering Index (Index-Only Scan!)
CREATE INDEX idx_name_covering ON users(name) INCLUDE (email);

-- Now this query is 10x faster:
SELECT email FROM users WHERE name = 'Alice';

Cost

Adding columns to an index makes the index physically larger on disk and in RAM. It also means every time you UPDATE a user's email, the database must write to both the main table and the index, slowing down writes. You should only use covering indexes for extremely frequent, performance-critical read queries.

Watch out for

SELECT * is the enemy: A covering index only works if your query selects exactly the columns included in the index. If a developer lazily writes SELECT * FROM users WHERE name='Alice', the database is forced to fetch the full row from the main table anyway, entirely defeating the purpose of the covering index!