MySQL Covering Index: Answer Queries from the Index

Normally, a lookup through a secondary index is two steps: find the primary key in the index, then read the full row from the table to get the other columns.

A covering index contains all the columns the query needs. MySQL answers the query from the index alone. The second step disappears.

query.sql

SQL

SET SESSION cte_max_recursion_depth = 100000;

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(10) NOT NULL,
    total DECIMAL(8,2) NOT NULL,
    notes VARCHAR(200) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL
);
INSERT INTO orders (customer_id, status, total, notes, created_at)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 30000)
SELECT 1 + MOD(i * 7, 500), ELT(1 + MOD(i, 3), 'paid', 'shipped', 'cancelled'), 10 + MOD(i * 13, 900), REPEAT('x', 100), '2026-01-01 00:00:00' + INTERVAL i MINUTE FROM n;

CREATE INDEX idx_customer ON orders (customer_id);
ANALYZE TABLE orders;

A normal index: lookups into the table

query.sql

SQL

EXPLAIN FORMAT=TRADITIONAL SELECT customer_id, total FROM orders WHERE customer_id = 42;

Output

OUTPUT

+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_customer  | idx_customer | 4       | const |   60 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set

idx_customer finds the rows for customer 42, but total is not in the index, so MySQL must go back to the table for every matching row. About 60 lookups here.

A covering index

query.sql

SQL

1
2

CREATE INDEX idx_customer_total ON orders (customer_id, total);
EXPLAIN FORMAT=TRADITIONAL SELECT customer_id, total FROM orders WHERE customer_id = 42;

Output

OUTPUT

+----+-------------+--------+------------+------+---------------------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys                   | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_customer,idx_customer_total | idx_customer_total | 4       | const |   60 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set

Extra: Using index is the sign. All the data the query needs (customer_id, total) is in idx_customer_total, so no row is read from the table. The query touches a small, narrow structure instead of wide table rows (the table rows are large here, because of the notes column).

The primary key comes free

Every secondary index also contains the primary key. So an index on (customer_id) already “covers” SELECT id FROM orders WHERE customer_id = 42, since id is stored in it.

query.sql

SQL

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM orders WHERE customer_id = 42;

Output

OUTPUT

+----+-------------+--------+------------+------+---------------------------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys                   | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------------------------+--------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_customer,idx_customer_total | idx_customer | 4       | const |   60 |   100.00 | Using index |
+----+-------------+--------+------------+------+---------------------------------+--------------+---------+-------+------+----------+-------------+
1 row in set

Why it is fast

Less I/O. Index pages are smaller than table pages, and more of them fit in memory.
No random lookups. Fetching 60 scattered rows from the table means up to 60 page reads.
Sequential reads. The index entries for one customer sit together.

Covering indexes are most valuable for frequent, read-heavy queries that touch few columns: lists, counters, dashboards, API endpoints.

Covering aggregates and sorts

query.sql

SQL

EXPLAIN FORMAT=TRADITIONAL SELECT status, COUNT(*) FROM orders WHERE customer_id = 42 GROUP BY status;

Output

OUTPUT

+----+-------------+--------+------------+------+---------------------------------+--------------+---------+-------+------+----------+-----------------+
| id | select_type | table  | partitions | type | possible_keys                   | key          | key_len | ref   | rows | filtered | Extra           |
+----+-------------+--------+------------+------+---------------------------------+--------------+---------+-------+------+----------+-----------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_customer,idx_customer_total | idx_customer | 4       | const |   60 |   100.00 | Using temporary |
+----+-------------+--------+------------+------+---------------------------------+--------------+---------+-------+------+----------+-----------------+
1 row in set

This needs customer_id and status only. Add status to the index and it is covered:

query.sql

SQL

1
2

CREATE INDEX idx_customer_status ON orders (customer_id, status);
EXPLAIN FORMAT=TRADITIONAL SELECT status, COUNT(*) FROM orders WHERE customer_id = 42 GROUP BY status;

Output

OUTPUT

+----+-------------+--------+------------+------+-----------------------------------------------------+---------------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys                                       | key                 | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------------------------------------+---------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_customer,idx_customer_total,idx_customer_status | idx_customer_status | 4       | const |   60 |   100.00 | Using index |
+----+-------------+--------+------------+------+-----------------------------------------------------+---------------------+---------+-------+------+----------+-------------+
1 row in set

The trade-off

A covering index is wider: it takes more space, and every write must update it.

Pros	Cons
Much less I/O for the query	A bigger index (more disk and memory)
Often avoids sorting and grouping	Slower inserts and updates for the extra columns
No table lookups	Easy to over-do: one index per query leads to dozens of indexes

Use covering indexes for the few queries that matter most.

Things that stop an index from covering

**SELECT ***: it asks for every column, so the index almost never covers it. Select only what you need (SELECT).
A column of type TEXT or BLOB (indexed only by prefix: a prefix index never covers a full column).
A prefix index on a column used in the select list.
A column that is not in the index, even if it is “small”.

MySQL has no INCLUDE

Some databases (SQL Server, PostgreSQL) have CREATE INDEX ... INCLUDE (column) to add non-key columns to an index. MySQL does not. Extra columns are simply added as normal index columns after the key columns, as above. They also become part of the sort order, so put them last.

lightbulb

Look for "Using index"

In EXPLAIN, Using index (without Using where next to type: range… or with it) means a covering index. Seeing it on your hottest queries is a good sign.