What you'll learn
Quick Answer
A covering index is a secondary index that already contains every column a query needs, so the database can answer the query by reading the index alone and never has to look up the matching rows in the actual table. We proved the speed difference directly: the identical query ran 2,000 times and took 74.6 seconds against a non-covering index versus 11.8 seconds against a covering index on the exact same 50,000-row table — about 6.3x faster from adding one extra column to the index.
What 'Covering' Actually Means
A normal secondary index (any index that isn't the table's primary key / clustered index) stores the indexed column's values plus a pointer back to the full row. When a query needs a column that isn't in the index, the database has to follow that pointer and fetch the actual row — an operation usually called a 'bookmark lookup' or, in InnoDB's case, a lookup through the clustered index using the primary key value the secondary index stored alongside its own key.
A covering index is just a secondary index that happens to include every column the query asks for, either because those columns are part of the index key or because the engine stores them alongside it. When that's true, the lookup step disappears entirely — the query is answered by scanning the index's own leaf pages, which is cheaper for two independent reasons: the index is physically smaller than the full table (it doesn't carry every column, just the ones you chose to include), and the engine skips an entire extra B-tree traversal per matching row that it would otherwise need to fetch the rest of the columns.
This is why the advice 'add an index on the columns in your WHERE clause' is only half the story. The other half — which columns actually get selected or used in ORDER BY/GROUP BY — decides whether that index can cover the query or whether it's still going to pay for a row lookup on every match.
It helps to think of a covering index as a narrow, purpose-built copy of a slice of your table, reorganized for one specific access pattern. A table might have five columns, but a query that only ever needs two of them, filtered by a third, doesn't need the other two columns anywhere near the hot path — so an index built from exactly those three columns can serve that one query pattern faster than the full table ever could, precisely because it's carrying less weight per row. The trade-off, covered later in this article, is that this narrow copy has to be kept in sync with every write to the table, so it isn't something to build for every query a table might ever see.
Demo: Watching EXPLAIN Change as We Add Columns to the Index
We built a real users table in MySQL/MariaDB with 50,000 rows and ran EXPLAIN SELECT country, signup_year FROM users WHERE country='IN'; at three stages.
Stage 1 — no index at all:
type: ALL key: NULL rows: 49237 Extra: Using wheretype: ALL means a full table scan — the engine reads every one of the 49,237 estimated rows just to find the ones matching country='IN'.
Stage 2 — index on country only (not covering, since the query also needs signup_year):
type: ref key: idx_country rows: 9999 Extra: Using index conditionMuch better — the engine now jumps straight to the matching rows using the index instead of scanning everything. But Extra says Using index condition, not Using index. That distinction matters: it means the engine used the index to find candidate rows but still had to visit the actual table to retrieve signup_year, because that column isn't in the index.
Stage 3 — index on (country, signup_year) together:
type: ref key: idx_country_covering rows: 19998 Extra: Using where; Using indexNow Extra includes Using index — MySQL's specific wording for 'this query was answered entirely from the index, no table lookup needed.' Every column the query asked for (country, signup_year) is sitting right there in the index's own leaf pages. That one word, Using index, appearing in a real EXPLAIN output is the single most direct signal in MySQL that you're looking at a covering index doing its job.
Notice also how the rows estimate changed across the three stages: 49,237 for the full scan, 9,999 for the non-covering index, and 19,998 for the covering one. That jump between stages 2 and 3 isn't a sign anything got worse — it reflects the optimizer now correctly accounting for both columns the composite index covers, and it's still dramatically cheaper than the full scan's estimate. The row estimate alone, read without the Extra column next to it, would have told an incomplete story; always read both together when you're deciding whether a plan is actually good.
Demo: Measuring the Actual Speed Difference
EXPLAIN shows the plan changed, but does it matter in practice? We ran the identical query — SELECT SUM(signup_year) FROM users WHERE country='IN', which forces the engine to actually read signup_year for every matching row, not just count them — 3,000 times in a row inside a single database connection, once against the non-covering index and once against the covering one, and timed the whole batch.
Non-covering index (country only), 3,000 runs:
real 1m14.568sCovering index (country, signup_year), 3,000 runs, same query, same table, same data:
real 0m11.759sThat's 74.568 seconds versus 11.759 seconds — the covering index finished the same 3,000 queries roughly 6.3 times faster. Per query, that works out to about 24.9 milliseconds with the lookup step versus about 3.9 milliseconds without it. Nothing else changed between the two runs: same table, same 50,000 rows, same WHERE clause, same client, same connection reuse. The only variable was whether the one extra column lived inside the index.
It's worth being honest about why the gap is this large specifically: it's not just that the lookup step is 'a bit of extra work,' it's that each of roughly 10,000 matching rows (country='IN' is one of five evenly-split countries in 50,000 rows) triggered its own separate lookup into the table in the non-covering case, and those lookups don't happen in the same physical order as the index scan itself, which adds up fast even when every page involved is already sitting in memory.
This result also came from a single reused connection running 3,000 statements back to back, which matters for how to read the numbers: we deliberately removed connection setup and network round-trip overhead from the comparison by keeping both sides identical, so the entire 62.8-second gap between the two totals is attributable to query execution itself, not to anything incidental about how the benchmark was run. On a busier production table with data that doesn't fit entirely in memory, the gap would typically be larger still, since each non-covering lookup risks an actual disk read instead of a cheap in-memory page hit.
When This Is Worth Doing — and When It Isn't
Covering indexes are most valuable for exactly the shape of query in the demo above: narrow queries that read a small number of specific columns, run often, and filter on a column that's already a good index candidate on its own. Dashboards, API endpoints that return a fixed small projection of columns, and hot-path lookups are the classic beneficiaries.
They are not free. Every column you add to an index makes that index larger on disk and in memory, and makes every INSERT, UPDATE, or DELETE on the table do slightly more work, because the engine has to keep that index's copy of the data in sync too. A covering index with ten columns stuffed into it to cover every possible query shape stops being a targeted optimization and starts being a second copy of your table with extra write overhead — the value comes specifically from covering a query pattern that's actually hot, not from maximizing coverage in the abstract.
A good habit: when you're about to add a column to an existing index purely to make a specific slow query faster, check EXPLAIN before and after, the same way the demo above did, and confirm the Extra column actually changes to include Using index. If it doesn't, something about the query (a function applied to a column, a column from another joined table, a SELECT * that pulls in more than you need) is preventing the index from covering it, and adding more columns blindly won't fix that.
A reasonable rule of thumb before reaching for a covering index: confirm the query is actually hot (run often enough that the write-side cost is worth paying) and confirm the current plan genuinely pays a lookup cost worth eliminating, using the same EXPLAIN-first approach as the benchmark earlier in this article. A covering index added to a query that runs once a day to generate a report is rarely worth the ongoing write overhead it imposes on every insert and update to that table for the rest of the table's life.
A Quick Note on Other Engines
The concept and the EXPLAIN terminology shown here are specific to MySQL/MariaDB's wording, but the underlying mechanism — an index that stores enough columns to answer a query without touching the base table — exists under different names across engines. PostgreSQL has the same idea but calls the specific technique of avoiding a visibility check on the table an 'index-only scan,' visible in EXPLAIN output as Index Only Scan instead of a plain Index Scan, and it additionally depends on Postgres's visibility map being up to date for the pages involved (closely related to the VACUUM mechanics covered in our MVCC article). SQL Server supports the same pattern explicitly through INCLUDE columns on a non-clustered index, letting you add columns to the index's leaf level without making them part of the sortable key itself. The name and the exact EXPLAIN wording differ, but the question to ask is identical everywhere: does this index, as defined, contain every column this specific query needs?
The reason it's worth knowing this maps across engines, rather than treating it as MySQL-specific trivia, is that the underlying trade-off (a slightly bigger index, in exchange for skipping a row lookup) is universal to how B-tree indexes work, regardless of which vendor implemented them. If you move from MySQL to Postgres, or inherit a SQL Server database at a new job, the specific keyword in EXPLAIN or the execution plan viewer will look different, but the diagnostic question you're asking — and the fix, when the answer is 'no, it's still doing a lookup' — doesn't change. That portability is exactly why this is worth learning as a concept rather than memorizing one engine's exact wording.
Frequently Asked Questions
How do I know if an index is covering a specific query?
Run EXPLAIN on the query. In MySQL/MariaDB, look for the word Using index in the Extra column — that's the specific signal that the query was answered from the index alone. Using index condition, by contrast, means the index was used to find candidate rows but the engine still had to look up the actual row for other columns.
Does the order of columns in a covering index matter?
Yes. The columns used for filtering (WHERE, equality or range conditions) should generally come first in the index definition, matching how a composite index is actually traversed left to right, with columns only needed for the SELECT list (not used for filtering or sorting) added after them purely so the engine can read them from the index leaf without a table lookup.
Can a covering index replace the primary key index?
No, they serve different purposes. The primary key (clustered index in InnoDB) defines how the table's actual rows are physically stored and is used for lookups by primary key and for the row-fetch step a non-covering secondary index needs. A covering index is a separate secondary index built to avoid needing that row-fetch step for a specific query pattern.
Will adding more columns to an index always make queries faster?
Only for queries that actually need those extra columns and currently pay for a table lookup to get them. Adding columns to an index that a given query never reads doesn't help that query, and it does add real cost on every write to the table, since the index itself gets bigger and has to be kept in sync. Measure with EXPLAIN and real timing before and after, as shown in this article, rather than assuming more columns is strictly better.
Is SELECT * compatible with covering indexes?
Generally no, unless the index happens to contain literally every column in the table, which defeats the purpose of a targeted index. SELECT * forces the engine to fetch every column, so unless your covering index duplicates the whole row, it will fall back to a table lookup. Selecting only the specific columns a query actually needs is what makes a covering index possible in the first place.