Skip to content

Covering Index

1. What is it? (2-3 lines)

A Covering Index is a secondary index that contains all columns requested by a SQL query (in the SELECT, WHERE, JOIN, GROUP BY, and ORDER BY clauses). Because the index itself satisfies the query, the engine reads results directly from the index leaf pages without fetching primary table row pages.

2. Why does it exist?

Secondary index lookups in systems like InnoDB require a two-step process: (1) traverse secondary index to find primary key, (2) traverse clustered index to fetch full row data (Double Lookup / Bookmark Lookup). A Covering Index eliminates Step 2, cutting disk I/O in half and enabling Index Only Scan query execution.

3. Where is it used?

  • High-throughput OLTP read endpoints where specific multi-column queries (SELECT id, status FROM orders WHERE user_id = 42) execute thousands of times per second.
  • PostgreSQL INCLUDE clause indexes (CREATE INDEX idx ON tbl(user_id) INCLUDE (status, total)).
  • MySQL InnoDB multi-column composite secondary indexes.

4. How does it work? (simple flow)

  1. Query Execution: User executes SELECT email, created_at FROM users WHERE tenant_id = 10.
  2. Index Evaluation: Query Optimizer checks index idx_tenant_email_created (tenant_id, email, created_at).
  3. Index Only Scan: Optimizer detects all requested columns (tenant_id, email, created_at) exist inside index leaf pages.
  4. Data Retrieval: Engine reads requested values directly from index leaf pages.
  5. Bypass Heap / Clustered Table: The physical table data pages on disk are NEVER READ, avoiding expensive random disk reads!

5. Advantages

  • Completely eliminates Double Lookups / Heap table fetches (Index Only Scan).
  • Cuts disk I/O reads by 50-90% for hot read queries.
  • Dramatically increases Buffer Pool cache efficiency (index leaf pages are smaller than full table rows).

6. Limitations

  • Increases index leaf page size on disk and RAM if non-search columns are added.
  • Increases write amplification penalty on INSERT, UPDATE, and DELETE queries.
  • PostgreSQL requires Visibility Map checks (VACUUM status) to verify MVCC tuple visibility before completing an Index Only Scan.

7. Common interview questions (10)

  1. What is an Index Only Scan in PostgreSQL or MySQL EXPLAIN query plans?
  2. How does a Covering Index eliminate the InnoDB Bookmark Lookup penalty?
  3. How does PostgreSQL's INCLUDE clause differ from standard composite index keys?
  4. What role does the PostgreSQL Visibility Map play in Index Only Scans under MVCC?
  5. What is the impact of adding 5 extra payload columns into a Covering Index?
  6. Can a Covering Index cover a SELECT * query? Why or why not?
  7. How does a Covering Index optimize pagination (LIMIT / OFFSET) queries?
  8. How does index column order affect Covering Index coverage for queries missing prefix columns?
  9. When should you use a Covering Index instead of caching in Redis?
  10. How does a Covering Index improve CPU L1/L3 cache locality during query execution?

8. Common misconceptions

  • Misconception: A Covering Index is a special index type in DDL. Fact: Any index becomes a "Covering Index" for a specific query if it includes all columns evaluated and returned by that query.
  • Misconception: Covering Indexes speed up INSERT statements. Fact: Covering indexes increase index size and slow down write statements.

10. 30-second cheat sheet

  • Definition: Index contains 100% of columns queried by SELECT, WHERE, ORDER BY.
  • Query Plan: Displays Using index (MySQL) or Index Only Scan (PostgreSQL).
  • Core Benefit: Bypasses physical table disk fetch entirely (Zero Bookmark / Heap lookup).
  • Postgres DDL: CREATE INDEX idx ON orders (user_id) INCLUDE (status, total);.