Covering Indexes: What They Actually Save You, and the MySQL vs. PostgreSQL Gotchas
Title: How to Design Covering Indexes: From the Cost of Lookups to MySQL and PostgreSQL Implementations Author: ENCI Tags: SQL, MySQL, PostgreSQL
A covering index does not save you from the "index lookup" step itself; it saves you from the random read that follows the index lookup, when the database has to go back to the clustered index to fetch the entire row. If you put all the columns the query needs to return into the same index, MySQL's Extra column will change from NULL to Using index, and PostgreSQL's execution plan will change from Index Scan to Index Only Scan. The cost is equally clear: the index gets wider, writes get slower, and the cache is more likely to be squeezed. This article walks through "why lookups are expensive -> three checks -> how to write it in each database -> when not to use it," ending with a runnable SQL script.
Table of Contents
- Conclusion: A covering index saves you from the lookup (The Gain Is the Avoided Lookup)
- Why lookups hurt (Why Lookups Hurt)
- Three checks: filter, sort, return (Three Checks)
- MySQL: How to read the three values in
Extra(Reading EXPLAIN)
- MySQL: How to read the three values in
- Fully runnable SQL (Runnable Script)
- PostgreSQL: Index Only Scan and the visibility map (Index-Only Scan)
- PostgreSQL's
INCLUDEsyntax and limitations (INCLUDE Clause)
- PostgreSQL's
- Design order: work backwards from slow queries (Work Backwards)
- Symptom, cause, fix (Symptom to Fix)
- When not to use a covering index (When to Skip)
1. Conclusion: A covering index saves you from the lookup (The Gain Is the Avoided Lookup)
In InnoDB, the leaf nodes of a secondary index store the indexed columns plus the primary key value, not the whole row. So if a query that uses a secondary index returns columns that are not all in the index, it must take the primary key and go back to the clustered index to fetch the whole row—this step is called a lookup.
A covering index makes this step unnecessary: the filter columns, sort columns, and return columns used by the query all fall into the same index. In that case, the executor can build the result by reading only the index, without touching the table data.
To decide whether something is a covering index, look at only three things: whether the columns in the filter conditions are in the index, whether the columns used for sorting/grouping are in the correct order, and whether the columns in the SELECT return list are in the index. All three must be satisfied for it to be covering; if any one is missing, you're back on the old path.
First, we need to debunk a misconception: a covering index is not a special type of index. MySQL does not have a
CREATE COVERING INDEXsyntax. It is just the fact that "some index happens to cover a particular query." The same index may be covering for query A but not for query B.
2. Why lookups hurt (Why Lookups Hurt)
Use a library as an analogy. A secondary index is like a card catalog: the card contains the book title and the call number, sorted by author. You can quickly flip to those cards. But the card does not contain the full text; you have to take the call number and go to the shelves to pull the book out—the lookup is that "going to the shelves" step. Card catalogs are flipped sequentially; the shelves are visited randomly, and the latter is where the time actually goes.
The data flow looks like this: WHERE user_id = 42 first locates a batch of leaf entries in the secondary index, each carrying a primary key value, e.g., 1001, 1007, 1032; then each primary key must traverse the clustered index's B+ tree again, typically three or four levels deep, to get the full row.
We can do a quick arithmetic estimate to build a sense of scale: fetching 1000 rows means 1000 extra primary key index lookups. If those pages are in the buffer pool, the cost is just memory access; if the table is larger than memory and the data is cold, each access is a random page read. So the statements in the slow query log that show "few scanned rows but are still slow" are most likely dying from random I/O caused by lookups, not from a missing index.
flowchart TD
A[SQL Query] --> B[Secondary Index B+ Tree]
B --> C{Return columns all in index?}
C -->|Yes| D[Read only the index and return directly]
C -->|No| E[Get primary keys from leaf]
E --> F[(Clustered index lookup)]
F --> G[Get full row data]
D --> H[Result set]
G --> H[Result set]
classDef input fill:#DBEAFE,stroke:#3B82F6,stroke-width:2px,color:#111827
classDef process fill:#DCFCE7,stroke:#22C55E,stroke-width:2px,color:#111827
classDef decision fill:#FEF3C7,stroke:#F59E0B,stroke-width:2px,color:#111827
classDef store fill:#CFFAFE,stroke:#06B6D4,stroke-width:2px,color:#111827
classDef output fill:#EDE9FE,stroke:#8B5CF6,stroke-width:2px,color:#111827
class A input
class B store
class C decision
class D process
class E process
class F store
class G process
class H output
3. Three checks: filter, sort, return (Three Checks)
Use an order table as an example: fields are id, user_id, created_at, amount, status, remark. The hottest business query is "get the amount and status of a user's most recent three orders."
| Check item | What to check | Common counterexample |
|---|---|---|
| Filter | Filter columns are in the index and follow the leftmost prefix rule | Index is (created_at, user_id), but filter by user_id |
| Sort | ORDER BY columns follow equality columns and the order matches the index |
Index is (user_id, created_at), but SQL writes ORDER BY amount DESC |
| Return | Every column in the SELECT is in the index (including the implicit primary key) |
Writing SELECT * for convenience, pulling in remark as well |
SELECT * is the number one enemy of covering indexes, for a simple reason: you can't guarantee that future added fields will also be in the index. Today it's covering; tomorrow an operator adds a column updated_by, and the query quietly degrades into a lookup, while the execution plan doesn't complain.
The sort condition is the easiest to overlook. user_id is an equality condition, created_at is a range/sort condition. This combination can follow the index directly without a filesort. But if the query changes to ORDER BY amount, even if all columns are covered, MySQL still has to fetch the results and re-sort them—covering solves only "how much to read," not "how to sort."
4. MySQL: How to read the three values in Extra (Reading EXPLAIN)
The Extra column in EXPLAIN is the only place to judge whether an index is covering or not, but it has several values that are easy to confuse:
Extra value |
Meaning | Is it covering? |
|---|---|---|
Using index |
Reading only the index yields all data | Yes |
Using index condition |
Index condition pushdown (ICP), condition pushed to the storage engine, but still needs a lookup to fetch the full row | No |
Using where |
Server layer filters rows after the lookup | No |
NULL |
No extra info, usually means a lookup has already happened | No |
Using index condition is the value most likely to mislead. It looks like "the index was used," but it actually means "condition pushdown," and the lookups happen just as often. The way to tell is simple: as long as there is no separate Using index in Extra, don't consider it covering.
Two other combinations are worth knowing: Using index; Using filesort means reads are covered but sorting still needs extra work; Using index for group-by is a covering optimization for grouping scenarios, not the same as ordinary covering.
5. Fully runnable SQL (Runnable Script)
The following script can be pasted and run as a whole on MySQL 8.0. The data volume is intentionally small, only six rows: on a small table the optimizer may choose a full table scan, so FORCE INDEX is used to pin the execution path, letting you first clearly see the difference in Extra between "covering" and "not covering." In real projects, remove FORCE INDEX and observe.
-- 1. Create the table: field widths are close to a real order table, remark is intentionally left out of the index
DROP TABLE IF EXISTS orders; -- Repeatable, clear the old table first
CREATE TABLE orders ( -- Order table, InnoDB clustered index
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- Primary key, implicitly carried by secondary indexes
user_id BIGINT UNSIGNED NOT NULL, -- User id, high-frequency filter column
created_at DATETIME NOT NULL, -- Order time, high-frequency sort column
amount DECIMAL(10,2) NOT NULL, -- Amount, high-frequency return column
status TINYINT NOT NULL DEFAULT 0, -- Status, high-frequency return column
remark VARCHAR(200) DEFAULT NULL -- Remark, intentionally not put into the index
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Unified charset to avoid implicit conversion
-- 2. Insert six sample rows covering multiple orders from the same user
INSERT INTO orders (user_id, created_at, amount, status, remark) VALUES
(42, '2026-11-01 10:00:00', 199.00, 1, 'First order'), -- User 42's first
(42, '2026-11-05 14:30:00', 88.50, 1, 'Repeat'), -- User 42's second
(42, '2026-11-09 09:15:00', 320.00, 2, 'Refunding'), -- User 42's third
(42, '2026-11-12 20:40:00', 45.90, 1, 'Add-on'), -- User 42's fourth
(7, '2026-11-03 11:20:00', 120.00, 1, 'New customer'), -- Another user's order
(9, '2026-11-08 16:05:00', 66.60, 3, 'Closed'); -- Third user's order
-- 3. Create two indexes: one narrow, one covering
CREATE INDEX idx_user_created ON orders (user_id, created_at); -- Only covers filter and sort
CREATE INDEX idx_user_cover ON orders (user_id, created_at, amount, status); -- Also covers return columns
-- 4. Covering: returning id / amount / status all in idx_user_cover
EXPLAIN
SELECT id, amount, status FROM orders FORCE INDEX (idx_user_cover) -- id is the primary key, secondary index leaves carry it
WHERE user_id = 42 -- Equality condition hits the leftmost column
ORDER BY created_at DESC -- Sort column follows equality column, no filesort
LIMIT 3; -- Expected Extra shows Using index
-- 5. Not covering: remark is not in the index, so a lookup is needed
EXPLAIN
SELECT id, amount, remark FROM orders FORCE INDEX (idx_user_cover) -- Adding remark breaks it
WHERE user_id = 42 -- Filter still uses the index
ORDER BY created_at DESC -- Sort still uses the index
LIMIT 3; -- Expected Extra no longer shows Using index
-- 6. Cleanup: drop the two indexes after verification to avoid polluting the test database
-- DROP INDEX idx_user_created ON orders;
-- DROP INDEX idx_user_cover ON orders;
For statement 4, you should see Using index in Extra; statement 5 won't show it, possibly only Using where or empty. That difference is whether a lookup happened.
One detail often overlooked: id does not need to be written into the index, because InnoDB secondary index leaves naturally carry the primary key value. But that also means if the primary key is long (for example, UUID as primary key), every secondary index gets fatter—this is the other side of "covering indexes are great."
6. PostgreSQL: Index Only Scan and the visibility map (Index-Only Scan)
The corresponding execution plan node in PostgreSQL is called Index Only Scan, semantically the same as MySQL's Using index: reading only the index can return results. But there's a pitfall here that MySQL doesn't have.
PostgreSQL index entries do not store tuple visibility information. To determine whether a row is visible to the current transaction, you must look at the heap table. To avoid that extra look, PG uses a visibility map, which marks pages as "all rows on this page are visible to all transactions." Only pages marked all-visible can an Index Only Scan truly skip the heap table; pages not marked still require a lookup to confirm visibility.
So to judge whether PG actually saved a lookup, you can't just look at the node name; you must look at Heap Fetches:
-- 1. Create the same covering index, putting amount / status into INCLUDE
CREATE INDEX idx_orders_cover ON orders (user_id, created_at) INCLUDE (amount, status); -- INCLUDE supported in 11+
-- 2. First update statistics so the optimizer knows the data distribution
ANALYZE orders; -- Without ANALYZE the plan may be wrong
-- 3. Critical step: mark heap pages as all-visible, otherwise Index Only Scan still does lookups
VACUUM orders; -- For large tables, coordinate with autovacuum strategy
-- 4. Look at the actual execution cost, focusing on Heap Fetches and Buffers
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, status FROM orders -- id is not in the index, see next section
WHERE user_id = 42 -- Equality condition uses the index
ORDER BY created_at DESC -- Sorting is provided by index order
LIMIT 3; -- Heap Fetches equal to 0 means truly covering
The key is the third line. Right after a bulk import or a large batch update, the visibility map is incomplete. The plan node may say Index Only Scan, but Heap Fetches could equal the number of returned rows—the name looks good, but not a single row was saved. This isn't the optimizer lying; it's that you didn't give it the chance to mark visibility.
7. PostgreSQL's INCLUDE syntax and limitations (INCLUDE Clause)
INCLUDE is syntax introduced in PG 11: columns in the parentheses participate in the index key, while columns in INCLUDE are stored in the leaf as "payload" and do not participate in sorting or lookup.
Remember the division of labor clearly:
| Position | Can be used in WHERE | Can be used in ORDER BY | Can be used in unique constraints | Purpose |
|---|---|---|---|---|
| Index key columns (in parentheses) | Yes | Yes | Yes | Position and sort |
| INCLUDE columns | No | No | No | Only for returning, to avoid lookup |
There is also a structural difference from InnoDB: PG's secondary index does not carry the primary key. In the SQL above, SELECT id—if id is not in the index, even if all other columns are covered, you still need a lookup to get id. To truly cover, you must write INCLUDE (id, amount, status). If you migrate from MySQL, almost everyone trips over this once.
If you don't want to stuff columns into the index, PG has two alternative tools: expression indexes can turn computed results like lower(email) into indexable keys; partial indexes (Partial Index) use WHERE status = 1 to build the index only on hot data, making it smaller and cheaper to cover. They solve different problems from covering indexes, but they are often used together.
8. Design order: work backwards from slow queries (Work Backwards)
The right way to use covering indexes is "slow query first, index second," not stuffing every possible column into the index at table creation time. The reasons are practical:
First, indexes have width. If you put a VARCHAR(200) remark into the index, the number of entries a leaf page can hold drops by an order of magnitude, the same data needs more pages, and the buffer pool hit rate falls. InnoDB's single-column index key limit is 3072 bytes (DYNAMIC row format), so long text fields basically have no place in an index.
Second, writes get amplified. Each additional index means INSERT / UPDATE / DELETE must maintain one more B+ tree, and also write redo and undo. Read-heavy, write-light reporting databases can be aggressive, but write-heavy tables like orders and logs need restraint.
Third, covering indexes create redundant indexes. (user_id, created_at) is the leftmost prefix of (user_id, created_at, amount, status), so the former is completely contained in the latter, and keeping it only adds write cost. The way to detect redundancy is to see whether the leftmost prefix can be fully covered by another index.
My suggestion is to fix the process into three steps: first, from the slow query log, pick statements with "few scanned rows but long duration" (these most resemble lookups); second, use EXPLAIN to confirm that lookups are indeed happening; third, only then decide which columns to add to the tail of the index. After adding, you must look back at write performance—don't trade a read problem for a write problem.
9. Symptom, cause, fix (Symptom to Fix)
| Symptom | Cause | Fix |
|---|---|---|
| Only tens of scanned rows but the query takes hundreds of milliseconds | Every row is doing a lookup, random I/O stacking up | Merge return columns into the index, see if Extra shows Using index |
Added an index, but EXPLAIN still doesn't show Using index |
Return columns contain fields outside the index, often from SELECT * |
Narrow the SELECT list, only take the columns you really need |
Extra shows Using index condition and you think it's covering |
Confusing index pushdown with covering read | Only recognize a standalone Using index; ICP still does lookups |
Still slow after covering, Extra shows Using filesort |
Sort fields don't match the index order | Adjust column order in the index so the sort column follows the equality column |
PG plan is Index Only Scan but Heap Fetches is large |
Visibility map not marked all-visible | Run VACUUM, and check whether autovacuum is keeping up |
| After adding the index, writes slow down and disk grows fast | Index too wide, or redundant with an existing index due to leftmost prefix | Drop redundant indexes; don't put long text columns into indexes |
10. When not to use a covering index (When to Skip)
There are three criteria; if any one is hit, stop.
Don't force covering when there are too many return columns. A wide table with a dozen fields to return—stuffing them all into an index is like copying the table. Reads get faster, but writes and storage hurt. In this case, you should reduce return columns or split large fields into an extension table.
Covering can't save you when the filter condition itself lacks selectivity. If user_id has only dozens of distinct values and each query returns a few percent of the whole table, the number of index pages scanned is already close to a full table scan, and the lookups you save don't make up for the extra index pages. Such queries should go to partitioning, pre-aggregation, or a different storage engine.
Don't cover in advance when writes are heavy and read patterns are unstable. A system whose read pattern changes three times a day—the index that covers query A today becomes a write burden tomorrow. Wait until the pattern stabilizes and the slow query list stabilizes before acting, and the benefit is repeatable.
One sentence to wrap up: a covering index is a trade that swaps "random read" for "a bit more sequential storage." Whether it's worth it depends on your read/write ratio and tolerance for latency, not on how advanced it sounds.
Covering indexes suit high-frequency queries with read-heavy, write-light patterns and fixed return columns; if return columns are wide, filter selectivity is poor, or writes are intensive, forcing one only turns a read problem into a write problem.
This column, "SQL Database Technical Column," goes from basic queries and multi-table joins all the way to indexes, execution plans, and database selection. Every article comes with a runnable SQL snippet, updated weekly. To read it in order, subscribe on the column page.
Top 1 from juejin.cn, machine-translated. The original thread is authoritative.
Even if it's covered, you still need to check the heap access count.