What a Covering Index Is
A normal index stores the key columns plus a pointer back to the row. To return other columns the engine must fetch the full row, an extra step called a heap lookup or bookmark lookup. A covering index includes every column a query needs, so the engine answers entirely from the index and never touches the table.
How To Build One
- Put the filter columns first so the index can be searched.
- Add the returned columns as extra fields, often using an INCLUDE clause.
- The query is then said to be covered by the index.
Trade Offs
- Reads get faster because there is no second lookup.
- The index grows larger and writes cost more to maintain.
- Use it for hot queries where the lookup cost dominates.
Key idea
A covering index contains every column a query reads, letting the database answer from the index alone and skip the extra lookup into the table.