Skip to content

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:

  1. No table access needed — just the index
  2. Index is smaller than the table — more cache-friendly
  3. Dramatically faster for read-heavy queries

The trade-off: the index is larger (stores extra columns) and slower to update on writes.

Example

sql
-- 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

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to glossary