Detailed explanation
A covering index is an index that contains all the column values needed to execute a particular query, including those used in SELECT, WHERE, JOIN, and any grouping or ordering expressions. Coverage is a relationship between an index and a query, not a separate universal index type: the same index can cover one query but not another. Its main benefit is avoiding additional lookups into the base table to retrieve missing columns, potentially reducing I/O and improving read performance.
For example, CREATE INDEX idx_cover ON orders(customer_id, order_date, total); covers SELECT order_date, total FROM orders WHERE customer_id = 7;. In a typical B-tree index, leaf entries hold the indexed values plus a row locator. Because customer_id is the leading key, the engine can locate the relevant index range efficiently and obtain order_date and total directly from those entries. If the query also selects shipping_address, this index no longer covers it unless that value is otherwise available in the index.
Coverage does not guarantee that the optimizer will choose an index-only plan. It compares estimated costs using factors such as selectivity, table size, statistics, and index width. Database storage rules also matter: PostgreSQL can perform an index-only scan, but may still fetch heap pages to verify row visibility when visibility-map information is insufficient. In MySQL InnoDB, secondary indexes include the primary-key columns, which can help satisfy coverage without explicitly adding those columns. Check the actual execution plan rather than assuming that a suitable index eliminates every table access.
Some databases, including PostgreSQL and SQL Server, support INCLUDE columns, allowing output-only values to be stored at the leaf level without making them search keys. An alternative design is CREATE INDEX idx_cover ON orders(customer_id) INCLUDE (order_date, total); where supported. Key columns determine navigation and ordering; included columns primarily supply values. Wider indexes consume more storage and cache space and increase write maintenance. Interviewers expect you to explain both the avoided table lookup and this trade-off, while distinguishing coverage from efficient filtering.
Key points to remember
- An index covers a query only when it supplies every required column value.
- Coverage can eliminate base-table lookups for missing column values.
- Leading key columns determine efficient navigation; coverage alone does not.
- INCLUDE columns can supply output values without becoming search keys.
- Verify the execution plan and weigh read benefits against storage and write costs.
Common mistakes candidates make
- Calling an index covering without identifying the query it covers.
- Assuming coverage guarantees an index-only plan with zero table access in every database.
- Adding every column to an index without considering key order, index width, or write overhead.
Likely follow-up questions
How is a covering index different from a composite index?
A composite index has multiple key columns, while a covering index supplies everything a particular query needs. A composite index may not cover a query, and a single-column index may cover a simple query.
Why use INCLUDE instead of adding all columns as index keys?
INCLUDE stores additional values for retrieval without making them part of the search key. Use key columns for filtering or ordering requirements and included columns for values needed only in the output, subject to database-specific behaviour.
Can a covering index still require a full index scan?
Yes, coverage means the required values are available, not that the engine can find matching rows through an efficient seek. Without a usable search predicate, it may scan the index and still avoid fetching missing values from the table.
Interview tip: Define coverage relative to a query, walk through the orders example, and finish with one execution-plan caveat and one maintenance trade-off.