Skip to content
QueryPlan

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.

  • 3 minute read
  • Examples run in SQLite
On this page

Compare the two plans

A composite index contains more than one column. It is a covering index for a query only when it contains every column that query needs.

Both plans search an index. Only the second one can answer the query without reading the table.

Schema and sample SQL
Schema and sample data
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);
INSERT INTO products (id, category, price_cents, name) VALUES
  (1, 'books', 1200, 'Algorithms'),
  (2, 'games', 3500, 'Chess'),
  (3, 'books', 1800, 'Databases');
Before
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.

  • SEARCH products USING INDEX idx_products_category (category=?)Finds the matching rows through an index.
Open in the Visualizer
After CREATE INDEX idx_products_category_price
CREATE INDEX idx_products_category_price
  ON products(category, price_cents);

The new index adds the output column, price_cents, after category.

  • SEARCH products USING COVERING INDEX idx_products_category_price (category=?)Finds the rows and reads every needed column from the index.
Open in the Visualizer

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

Covering has a cost

Wider indexes use more storage and make writes do more work. A wider index can also duplicate an index you already have. Use a covering index for an important read after a narrower index has shown that table lookups are a large cost.

Try the same idea

First, open the free Query Plan Visualizer and choose Covering index challenge. Run the query with NOT INDEXED. Then remove those two words and run it again. The rows stay the same, and SQLite uses a covering index search instead of a scan.

Open in the Visualizer

Key points

  • List every column the query uses to filter, join, order, group, and return data.
  • Check whether the chosen index holds 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 trusting the plan label alone.
  • Measure the extra write and storage cost.

Other databases use different terms

PostgreSQL may use an index-only scan but still visit table rows to check whether they are visible to the current transaction. MySQL reports index use differently. SQLite's USING COVERING INDEX record shows that the index supplies the query's columns, but each engine has its own conditions and costs.

Practice this in Module 3: Indexes

Ten exercises on composite and covering indexes. One lesson asks you to cover a customer order summary.

See Module 3

Keep reading

References