Covering Index
AdvancedA covering index includes all columns needed for a query, eliminating the secondary lookup to the clustered index; MySQL shows "Using index" in EXPLAIN for covered queries.
Overview
A **covering index** is a secondary index that contains all the columns a query needs — the `WHERE` filter columns, the `SELECT` projection columns, and any `ORDER BY`/`GROUP BY` columns. When a query is "covered", MySQL satisfies it entirely from the index B-tree without performing a **row lookup** (secondary index → clustered index row fetch). This eliminates the most expensive part of secondary index access: the random I/O to fetch the actual data row. EXPLAIN shows `Extra: Using index` for covered queries. The trade-off: wider indexes consume more space and slow down writes. Add only the minimal set of extra columns needed.
What Makes a Query Covered
InnoDB stores the primary key in every secondary index leaf node. If a secondary index on `(status, created_at)` is used to answer `SELECT id FROM orders WHERE status='PAID'`, it is automatically covered because `id` (the PK) is already in the leaf. To cover `SELECT id, total FROM orders WHERE status='PAID'`, you need `total` in the index too.
-- Query 1: only needs columns in the index
-- Index: (status, created_at)
SELECT id, status, created_at -- id = PK, already in every secondary index
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC;
-- EXPLAIN → Extra: Using index ✓ (PK included implicitly)
-- Query 2: needs 'total' — not in the index → row lookup required
SELECT id, status, created_at, total -- total NOT in index
FROM orders
WHERE status = 'PAID';
-- EXPLAIN → Extra: (no "Using index") → performs row lookup for each row
-- Fix: create a covering index that includes 'total'
CREATE INDEX idx_orders_covering
ON orders(status, created_at, total);
-- Now: EXPLAIN → Extra: Using index ✓Index-Only Scan vs Row Lookup Cost
A secondary index access normally requires two lookups: (1) traverse the B-tree to find matching index entries, (2) for each entry, fetch the full row from the clustered index (random I/O for each row). For large result sets on an HDD, these random row fetches dominate query time. A covering index eliminates step 2 entirely — the data is read sequentially from the index pages.
-- Without covering index: two-step lookup
-- Step 1: index scan (status='PAID') → returns (id, created_at) pairs
-- Step 2: for each id → random I/O fetch from clustered index → get total, amount, etc.
-- Cost: O(N) random I/O for N matching rows
EXPLAIN SELECT id, total, customer_id
FROM orders WHERE status = 'PAID' AND created_at > '2025-01-01';
-- possible_keys: idx_status_date
-- Extra: NULL ← row lookup happening
-- With covering index (status, created_at, total, customer_id)
EXPLAIN SELECT id, total, customer_id
FROM orders WHERE status = 'PAID' AND created_at > '2025-01-01';
-- Extra: Using index ← no row lookup!
-- Reads: sequential scan of index pages only
-- Verify with EXPLAIN ANALYZE (MySQL 8.0.18+)
EXPLAIN ANALYZE
SELECT id, total FROM orders
WHERE status = 'PAID' AND created_at > '2025-01-01';
-- Shows actual rows read and time — compare with/without covering indexDesigning Covering Indexes — Column Order
For a covering index, the column order still follows the leftmost prefix rule for filtering. The pattern: **(filter columns) + (sort columns) + (SELECT columns)**. Filter columns must come first (equality before range). The extra SELECT columns are appended last — they serve only as payload, not as filter keys. Avoid adding too many columns: each extra byte increases the index size and write overhead.
-- Pattern: (WHERE equality) + (WHERE range / ORDER BY) + (SELECT extras)
-- Query:
SELECT user_id, total, status
FROM orders
WHERE customer_id = ? AND created_at BETWEEN ? AND ?
ORDER BY created_at;
-- Covering index design:
CREATE INDEX idx_orders_cov
ON orders(customer_id, created_at, total, status);
-- customer_id = equality filter (leftmost)
-- created_at = range filter + ORDER BY (no filesort needed)
-- total, status = SELECT projection (just payload)
-- EXPLAIN shows:
-- key: idx_orders_cov
-- Extra: Using index (covered + no filesort)
-- Avoid: adding ALL columns to every index
-- ❌ CREATE INDEX idx_fat ON orders(customer_id, created_at, total, status,
-- address, notes, metadata, ...);
-- → huge index, slow writes, marginal query gain for rare queriesKey Points to Remember
- 1A covering index contains all columns a query needs: WHERE + ORDER BY + SELECT
- 2InnoDB includes the PK in every secondary index leaf — PK is always "free" for covering
- 3"Using index" in EXPLAIN Extra means the query is covered — no row lookup
- 4Row lookup (secondary → clustered index) is O(N) random I/O — covering index eliminates it
- 5Index column order: equality filters → range/sort → SELECT payload columns
- 6Trade-off: wider covering indexes write slower and consume more space — add only what is needed
Interview Questions
Sign in to ask AriaWhat is a covering index and what does "Using index" in EXPLAIN mean?
Why does InnoDB include the primary key in every secondary index?
Design a covering index for: SELECT id, total FROM orders WHERE customer_id=? AND status=?
What is the trade-off between adding more columns to a covering index and query performance?
A query uses a secondary index but EXPLAIN shows no "Using index". What does that mean?
Ask Aria about Covering Index
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.