Course contentsShow
← System Design
Lesson 279 of 1,9198. Database Performance OptimizationFree lesson

Covering Indexes

Creating indexes that contain all columns needed by a query to eliminate table lookups entirely.

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:

  1. Uses the index to find the row location
  2. Goes to the main table to fetch name and email

With a covering index on (user_id, name, email), the database:

  1. Uses the index to find the row
  2. 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.