What does it mean for an index to 'cover' a query, and what work does the database avoid when it can answer a query from the index alone?
answer
- Index holds key + row pointer
- Covering = no per-row table fetch
- Counts every column touched, not just SELECT list
- SELECT * defeats covering
- Narrow + ordered + cache-friendly
basics
~20 sAn index covers a query when every column the query needs — filters, joins, output, sorting — is present in the index. The engine then answers from the index alone and skips fetching the table rows, avoiding one random read per matching row.
solid answer
~60 sAn index **covers** a query when all columns the query touches — in `WHERE`, in `ORDER BY`, in joins, and in the `SELECT` list — exist in that index. The resulting access path is called an **index-only scan**. Normally a secondary index stores only the key columns plus a pointer to the row, so after finding matching entries the engine must go fetch each row from the table to read the remaining columns. That step is one lookup per matching row, and those lookups are scattered — the order the index gives you has nothing to do with where rows physically live. For a query returning thousands of rows, this is usually the dominant cost. Covering removes that step. The index becomes a narrow, ordered, self-sufficient copy of the columns you need, so the scan reads a handful of contiguous pages and returns. The classic tell is a query that got much faster after adding one seemingly irrelevant column to an existing index: that column was the last one forcing the table fetch.
code
sql · 9 lines-- index on (status) alone: seek, then one table fetch per matching row
CREATE INDEX idx_orders_status ON orders (status);
SELECT customer_id, total
FROM orders
WHERE status = 'NEW';
-- covering: every column the query touches lives in the index
CREATE INDEX idx_orders_status_cover ON orders (status, customer_id, total);go deeper
Define covering and name the avoided work — the per-row trip to the table — and note that every column the query mentions must be in the index.
Quantify the win (narrow contiguous index pages versus scattered per-row lookups), and mention that key columns and payload columns are two ways to achieve it.
Add when covering flips the plan choice entirely (a large-fraction predicate that would otherwise be a table scan) and flag the write/space cost and the visibility caveat.
Treat it as a targeted denormalization of hot read paths, budgeted against write amplification, cache footprint and the number of such indexes a write-heavy table can carry.
## The two-step access path A secondary index entry holds the index key plus a row identifier — a physical address (heap/rowid) or the primary key, depending on the engine. So the default plan for `WHERE status = 'NEW'` on an index over `status` is: 1. **Seek and scan the index** for the matching entries. Cheap: contiguous, ordered, narrow. 2. **For each entry, fetch the row** to read the columns the index does not contain. Expensive: one lookup per row, in index order, which is effectively random with respect to table layout. Step 2 is where the cost concentrates. Ten thousand matching rows means up to ten thousand scattered page accesses, and the pages may not be cached. This is also why the optimizer often prefers a full table scan once the predicate matches more than a small fraction of the table — sequential reading of everything beats scattered fetching of many things. ## Covering removes step 2 If all columns the query needs are already in the index, step 2 disappears. The plan reads only index pages. Concretely, for ``` SELECT customer_id, total FROM orders WHERE status = 'NEW' ``` an index on `(status)` needs a fetch per row; an index on `(status, customer_id, total)` does not — the entries already carry `customer_id` and `total`. Note what "all columns the query touches" means: not just the `SELECT` list. Join predicates, `WHERE` filters that are not part of the seek, `ORDER BY` columns, `GROUP BY` columns and aggregate arguments all count. `SELECT *` almost never gets covered on a wide table, which is one concrete reason to select the columns you need. ## Why the win is often large Three compounding effects: - **Fewer page accesses.** Index entries are narrow, so far more of them fit per page than table rows do. Scanning 10,000 entries might touch 40 index pages, while fetching 10,000 rows touches up to 10,000 table pages. - **Better locality.** The entries you want are contiguous in the leaf level; the rows are not. - **Better cache residency.** A narrow index over a few columns may stay entirely in memory while the table cannot. This is also why a covering index can turn an aggregate over a large slice of a table — a `COUNT` or `SUM` — from a table scan into a comparatively small index scan. ## Two families of covering - **Extend the key.** Add the extra columns as trailing key columns. They then also participate in ordering and can be used for further narrowing. - **Payload / non-key columns.** Several engines let you attach columns that are stored only at the leaf level and are not part of the sort key, spelled `INCLUDE (...)`. Cheaper on the tree, but unusable for seeking or ordering. ## The catch An index-only plan is not automatically free of table access in every engine. Deciding whether a row version is visible to the current transaction is metadata that lives with the row, not necessarily in the index, so some engines must consult the table for rows whose visibility cannot be established from index-level information alone. That is a separate, deeper discussion — but it is why a plan can say "index only" and still show table fetches. And covering is never free to maintain: every added column widens every entry, must be written on insert, and must be updated whenever that column changes, even though the query never filtered on it. ## How to answer Define it (all needed columns are in the index), name the avoided work (per-row table lookup, which is random I/O), give the `SELECT *` caution, and mention the two ways to build one. Closing with "and it costs writes and space" shows you know it is a tradeoff rather than a free win.
- Why does SELECT * usually prevent an index-only scan?Covering requires every referenced column to be present in the index, and `SELECT *` references every column in the table. Building an index that wide would duplicate the table, cost as much to write as the table itself, and lose the narrowness that makes index scans cheap. Selecting only the columns you actually need is what makes covering achievable.
- Do columns in ORDER BY and join conditions need to be in the index for it to cover?Yes. Covering is about every column the query touches at any stage, not just the projection list. A column used only in an ORDER BY or a join predicate still has to be read from somewhere, and if it is missing from the index the engine must fetch the row to get it. This is why a query can lose its index-only plan after someone adds a single sort column.
A library catalogue card that lists title, author and shelf number. If you only need the title, the card is enough. If you need the first sentence of the book, you must walk to the shelf for every card — one trip per book.
saying these in an interview costs you the question
- Thinking covering only concerns the SELECT list and ignoring WHERE, JOIN and ORDER BY columns
- Believing a covering index is free because it 'avoids the table'
- Assuming any index that includes the filter column already covers the query
- Confusing a covering index with a unique or clustered index
- Expecting SELECT * to be served by a covering index on a wide table