Answering From The Index
A covering index contains all the columns a query reads, so the database can satisfy the query entirely from the index without fetching the underlying row. This avoids the extra row lookup that a nonclustered index normally needs, often called an index only scan.
Two Ways To Cover
- Composite key columns: add the needed columns to the index key. They count toward sorting and seeking.
- Included columns: many databases let you attach extra columns as payload stored only in the leaves, not part of the sort key. They make the index cover without bloating the search path.
Use key columns for filtering and ordering, and included columns purely to carry the rest of the selected fields.
The Tradeoff
Covering speeds reads dramatically but is not free:
- A wider index uses more storage and cache.
- Every covered column must be maintained on writes, so updates to included columns still touch the index.
Cover the hot, narrow queries that benefit most, not every column on every index.
Key idea
A covering index holds every column a query needs, often via included payload columns, so it answers from an index only scan with no table fetch, at the cost of a wider index that is slower to maintain.