Covering Indexes
What you'll learn: How to create indexes that answer queries entirely without touching the actual data table.
The Core Idea
A covering index is an index that contains all the columns needed to satisfy a query. When your query can be answered using only the index itself, the database doesn't need to perform an additional lookup to the main table (the "heap"). This eliminates expensive disk I/O and dramatically speeds up query performance.
Think of it like a cheat sheet for a specific test question. Instead of opening your textbook (the main table) to find information scattered across multiple pages, you have a single index card (the covering index) with everything you need to answer that question.
How It Works
Normally, an index stores indexed columns plus a pointer to the full row. When you query:
SELECT name, email FROM users WHERE user_id = 123;
If you only have an index on user_id, the database:
- Uses the index to find the row location
- Goes to the main table to fetch
nameandemail
With a covering index on (user_id, name, email), the database:
- Uses the index to find the row
- Done! All needed data is already in the index
When to Use Covering Indexes
Create covering indexes for frequently-run queries where:
- You always select the same subset of columns
- Read performance is critical
- The extra storage cost is acceptable (covering indexes are larger)
Trade-offs
- Pro: Eliminates table lookups, faster reads
- Con: Larger index size, slower writes (more data to maintain)
Key Takeaway: Covering indexes eliminate table lookups by storing all queried columns in the index itself, trading storage space for read performance.