What Is a Covering Index?

A covering index is one that contains every column the query needs. Because the index already holds those values, the engine can answer the query entirely from the index structure and never look up the row in the table.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL,
  note TEXT
);

-- The index holds customer_id and amount: everything the query needs.
CREATE INDEX idx_orders_cust_amount ON orders (customer_id, amount);

EXPLAIN QUERY PLAN
SELECT amount FROM orders WHERE customer_id = 1;

The plan says SEARCH orders USING COVERING INDEX idx_orders_cust_amount (customer_id=?). The word COVERING is the important part — the engine reads the index and stops there.

The saving is a whole random access per matching row. A normal lookup is two steps: find the key in the index, then follow the row pointer to the table and read the row's page. On a table larger than your buffer cache, that second step is a disk read, and a query matching a thousand rows does a thousand of them, in whatever order the index happens to produce. A covering index turns that into a single sequential scan of the narrow index structure.

That is why covering matters most on wide tables, on queries that return many rows, and on tables with poor locality — where the clustered structure is not in the order the query needs.

Two notes. The index must contain the columns the query selects as well as the ones it filters on, so the covering index is a composite index whose key is the filter columns and whose tail is the selected columns. And the cover only holds while the query stays the same — add a column to the SELECT list and the index stops covering, which the plan will tell you immediately.

Two queries, two plans, and the difference between them is one column in the select list.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL
);

CREATE INDEX idx_orders_cust_amount ON orders (customer_id, amount);

-- Covered: the index holds customer_id and amount, so the table is not touched.
EXPLAIN QUERY PLAN
SELECT amount FROM orders WHERE customer_id = 1;

-- Not covered: status is not in the index, so the row must be fetched.
EXPLAIN QUERY PLAN
SELECT status FROM orders WHERE customer_id = 1;

SELECT amount FROM orders WHERE customer_id = 1 ORDER BY amount DESC;

The first plan says SEARCH orders USING COVERING INDEX idx_orders_cust_amount (customer_id=?). The second says SEARCH orders USING INDEX idx_orders_cust_amount (customer_id=?) — the same index, the same seek, but no COVERING, because status is not in the index and the engine has to go to the table for it. The final query returns the matching amounts in descending order.

That single word in the plan is the diagnostic. When it appears, the query reads only index pages. When it does not, there is a table lookup for every matching row, and on a large table that is the expensive part.

The fix, if the query is hot, is to widen the index so it covers the extra column: a composite index on (customer_id, amount, status) would cover both queries. The trade-off is that every index entry is bigger, so the index takes more disk, is slower to scan and costs more to maintain on every insert.

One caution about relying on it. The covering property depends on the exact columns in the query, and it breaks silently when somebody adds a column to the SELECT list. A query that was covered becomes not covered, the plan loses the word, and the query gets slower without anyone changing the index. That is why the plan is worth checking again after a query change, and why the covering index should be created against the queries you actually run rather than a guess about what they might select.

When the query it covers is not hot. A covering index is wide — it holds every column the query needs — and that width costs you three times: disk space, write throughput, and buffer cache pressure.

Disk space is the obvious one. A covering index on a wide table can be several times the size of the table itself, and if the table is already the biggest thing in your database, that is real money. Write throughput is the second: every insert has to write an entry into the wide index, and if the primary key is random, those writes land all over the index file, producing the random I/O problem again. The third is that a wide index evicts other things from the buffer cache, which can slow down queries that were perfectly fine before.

The other way it goes wrong is by over-covering. It is tempting to build an index with every column a query might ever need, and then the index covers every query. That is a bad trade if only one of those queries is frequent: you have paid write and space costs for a covering property that only one query benefits from. The right size is the smallest index that covers the queries that are actually hot.

And then there is the case where the covering index is not covering at all. If the query also needs a column you left out, or if the planner chooses a different index, you have paid the cost of a wide index and got none of the benefit. The way to avoid that is to check the plan after creating it and confirm the word COVERING actually appears.

The balanced position for an interview: create covering indexes for the handful of queries that run constantly and read a lot of rows, keep them as narrow as the query allows, and revisit them whenever the query changes. Everything else gets a plain index and a table lookup.