What is a covering index?
A covering index contains every column a query needs, so the database answers from the index alone without visiting the table. This is called an index-only scan and avoids extra lookups.
Explain it simply
If the back-of-book index not only said "Normalization, page 142" but also printed the definition beside it, you would never need to open page 142. A covering index stores the extra columns, so the lookup itself holds the answer.
Example
-- query
SELECT name, city FROM students WHERE city = 'Pune';
-- covering index
CREATE INDEX idx_city_name ON students(city, name);The filter column city and the selected column name are both inside the index, so no table access is needed. EXPLAIN shows "Using index" in MySQL or "Index Only Scan" in PostgreSQL.
If the query also selected email, the database would have to fetch each row from the table, called a lookup or key lookup.
Some databases allow non-key columns in the index:
-- PostgreSQL 11+, SQL Server
CREATE INDEX idx_city ON students(city) INCLUDE (name);The trade-off: wider indexes use more space and slow writes, so cover only your hottest queries.
What to say in the interview
"A covering index holds all columns the query touches, enabling an index-only scan with no table lookups. I build them for frequent read-heavy queries and keep them narrow."
Common mistakes
- Using SELECT * and then wondering why the index does not cover the query.