All Columns In The Index
A covering index contains every column a query references, both in its predicates and its output. Because the index leaf nodes already hold the needed data, the engine never has to follow a pointer back to the base table. This is called an index only scan.
Avoiding The Lookup
Normally a non clustered index stores only the key columns plus a pointer to the row. To return other columns the engine performs a table lookup per matching row, scattered random reads that dominate cost. A covering index eliminates them entirely.
Many engines support included columns: extra non key columns stored in the leaf level only. They make an index covering without bloating the search key or affecting its sort order.
Trade Offs
- Covering indexes are wider, so they consume more storage and cache.
- Every included column must be maintained on writes, raising update cost.
- They shine for hot read queries where the lookup cost is the bottleneck.
Key idea
A covering index stores every column a query needs, often via included columns, so the engine answers from the index alone and skips costly table lookups.