What is a covering index?

WHAT THIS TESTS The interviewer wants to see that you understand how a query touches both the index and the table, and how to eliminate the second access. This is a core optimization for read-heavy paths.

A GOOD ANSWER COVERS Normally an index lets the engine find matching rows, then it follows pointers back to the table heap to fetch the remaining columns the query projects; those heap lookups are random I/O and dominate cost. A covering index contains every column the query needs in the index itself, so the engine performs an index-only scan and never touches the heap. You design one by including the filter and join columns as leading keys, any sort columns next, and the projected columns either as trailing keys or, in engines that support it, as non-key INCLUDE payload columns so they ride along without bloating the search key. Visibility metadata can still force occasional heap visits in MVCC systems, but a well-maintained covering index avoids the bulk of them.

COMMON WRONG ANSWERS Indexing only the WHERE column and assuming the query is covered when the SELECT list still needs other columns. Believing a covering index must put every column in the key, ignoring INCLUDE. Thinking covering eliminates all table access in every engine regardless of visibility checks.

LIKELY FOLLOW-UPS What is the downside of a wide covering index; how does column order affect usability; does INCLUDE help sorting; when does the index get too large to be worthwhile.

ONE CONCRETE EXAMPLE For the query SELECT email FROM users WHERE org_id = 42 ORDER BY created_at, a covering index is CREATE INDEX idx_users_org_cover ON users (org_id, created_at) INCLUDE (email). The engine seeks org_id, reads entries already in created_at order, and reads email from the index payload, returning results with no heap access at all.