A covering index is an index that contains every column a particular query needs, so the database can answer the query from the index alone without reading the underlying table rows. In database indexing, the term describes a relationship between an index and a query: the same index may cover one query and not another.
The access method that results is usually called an index-only scan or index-only access. By skipping the table lookup for each matching row, a covering index can cut the number of page reads dramatically, which makes it one of the most effective tools for speeding up frequent, read-heavy queries.
How a covering index avoids table lookups
In a typical B-tree index, each leaf entry holds the indexed key values plus a pointer to the full row, such as a row identifier or the primary key. A query that filters on the index key but also needs other columns must follow that pointer for each match, an operation known as a key lookup, bookmark lookup or table access by row ID.
These lookups are usually random reads scattered across the table. When a query matches a few rows, the cost is small, but for thousands of rows the lookups can dominate execution time. If the index already contains all referenced columns, in the SELECT list, WHERE, JOIN, GROUP BY and ORDER BY clauses, the lookups disappear entirely.
A covering index can also satisfy sorting and grouping directly, because the index entries are already ordered by their key columns. A query such as SELECT order_date, total FROM orders WHERE customer_id = ? ORDER BY order_date is fully served by an index on (customer_id, order_date) that also stores total.
Building a covering index
There are two ways to make an index cover a query: add the extra columns as additional key columns in a composite index, or attach them as non-key included columns. The second option is cleaner when the extra columns are only read, not searched or sorted.
| Database | Mechanism | Notes |
|---|---|---|
| SQL Server | INCLUDE clause on a nonclustered index | Included columns live only in leaf pages |
| PostgreSQL | INCLUDE clause since version 11 | Index-only scans also depend on the visibility map |
| MySQL with InnoDB | Composite key columns | Secondary indexes always contain the primary key |
| Oracle | Composite key columns | Index fast full scans can read the whole index |
| SQLite | Composite key columns | Planner reports a covering index in its plan output |
Column order still follows the rules of a composite index: columns used for equality filters come first, then columns used for ranges or sorting. Included columns do not affect the ordering and cannot be used for seeking, so they belong at the end or in an INCLUDE list.
Benefits and costs of covering indexes
A covering index is a trade-off between read speed and write and storage cost. Every additional column makes the index larger and must be maintained on each insert, update and delete.
| Factor | Effect | What it depends on |
|---|---|---|
| Read performance | Fewer page reads, no random lookups | Number of matching rows and row width |
| Index size | Grows with every added column | Width of included columns |
| Write overhead | More data to update per change | Update frequency of covered columns |
| Memory pressure | Larger index competes for buffer cache | Available memory and working set |
| Plan stability | Optimizer reliably prefers the index | Accurate statistics |
The benefit grows with the number of rows a query touches. For a lookup of a single row by primary key, a covering index changes little; for a report scanning tens of thousands of index entries, it can reduce reads from tens of thousands of pages to a few hundred.
Covering index compared with related index types
The covering index is a role, not a separate data structure. It is usually a B-tree, and it often overlaps with other index concepts in the same topic.
| Concept | Definition | Relationship |
|---|---|---|
| Composite index | Index on several key columns | Often the basis of a covering index |
| Clustered index | Table rows stored in index order | Covers every query on that table by definition |
| Hash index | Equality lookups through hashing | Rarely covering; usually stores only keys and pointers |
| Query planner | Chooses execution strategies | Decides whether to use an index-only scan |
The query planner has the final say. In PostgreSQL, for example, an index-only scan still visits the table for pages not yet marked all-visible, so a recently updated table may see less benefit until vacuum updates the visibility map. Checking the execution plan is the only reliable way to confirm coverage.
Common mistakes with covering indexes
- Adding SELECT star queries: selecting all columns makes coverage impossible; name only the needed columns.
- Covering every query: dozens of wide indexes inflate storage and slow writes more than they help reads.
- Including frequently updated columns: each update then rewrites index entries as well.
- Assuming coverage without checking: an expression, implicit conversion or extra column in a later code change silently breaks it.
- Duplicating overlapping indexes: an index on (a, b) and another on (a, b, c) can often be merged into one.
A sound practice is to reserve covering indexes for the most frequent or most expensive queries, identified from workload statistics, and to review them whenever those queries change.
In everyday language
A covering index is an index that already holds every piece of information a question needs, so the database never has to open the main table to answer it.
Imagine a library catalog card that lists not only a book’s title and shelf number but also its author and year. If you only want the author and year, you read them straight off the card and never walk to the shelf. That saved trip, repeated thousands of times, is what makes a covering index fast.
Seen in the real world
An e-commerce site shows each signed-in customer a list of recent orders with the date, status and total. The orders table holds about 40 million rows, and the page is opened thousands of times per hour. An existing index on the customer column found the orders quickly, but the database then performed a separate table lookup for every order to read the other three columns.
A database engineer replaced it with an index keyed on customer and order date that includes status and total. The execution plan switched to an index-only scan, and the average query time for customers with long histories dropped from tens of milliseconds to a few milliseconds.
Write performance on the orders table changed only slightly, because status updates are infrequent. The page became faster without any change to application code, and the engineer documented the index alongside the query it was designed to cover.