Covering Index
In one line
A covering index has all columns a query needs — the database answers from the index without reading the table.
In simple words
A covering index includes all the columns a query needs — both the WHERE columns and the SELECT columns. The database can answer the query entirely from the index without fetching the actual table rows. This is called an "index-only scan" and is very fast.
In PostgreSQL, use the INCLUDE clause: CREATE INDEX idx ON orders(customer_id) INCLUDE (total, status) In MySQL/SQL Server, the index automatically includes primary key columns.
When a query uses a covering index:
- No table access needed — just the index
- Index is smaller than the table — more cache-friendly
- Dramatically faster for read-heavy queries
The trade-off: the index is larger (stores extra columns) and slower to update on writes.
Example
-- Without covering index: must read table for each match
CREATE INDEX idx_customer ON orders(customer_id);
SELECT customer_id, total, status FROM orders WHERE customer_id = 42;
-- Plan: Index Scan + Heap Fetch (slow — reads table rows)
-- With covering index (PostgreSQL INCLUDE):
CREATE INDEX idx_customer_covering
ON orders(customer_id) INCLUDE (total, status);
SELECT customer_id, total, status FROM orders WHERE customer_id = 42;
-- Plan: Index Only Scan (fast — no table access!)
-- MySQL: the index includes all needed columns
CREATE INDEX idx_covering ON orders(customer_id, total, status);A covering index has all columns the query needs. The database does an Index-Only Scan — no table access, much faster.
Common confusions
Confused with: composite index
The difference: A composite index covers multiple WHERE/filter columns. A covering index covers all columns a query needs (WHERE + SELECT*SELECT) so the table isn't accessed. A covering index is often composite, but the key difference is that it includes non-filter columns for the SELECT clause.
Related terms
Related Resources
Related Lessons
- Database Indexes
Covering indexes.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.