SQL / INDEXES AND QUERY PERFORMANCE
Covering indexes and the lookups they avoid
Tell from a query plan whether an index answers a query on its own, and widen a composite index so the per-row table lookup disappears.
What you will learn
- Spot in a plan whether rows are still fetched: COVERING INDEX vs plain INDEX
- Count every referenced column, not just the SELECT list, when checking coverage
- Order a composite index by what positions the search, then append payload columns
- Weigh the saved per-row lookup against a bigger index and slower writes
Understanding Covering indexes and the lookups they avoid
An index entry is not only a key. It is the indexed columns plus whatever the engine needs to find the row again: the rowid in SQLite, the primary key columns in InnoDB, a heap pointer in PostgreSQL. If the query needs a column that is not in the entry, the engine follows that pointer once per matching row, and each visit lands at an unrelated place in the table structure. An index covers a query when no such visit is needed, and that second lookup, not the index search itself, is usually what makes an already-indexed query slow once many rows match.
"What the query needs" means every column the statement references for that table, not just the SELECT list: filter predicates, join conditions, GROUP BY, HAVING and ORDER BY all count. One extra column in the SELECT list is enough to turn an index-only plan back into a million row fetches, and SELECT * guarantees them. Engines say the same thing in different words: SQLite prints USING COVERING INDEX, PostgreSQL prints Index Only Scan, MySQL puts Using index in the Extra column of EXPLAIN.
To build one, think position first, payload second. The columns that get you to the right slice of the index (equality predicates, then the range or sort column) belong at the front because those are what the search uses to seek; the columns you only read can follow in any order. PostgreSQL and SQL Server let you attach the read-only ones with INCLUDE (...), which stores them in leaf entries only and keeps them out of the comparison keys, while MySQL and SQLite make you append them to the key list. The bill is size and write cost: every payload column is copied into the index and maintained by each INSERT and by any UPDATE that touches it.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount INTEGER NOT NULL,
status TEXT NOT NULL
);
INSERT INTO orders (customer_id, amount, status) VALUES
(1, 250, 'paid'),
(1, 120, 'open'),
(2, 400, 'paid'),
(1, 310, 'paid'),
(3, 95, 'open');
CREATE INDEX orders_customer_amount ON orders (customer_id, amount);
-- Both columns this query touches live in the index.
EXPLAIN QUERY PLAN
SELECT amount FROM orders WHERE customer_id = 1;
-- status is not in the index, so every matching row must be fetched.
EXPLAIN QUERY PLAN
SELECT amount, status FROM orders WHERE customer_id = 1;An index covers a query when every column the query touches for that table sits inside the index entry, so the engine never follows the pointer back to the row.
Worked examples
Widening the index until it covers
Appending the payload column to the key list turns the second plan from the main example into an index-only plan.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount INTEGER NOT NULL,
status TEXT NOT NULL
);
INSERT INTO orders (customer_id, amount, status)
VALUES (1, 250, 'paid'), (1, 120, 'open'), (2, 400, 'paid');
CREATE INDEX orders_cas ON orders (customer_id, amount, status);
EXPLAIN QUERY PLAN
SELECT amount, status FROM orders WHERE customer_id = 1 ORDER BY amount;Example explained
Line 1customer_id is first, so the equality predicate positions the search at the start of customer 1's entries.
Line 2amount is second, so entries already arrive in ORDER BY order and no sorting step appears in the plan.
Line 3status is last and is pure payload: it never helps position the search, it only answers the SELECT.
Line 4COVERING appears because all three columns the statement names are inside the entry.
The primary key rides along for free
Selecting the primary key stays covered even though it is not part of the index definition.
CREATE TABLE events (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
kind TEXT NOT NULL
);
INSERT INTO events (user_id, kind)
VALUES (7, 'login'), (7, 'click'), (9, 'login');
CREATE INDEX events_user ON events (user_id);
EXPLAIN QUERY PLAN
SELECT id FROM events WHERE user_id = 7;Example explained
Line 1id is not listed in CREATE INDEX, yet the plan still reports COVERING.
Line 2Each entry of events_user stores the rowid it points at, and id is an alias for that rowid here.
Line 3The pointer that would normally be followed is itself the value the query asked for.
Line 4InnoDB behaves the same way: secondary index entries carry the primary key columns, so selecting them costs nothing extra.
COUNT(*) needs no columns at all
A single-column index covers a filtered count, so wide row bodies are never read.
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
queue_id INTEGER NOT NULL,
body TEXT NOT NULL
);
INSERT INTO tickets (queue_id, body)
VALUES (1, 'disk full'), (1, 'slow login'), (2, 'password reset');
CREATE INDEX tickets_queue ON tickets (queue_id);
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM tickets WHERE queue_id = 1;Example explained
Line 1COUNT(*) names no column, so queue_id alone is the entire column set the query touches.
Line 2The engine walks the queue_id = 1 range of the index and counts entries, which is why the count returns 2 without reading a row.
Line 3body is a wide column and is never touched, which is the whole saving when thousands of entries match.
Line 4Add AND body LIKE '%disk%' and COVERING disappears, because body can only come from the row itself.
Important notes
Older SQLite versions print SEARCH TABLE orders instead of SEARCH orders; only the presence of the COVERING keyword matters here.
PostgreSQL's Index Only Scan can still read the table: it consults the visibility map, and pages not marked all-visible show up as Heap Fetches, a number that drops after VACUUM.
Common mistakes
Checking only the SELECT list: a column used solely in WHERE or ORDER BY is part of the column set too, so the plan keeps its per-row lookup and the timing does not budge.
Writing SELECT * and expecting coverage; any column outside the index, including one added later by ALTER TABLE ADD COLUMN, silently reintroduces one row fetch per matched row.
Putting payload at the front, such as (status, customer_id, amount) for a customer_id filter: the plan says COVERING but scans the whole index instead of seeking a single range.
Try it yourself
Change, predict, then run
In a browser SQLite editor, create posts(id INTEGER PRIMARY KEY, author_id, published_at, title, body) with an index on (author_id, published_at), then compare EXPLAIN QUERY PLAN for SELECT published_at FROM posts WHERE author_id = 3 ORDER BY published_at against the same query with title added. Change the index definition until both plans report COVERING.
Open the SQL workspaceCheck your understanding
Two plans for the same table both use the index on orders(customer_id, amount); one says COVERING INDEX, the other just INDEX. The non-covering query matches 3 rows out of 5,000,000. Why is the speed difference between them tiny here?
- Only three rows match, so the non-covering plan performs at most three extra row lookups
- Both plans read the table equally often; COVERING only affects the order rows come back in
- Equality predicates make the engine skip row lookups regardless of the index contents
- The lookup cost is driven by the table's 5,000,000 rows, so it is the same for both plans
Show answer
Coverage saves exactly one row fetch per matching row, so with three matches there is almost nothing to save and the win only appears when thousands of rows match. Option 4 is tempting because the table is huge, but each fetch is a targeted descent by rowid or primary key for one matched row: total lookups track matched rows, not table rows.