Index-Only Scan — An Index That Skips the Table

Skip the table lookup — the Index-Only Scan

A regular index lookup finds the target row in the index, then goes back to the base table to read the other columns one row at a time.

This trip back is called a table lookup (the step of pulling a row from the base table via the index).

When the result set is large, those round trips add up and start to cost real time.

If you use an index that contains every column the query references, all the values are right there in the index, and there's no need to go back to the table itself.

This pattern, where the index alone delivers the result, is called an Index-Only Scan (an index that covers every column the query touches is also known as a covering index).

-- Aggregating region with an index that contains only region
-- The referenced column (region) is fully inside the index → no trip back to the base table
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT region, COUNT(*)
FROM perf_sales
GROUP BY region;

Imagine the requirement: "We want the total sales per rep." This query only touches two columns: emp_id and amount. Build an index that contains both, and confirm the plan delivers the result from the index alone, without going back to the base table. Drop and rebuild the index inside this single console execution so the plan is fully self-contained. (Run it correctly to reveal the explanation.)

① Drop the index with DROP INDEX IF EXISTS.

② Create an index that contains both emp_id and amount — the columns the query references.

③ Use EXPLAIN QUERY PLAN on the aggregate query that sums amount per emp_id, and confirm the plan never reads the base table.

Miss even one column and you go back to the base table

An Index-Only Scan only happens when every column the query touches — in SELECT, WHERE, GROUP BY, and so on — is inside the index.

Miss even one column and the database has to go back to the base table to read it, and USING COVERING INDEX disappears from the plan.

For example, with an index on (emp_id, amount), writing SELECT emp_id, SUM(amount), region adds region, which isn't in the index — so the query heads back to the base table to fetch it.

-- With an index on (region, amount),
-- adding product to the referenced columns breaks the Index-Only Scan
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, amount);

-- region, SUM(amount) only → fully covered by the index
EXPLAIN QUERY PLAN
SELECT region, SUM(amount) FROM perf_sales GROUP BY region;

-- Add product → not in the index, so back to the base table
EXPLAIN QUERY PLAN
SELECT region, SUM(amount), MAX(product) FROM perf_sales GROUP BY region;

Using the same index, compare two aggregates: one whose referenced columns fit inside the index, and one that adds a single column that doesn't. Build a composite index on emp_id and amount, then look at both plans side by side.

① Drop the index with DROP INDEX IF EXISTS.

② Create a composite index containing emp_id and amount.

③ Show EXPLAIN QUERY PLAN for the aggregate that sums amount per emp_id (fits inside the index).

④ Then show EXPLAIN QUERY PLAN for the same aggregate plus the maximum of region, and compare it with ③ to see how the plan changes (region isn't in the index).

Keep the Index-Only Scan by including WHERE filter columns too

SELECT isn't the only place columns show up in your query.

WHERE filter columns also count as columns the query touches, so they need to be in the index too — otherwise you still end up going back to the base table.

Fold them all into one index in the order "filter columns → output / aggregate columns", and both the filtering and the value retrieval finish inside the same index.

For example, to filter with WHERE region = 'East' and compute SUM(amount), put the filter column region first and the aggregate column amount second: (region, amount).

The index narrows down to the target rows by region and reads amount from the same index, so no trip back to the base table is needed.

-- Fold a filter column (status) and an aggregate column (amount) into one index
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(status, amount);

EXPLAIN QUERY PLAN
SELECT SUM(amount)
FROM perf_sales
WHERE status = 'pending';

Imagine the requirement: "We want the total sales for a specific region." The query filters on region in WHERE and computes the total of amount. Fold the filter column and the aggregate column into one index, and confirm the plan never goes back to the base table.

① Drop the index with DROP INDEX IF EXISTS.

② Create a composite index with the filter column region first and the aggregate column amount second.

③ Use EXPLAIN QUERY PLAN on the aggregate that filters by region and sums amount, and confirm the plan never reads the base table.