SQL Interview Prep · Lesson

Covering Indexes and Index-Only Scans

Including columns so a query never touches the table heap.

Covering Indexes and Index-Only Scans is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Recalling the Heap Fetch

Earlier you learned that a normal B-Tree stores only the indexed columns plus a row pointer, so after the index finds matches the engine still hops to the table to read the other columns. That hop is the heap fetch, and it is the cost a covering index is designed to eliminate.

Interviewers ask about covering indexes to see whether you understand why an index can fully answer a query without touching the table.

What 'Covering' Means

An index covers a query when every column the query needs, in SELECT, WHERE, ORDER BY and GROUP BY, is present in the index itself.

When that holds, the engine reads only the index and never visits the table. PostgreSQL calls this an Index-Only Scan; SQL Server and others call it a covering index. The payoff is fewer page reads and faster queries.

Worked Example: A Covered Query

Suppose a query only needs customer_id and order_date. A composite index on exactly those columns contains everything the query asks for, so it can be answered from the index alone.

CREATE INDEX idx_orders_cust_date
  ON orders (customer_id, order_date);

-- Covered: both selected columns are in the index
SELECT customer_id, order_date
FROM orders
WHERE customer_id = 42;

One Extra Column Breaks Coverage

Add a column the index does not contain and coverage is lost, the engine must fetch the heap to get it.

Here total is not in the index, so even though customer_id drives the seek, every matching row triggers a heap fetch to read total.

-- NOT covered: total is not in the index, forces heap fetches
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;

The INCLUDE Clause

You could add total as a fourth key column, but if you never filter or sort on it, that wastes space in the tree's sort order. The cleaner tool is INCLUDE (supported by PostgreSQL and SQL Server): it stores extra columns in the index leaf only, as payload, not as part of the sort key.

Now the query is covered without bloating the searchable part of the index.

CREATE INDEX idx_orders_cust_date_inc
  ON orders (customer_id, order_date)
  INCLUDE (total);

-- Now covered: total is carried in the leaf
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;

Key Columns vs Included Columns

A precise distinction that impresses interviewers:

Key columns define the sort order and can be used to seek and range-scan. They obey the leftmost-prefix rule.
Included columns are stored only in leaves as extra data; they cannot be searched, but they let the index cover more queries.

Rule of thumb: columns you filter or sort on go in the key; columns you only return go in INCLUDE.

MySQL/InnoDB: the Clustered Twist

Show cross-dialect awareness. InnoDB (MySQL) tables are clustered by the primary key: secondary indexes implicitly carry the primary key columns. So a secondary index automatically covers any query that selects only the indexed columns plus primary-key columns, no INCLUDE clause needed (MySQL has no INCLUDE).

The covering concept is universal; the syntax and free-rider columns differ by engine.

Verifying an Index-Only Scan

Prove coverage with EXPLAIN. In PostgreSQL the plan node reads Index Only Scan instead of Index Scan. Watch for Heap Fetches: 0 in EXPLAIN (ANALYZE), that is the definitive sign no table access happened.

If you expected index-only but see Index Scan with heap fetches, a selected column is missing from the index.

EXPLAIN (ANALYZE)
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;
-- Look for: Index Only Scan ... Heap Fetches: 0

The Postgres Visibility-Map Caveat

A subtle Postgres point worth a bonus mark: an Index-Only Scan can still touch the heap if a page is not marked all-visible in the visibility map. After heavy updates, run VACUUM so the visibility map is current; otherwise Heap Fetches climbs and the 'index-only' benefit shrinks.

-- Keeps the visibility map fresh so index-only scans stay heap-free
VACUUM ANALYZE orders;

When NOT to Build a Wide Covering Index

Covering indexes are not free. Stuffing many columns into INCLUDE makes the index large, consuming cache and slowing writes (every relevant write updates the index). Trade-offs to state aloud:

Great for hot, narrow, high-frequency read queries.
Bad as a dumping ground for every column 'just in case'.

Cover the query that matters, not the whole row.

How to Phrase It in the Interview

A clean summary:

'A covering index contains every column a query touches, so the engine answers it from the index alone, an Index-Only Scan, skipping the heap fetch. I put searched columns in the key and returned-only columns in INCLUDE, verify Heap Fetches is zero with EXPLAIN ANALYZE, and keep the index narrow to protect write speed.'

Quick Check

Reason about coverage and the right place for each column.

Recap: Covering Indexes

Key takeaways:

An index covers a query when it holds every column the query needs, enabling an Index-Only Scan with no heap fetch.
Key columns drive seeks and follow the leftmost-prefix rule; INCLUDE columns are leaf-only payload for coverage.
InnoDB secondary indexes implicitly include the primary key.
Verify with EXPLAIN (ANALYZE) and watch Heap Fetches; in Postgres keep VACUUM current.
Keep covering indexes narrow to protect write performance.

Next: the flip side, when indexes actually hurt.

Frequently asked questions

Is the “Covering Indexes and Index-Only Scans” lesson free?

Yes — the full text of “Covering Indexes and Index-Only Scans” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Covering Indexes and Index-Only Scans”?

Including columns so a query never touches the table heap. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Covering Indexes and Index-Only Scans” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

B-Tree Indexes and How They Help
Composite Index Column Order
Covering Indexes and Index-Only Scans
When Indexes Hurt: Writes and Selectivity

← Back to SQL Interview Prep