Covering Indexes
What you'll learn: How to design indexes that contain all the data a query needs, eliminating the need to access the table itself.
The Core Idea
A covering index (also called a covering composite index) is an index that includes every column referenced in a query—not just the columns in the WHERE clause, but also those in the SELECT list and any ORDER BY or GROUP BY clauses.
When an index covers a query completely, the database can satisfy the entire request by reading only the index, without ever touching the actual table data. This is significantly faster because indexes are typically smaller and more efficiently organized than full table rows.
Why It Matters
Think of a covering index like a restaurant menu that includes not just dish names but also prices, ingredients, and preparation times. If someone asks "What's the price of the Caesar salad?", you can answer directly from the menu without going to the kitchen to check the recipe book.
Without a covering index, the database must:
- Use the index to find matching row identifiers
- Look up each row in the table (called a "table lookup" or "bookmark lookup")
- Retrieve the additional columns
With a covering index, step 2 and 3 disappear entirely.
Example Structure
If you frequently run:
SELECT user_id, email FROM users WHERE last_login > '2024-01-01'
A covering index would be:
CREATE INDEX idx_covering ON users (last_login, user_id, email)
The index includes last_login (for filtering), plus user_id and email (for selection)—everything the query needs.
Key Takeaway: A covering index includes all columns needed by a query, allowing the database to fulfill requests entirely from the index without accessing table data, dramatically improving query performance.