← Back to glossary

Covering Index

Performance Intermediate

debt(d5/e3/b3/t5)

d5 Detectability Operational debt — how invisible misuse is to your safety net

Closest to 'specialist tool catches' (d5). Detection requires running EXPLAIN/EXPLAIN ANALYZE on queries to verify index-only scans are being used. No automated detection is indicated in the term's detection_hints (automated: no). A profiler or database query analyzer can identify missing covering indexes, but it's not caught by standard linters or compilers.

e3 Effort Remediation debt — work required to fix once spotted

Closest to 'simple parameterised fix' (e3). The quick_fix suggests reviewing documentation and applying to context. Creating or modifying a covering index is typically a single ALTER TABLE/CREATE INDEX statement, but requires analysis of query patterns and testing. May touch multiple migration files if the codebase has several queries that need optimization, but remains a localized database-layer change.

b3 Burden Structural debt — long-term weight of choosing wrong

Closest to 'localised tax' (b3). Covering indexes apply to web and cli contexts per applies_to, affecting database query performance. The choice imposes write overhead on the specific tables involved, but doesn't fundamentally shape the rest of the codebase. It's a performance optimization layer that future maintainers need to understand when modifying queries on those tables, but doesn't create system-wide architectural constraints.

t5 Trap Cognitive debt — how counter-intuitive correct behaviour is

Closest to 'notable trap' (t5). The misconception explicitly states that developers wrongly believe 'any index on a WHERE column speeds up a query sufficiently.' This is a documented gotcha that most developers eventually learn — the need to include SELECT and ORDER BY columns, not just WHERE columns. Common mistakes reinforce this: adding columns 'just in case' or forgetting ORDER BY columns are traps that require learning but aren't catastrophically counterintuitive.

About DEBT scoring → scored by claude-opus-4-5-20251101 · 2026-05-07 · reviewed by human

Also Known As

covering index index-only scan composite covering index

TL;DR

An index that contains all columns referenced by a query, allowing the database to answer it entirely from the index without touching the table.

Explanation

A covering index satisfies a query entirely from index data — the database engine never needs to read the actual table rows (avoiding a 'back to table' lookup). For example, an index on (user_id, created_at, status) covers SELECT status FROM orders WHERE user_id = 1 ORDER BY created_at. MySQL/InnoDB shows 'Using index' in EXPLAIN when a covering index is used. Covering indexes dramatically reduce I/O for read-heavy queries. The trade-off is larger index size and increased write overhead. Use EXPLAIN to identify queries that would benefit, and design composite indexes with selectivity and query patterns in mind.

Diagram

flowchart TD
    QUERY[SELECT id name FROM users<br/>WHERE email = x]
    subgraph Without_Covering_Index
        IDX_LOOKUP[Index lookup on email<br/>finds row ID]
        TABLE_FETCH[Fetch full row from table<br/>extra IO round trip]
        IDX_LOOKUP --> TABLE_FETCH
    end
    subgraph With_Covering_Index
        COV[Index on email id name<br/>all needed columns in index]
        INDEX_ONLY[Index-only scan<br/>no table fetch needed]
        COV --> INDEX_ONLY
    end
style TABLE_FETCH fill:#f85149,color:#fff
style INDEX_ONLY fill:#238636,color:#fff
style COV fill:#1f6feb,color:#fff

Common Misconception

✗ Any index on a WHERE column speeds up a query sufficiently. A covering index includes all columns referenced in SELECT, WHERE, and ORDER BY — eliminating the table lookup entirely. An index that only covers the WHERE clause still requires a second lookup for each matched row.

Why It Matters

A covering index contains all columns needed to satisfy a query — the database engine never touches the table data, making reads dramatically faster at the cost of slightly larger indexes.

Common Mistakes

Adding every column to an index 'just in case' — covering indexes have write overhead; only add what queries actually need.
Not checking EXPLAIN output to verify index-only scans are being used.
Covering indexes that are never used because the query's WHERE clause prevents index selection.
Not including the ORDER BY columns in the index — sorting still hits the table without them.

Code Examples

💡 NoteA covering index eliminates the most expensive part of an index lookup — the heap fetch. Essential for high-frequency read queries.

✗ Vulnerable

-- Query hits table for every row even with index on user_id:
SELECT user_id, email, created_at FROM orders WHERE user_id = 42;
-- Index on (user_id) only — must fetch email, created_at from table

-- Covering index — no table access:
CREATE INDEX idx_orders_covering ON orders (user_id, email, created_at);
-- Now EXPLAIN shows 'Index Only Scan'

✓ Fixed

-- Covering index: all columns the query needs are IN the index
-- No heap lookup required — pure index scan

-- Query: find active users by email, return only name
SELECT name FROM users WHERE email = ? AND active = 1;

-- Covering index includes all columns touched by the query
CREATE INDEX idx_users_covering ON users(email, active, name);
-- Now the DB reads only the index — never touches the table rows

-- EXPLAIN confirms: 'Using index' (MySQL) or 'Index Only Scan' (PostgreSQL)

References

https://use-the-index-luke.com/sql/clustering/index-only-scan-covering-index