Covering Index Strategy: Forcing Index-Only Scans on Purpose

1. What a covering index really is

A covering index is an index that contains every column a specific query needs, whether in the WHERE clause, the SELECT list, or ORDER BY. When an index covers all these columns, the MySQL server no longer has to jump to the actual table after finding the matching index entries to read additional column values. This technique is called an index-only scan, because the entire result is answered directly from the index structure.

The difference from a regular index is purely conceptual, not a syntax feature. There is no special CREATE INDEX statement for a covering index, it is simply a composite index whose column selection was deliberately chosen to cover the whole query. This exact deliberate choice of columns separates a random index definition from a targeted covering index strategy.

The effect is especially noticeable with InnoDB, because InnoDB organizes tables as a clustered index around the primary key. Accessing a row via a secondary index there always means an additional lookup in the primary key tree, unless all needed columns are already present in the secondary index itself. A covering index eliminates exactly this second step.

2. Why InnoDB makes table access expensive

InnoDB does not store table data in a separate heap structure but directly inside the B-tree of the primary key, the so called clustered index. Every secondary index therefore stores not the full row but only the indexed columns plus the primary key value. If a row is looked up via a secondary index whose additional columns are not contained in the index, InnoDB has to perform a second tree access in the clustered index with the found primary key value to read the remaining columns. This second access is called a bookmark lookup or table lookup.

Sequentially reading the secondary index but randomly accessing the corresponding rows in the clustered index produces random I/O, which is especially expensive on classic hard disks and remains noticeable even on SSDs due to cache misses. With a thousand matching index entries, that means, in the worst case, a thousand extra random accesses to the buffer pool or the disk. A covering index reduces that to zero extra accesses, because all needed data already sits in the sequentially read secondary index.

-- InnoDB stores the full row inside the clustered index (PRIMARY KEY id)
-- A secondary index only stores its own columns plus the primary key value
SHOW CREATE TABLE orders\G
-- PRIMARY KEY (id)              -- clustered index, holds the full row
-- KEY idx_status (status)       -- secondary index, holds status + id only

-- Lookup via secondary index without covering columns:
-- step 1: find id via idx_status, step 2: bookmark lookup into PRIMARY
EXPLAIN SELECT total_amount FROM orders WHERE status = 2\G
-- key: idx_status, Extra: NULL -- total_amount forces a second tree access

3. Recognizing Using index in EXPLAIN

The unambiguous signal for a successful index-only scan in MySQL is the value Using index in the Extra column of EXPLAIN, without an additional Using where with a separate table lookup. The distinction matters: Using index condition means a filter condition is already checked inside the index, but a table lookup for further columns still happens afterward. Only the plain Using index without this addition confirms the entire access was served from the index.

In practice it pays off to test with a concrete query and a deliberately designed composite index: append the SELECT columns to the index as extra, non-filtering trailing columns and then check again with EXPLAIN whether Using index appears. If even a single needed column is missing from the index, the optimizer falls back to the classic table lookup, and the covering index advantage is completely lost.

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  status TINYINT NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- Non-covering index: id and total_amount must be looked up in the table
CREATE INDEX idx_customer_status ON orders (customer_id, status);

EXPLAIN SELECT id, total_amount FROM orders
WHERE customer_id = 4821 AND status = 2\G
-- Extra: Using index condition -- table lookup still required

-- Covering index: total_amount added as a trailing, non-filtering column
CREATE INDEX idx_customer_status_covering
  ON orders (customer_id, status, total_amount);

EXPLAIN SELECT id, total_amount FROM orders
WHERE customer_id = 4821 AND status = 2\G
-- Extra: Using index -- pure index-only scan, no table lookup

4. Designing a covering index on purpose

Designing a covering index initially follows the same rules as any composite index: equality columns first, then the single range or sort column, ordered by the leftmost prefix rule. In addition, you append at the end all other columns needed in the SELECT list of the query but neither filtered nor sorted on. These appended columns do not contribute to access speed on the index itself, they exist solely to prevent the downstream table lookup.

The order of the appended, non-filtering columns is, unlike the filtering columns, mostly irrelevant, because they are carried in the B-tree only as extra, unsorted-relevant payload. All that matters is that every column used in the SELECT appears somewhere in the index, either as a filtering column at the start or as an appended column at the end.

A common application is pagination of large result lists: a query that only needs id and created_at for an overview page benefits enormously from a covering index over exactly these two columns plus the filter column, while the full row data is only loaded afterward for the twenty displayed results in a second, targeted query over the ids.

-- Covering index for a paginated customer order overview
CREATE INDEX idx_customer_covering
  ON orders (customer_id, status, created_at, id);

-- Step 1: index-only scan delivers just the ids for the current page
EXPLAIN SELECT id, created_at FROM orders
WHERE customer_id = 4821 AND status = 2
ORDER BY created_at DESC LIMIT 20\G
-- Extra: Using index -- no table lookup for the listing itself

-- Step 2: full row data loaded only for the 20 displayed ids
SELECT id, total_amount, customer_id, status, created_at
FROM orders WHERE id IN (9001, 9002, 9003 /* ... 20 ids */);

5. The primary key as a hidden component

An often overlooked aspect: with InnoDB, every secondary index implicitly contains the primary key value, even when it is not explicitly listed in the index definition. That means a query needing only the primary key column itself is automatically covered by any secondary index that contains the filter columns, without appending any extra columns at all. This circumstance makes many covering index designs simpler than initially expected.

With composite primary keys, as commonly found in link tables, this implicit component grows larger, because all columns of the primary key are carried along. That can noticeably enlarge secondary indexes and should be considered when choosing between a compact surrogate key and a composite primary key, especially for tables with many secondary indexes.

-- customer_id is filtered, id is the implicit primary key payload:
-- no extra column needs to be appended to cover a plain id lookup
CREATE INDEX idx_customer_id_only ON orders (customer_id);

EXPLAIN SELECT id FROM orders WHERE customer_id = 4821\G
-- Extra: Using index -- id is already carried by every secondary index

-- Composite primary key in a link table carries both columns everywhere
CREATE TABLE order_items (
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (order_id, product_id)
) ENGINE=InnoDB;
-- Every secondary index on order_items implicitly carries (order_id, product_id)

6. Why SELECT * defeats a covering index

The most common reason a carefully planned covering index does not work in practice is the use of SELECT * instead of an explicit column list. As soon as even one table column is needed that is not contained in the index, the optimizer has to fall back to the full table lookup, regardless of how many of the remaining columns were already available in the index. For the covering index strategy, an explicit, deliberately short column list is therefore not a matter of style but a functional necessity.

In ORM heavy applications that load all columns of a model by default, the application must be explicitly adapted to benefit from a covering index, for example through projected queries for list views that load only the actually displayed columns, while detail views continue to load the full row via the primary key.

7. Weighing storage cost and write load against benefit

A covering index is fundamentally larger than a minimal index over just the filter columns, because additional columns are carried along. That extra storage has to be maintained on every INSERT and UPDATE touching the affected columns, which slightly increases write load compared to a leaner index. For tables with very high write frequency and rarely executed read queries, this trade-off can argue against a covering index.

For read heavy tables, especially ones repeatedly queried with the same column combinations in overview pages, APIs, or reports, the read benefit almost always outweighs the moderate extra write cost. The rule of thumb: the higher the ratio of reads to writes on a table, the more a deliberately wide covering index pays off.

-- Compare index size before and after adding covering columns
SELECT index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE table_name = 'orders' AND stat_name = 'size';

-- idx_customer_status              4.10 MB  (minimal filter index)
-- idx_customer_status_covering    11.80 MB  (with total_amount appended)
-- Roughly 3x larger, but table lookups for this query drop to zero

8. Limits of the covering index strategy

Not every query can be sensibly covered by an index. For queries that use many different, rarely recurring column combinations in the SELECT list, a covering index for every variant would either become very wide, or multiple specialized indexes would be needed, disproportionately increasing write load. In such cases, a normal, lean index combined with an accepted table lookup is often the more pragmatic solution.

For text columns of type TEXT or BLOB, a covering index is also mostly not an option, since these column types can only be indexed with a prefix length and can rarely be usefully carried as an appended column in an index. In such cases, other strategies like separate full-text indexes or outsourced search systems are often the better choice.

9. Covering index compared to other approaches

The table below contrasts the covering index strategy with common alternatives and shows when each approach makes sense.

A covering index and a caching layer are not mutually exclusive. In practice, a good covering index already reduces database load so much that an additional cache only becomes necessary at even higher traffic, keeping overall operations simpler.

10. Summary

A covering index contains every column a query needs and lets MySQL answer the result entirely from the index, without touching the actual table. With InnoDB, where every secondary index hit otherwise requires an additional access to the clustered index, this strategy saves a particularly large amount of random I/O. The signal in EXPLAIN is Using index, while Using index condition still means a table lookup.

The design follows the rules for composite indexes, extended with appended, non-filtering columns for every value needed in the SELECT. SELECT * defeats this strategy almost every time, which is why explicit column lists are mandatory. For read heavy tables with recurring query patterns, the read gain almost always outweighs the moderate extra cost in write operations and storage.

11. FAQ: Covering indexes in MySQL