Skip to main content

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →
A covering index is an index that contains every column a query needs. Understanding the basics of how indexes work first helps — How MySQL Indexes Work covers the B-tree structure, key length, and selectivity rules that apply here too. When MySQL can answer a query entirely from the index — without touching the actual table rows — it’s called an index-only scan. On large tables, this is one of the biggest single-query optimizations available.

​
How Row Lookups Work (and Why They’re Expensive)

A regular index lookup is two steps:
  1. MySQL searches the index to find which rows match
  2. For each matching row, MySQL fetches the full row from the table (a “row lookup” or “back-to-table lookup”)
Step 2 is the expensive part. InnoDB’s clustered index means the table data is physically sorted by primary key. When you look up rows by a secondary index, the primary key values are scattered — each row lookup is likely a random I/O on a different disk page. At scale, those random reads dominate query time. A covering index eliminates step 2 entirely.

​
Identifying a Covering Index in EXPLAIN

EXPLAIN tells you when a covering index is in use: look for Extra: Using index. If you’re not yet familiar with EXPLAIN output, Reading EXPLAIN in MySQL covers all the key columns. 
CREATE TABLE orders (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    user_id    INT NOT NULL,
    status     VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL
);

CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at);

-- All three columns in the SELECT are in the index
EXPLAIN SELECT user_id, status, created_at FROM orders WHERE user_id = 42;

+-------+---------------------------+------+-------------+
| type  | key                       | rows | Extra       |
+-------+---------------------------+------+-------------+
| ref   | idx_user_status_created   |   12 | Using index |
+-------+---------------------------+------+-------------+
Using index — MySQL read only the index. No row lookups. Now add a column that’s not in the index: 
-- order_total is not in the index
EXPLAIN SELECT user_id, status, created_at, order_total FROM orders WHERE user_id = 42;

+-------+---------------------------+------+-------+
| type  | key                       | rows | Extra |
+-------+---------------------------+------+-------+
| ref   | idx_user_status_created   |   12 | NULL  |
+-------+---------------------------+------+-------+
Extra is now empty — MySQL uses the index to find rows, then fetches the full row to get order_total. The row lookup is back.

​
Designing a Covering Index

The goal is to include every column the query touches — in the WHERE, ORDER BY, GROUP BY, and SELECT list. Column order still follows the leftmost prefix rule for WHERE and ORDER BY filtering: 
-- Query pattern:
SELECT status, created_at FROM orders
WHERE user_id = 42
ORDER BY created_at;

-- Covering index: equality column first, then sort column, then SELECT columns
CREATE INDEX idx_covering ON orders (user_id, created_at, status);
  • user_id first — the equality filter
  • created_at second — the sort column (index already ordered, no filesort)
  • status last — only in the SELECT, not in filtering or sorting
The SELECT columns come last because they don’t affect which rows are found or in what order — they’re just along for the ride.

​
When Covering Indexes Are Worth It

ScenarioWorth it?
High-traffic query on a large tableYes
Narrow SELECT list (a few columns)Yes
Read-heavy workloadYes
Small tableNo — row lookups are fast anyway
SELECT * queryNo — you can’t cover all columns without indexing the whole table
Write-heavy tableEvaluate — every INSERT/UPDATE/DELETE updates the index
-- Not a candidate: SELECT * means no index can cover this
SELECT * FROM orders WHERE user_id = 42;

-- Good candidate: narrow SELECT, high-traffic query
SELECT order_id, status FROM orders WHERE user_id = 42 AND created_at > '2024-01-01';

​
Frequently Asked Questions

​
Does a covering index help with SELECT *?

No. An index can only cover a query if it contains every column in the SELECT list. SELECT * selects all columns, so you’d need to index all columns — at that point you’ve essentially duplicated the table as an index, which isn’t useful.

​
Can a covering index still speed up SELECT * queries?

Yes, partially. Even if MySQL has to do row lookups, the index narrows down which rows to fetch. The improvement comes from the WHERE filtering, not from eliminating row lookups.

​
How do I know if a covering index would help?

Run EXPLAIN and check Extra. If you see Using index condition or nothing (no Using index), and rows is large, a covering index might help. EXPLAIN ANALYZE shows actual vs. estimated row counts, which is useful for confirming the optimizer’s choices.

​
Troubleshooting

ProblemSolution
Extra shows nothing instead of Using indexA column in the SELECT is not in the index — add it, or remove it from the query
Extra: Using index condition instead of Using indexThe index is used for filtering but not fully covering — add the remaining SELECT columns
Index exists but key is NULLRun ANALYZE TABLE to refresh statistics; check that the query’s WHERE uses the leftmost index column
Covering index slowing down writesExpected trade-off — evaluate whether the read benefit outweighs the write cost for your workload