Course contentsShow
← Data Science
Lesson 1082 of 2,14527. Database Design and OptimizationFree lesson

Covering Indexes and Index-Only Scans

Learn how indexes that include all queried columns eliminate table lookups, dramatically improving performance.

Covering Indexes and Index-Only Scans

You'll learn how creating indexes that include all columns needed by a query can eliminate the need to look up data in the main table, dramatically speeding up reads.


What Is a Covering Index?

A covering index is an index that contains all the columns your query needs—both those used in the WHERE clause and those in the SELECT list. When an index "covers" your query, the database can satisfy the entire request by scanning just the index, without ever touching the actual table data.

Think of it like a cheat sheet that has both the questions and the answers. Instead of finding the page number in an index and then flipping to that page, you get everything you need directly from the index itself.


How Index-Only Scans Work

Normally, when you use an index, the database:

  1. Scans the index to find matching rows
  2. Looks up those rows in the table to retrieve the requested columns

With a covering index, step 2 is eliminated entirely. The query planner realizes all needed data exists in the index and performs an index-only scan—reading exclusively from the index structure.


When to Use Covering Indexes

  • Queries that request only a small subset of columns
  • Frequently-run queries where read performance is critical
  • Scenarios where the extra storage cost (indexes take space) is justified

You might create a composite index on (customer_id, order_date, total_amount) if you frequently query order totals for specific customers—the index serves both filtering and retrieval.


Key Takeaway: A covering index includes every column a query needs, enabling index-only scans that bypass table lookups and can drastically reduce query execution time.