Covering Index

Also Known As

TL;DR

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

Why It Matters

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

-- 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'

-- 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

Tags

Related Terms