Covering indexes

What a covering index does

A covering index contains every column one query needs for its search and output. The database can answer that query from the index without looking up the matching table rows. An index covers a specific query, not a table in general.

Compare the two plans

Schema

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  category TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  name TEXT NOT NULL
);
CREATE INDEX idx_products_category ON products(category);

Sample data

INSERT INTO products (id, category, price_cents, name) VALUES
  (1, 'books', 1200, 'Algorithms'),
  (2, 'games', 3500, 'Chess'),
  (3, 'books', 1800, 'Databases');

Before query

SELECT category, price_cents
FROM products
WHERE category = 'books';

The first index can find the matching category, but SQLite must still read price_cents from the table:

Before plan

SEARCH products USING INDEX idx_products_category (category=?)

Add the output column to the index:

Change

CREATE INDEX idx_products_category_price
  ON products(category, price_cents);

After query

SELECT category, price_cents
FROM products
WHERE category = 'books';

The plan can now say:

After plan

SEARCH products USING COVERING INDEX idx_products_category_price (category=?)

The query returns the same columns and rows. The second plan can answer from the index alone.

Covering has a cost

Wider indexes use more storage and make writes do more work. Extra columns can also duplicate another index. Use a covering index for an important read path after a narrower index has already proved that table lookups are a meaningful cost.

Check whether an index covers the query

List every column used to filter, join, order, group, and return data.
Check whether the chosen index contains those columns in a useful order.
Look for USING COVERING INDEX in the SQLite plan.
Confirm the result is unchanged.
Compare realistic timing instead of assuming the plan label is enough.
Measure the added write and storage cost.

Try the same idea

Open the free Query Plan Visualizer and choose Covering index challenge. Run the query with NOT INDEXED, remove those two words, and run it again. The rows stay the same while the plan moves from a scan to a covering index search.

Other databases use different terms

PostgreSQL may use an index-only scan and still need visibility checks. MySQL has its own plan fields. SQLite's USING COVERING INDEX record teaches the core idea, but the exact conditions and costs are engine-specific.