Covering index: why "Using index" in EXPLAIN is the goalMySQL 8.4 • MariaDB 11.4

Three acts: non-covering plan, covering plan, and the InnoDB PK-append property that silently covers queries you didn't realize were covered.

Table and indexes (fixed for this lesson)

About this example

users: 9 rows, PRIMARY KEY (id), KEY idx_status (status). Act 2 adds KEY idx_status_email (status, email). Every InnoDB secondary index entry also holds the primary key id.

Ready: press Next step, or Play to run it all
0.0s 0.0s

About this example

Example query this animation executes

CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(64),
                    status VARCHAR(16), country CHAR(2),
                    KEY idx_status (status));
-- Acts 1 and 2 (Act 2 first adds an index):
SELECT email FROM users WHERE status = 'active';
CREATE INDEX idx_status_email ON users (status, email);
-- Act 3, with idx_status:
SELECT id FROM users WHERE status = 'active';

One table, three plans. Watch where each returned value comes from: the index entry alone, or a second lookup in the clustered index. Act 3 shows the primary key that InnoDB keeps in every secondary index entry.

What you'll see in the animation

  • Left: the leaf entries of the secondary index, sorted by status and then id. Right: the leaf rows of the clustered index (the table itself), sorted by id, with every column.
  • The outlined entry is the one being read. In Act 1 its id travels along an orange arrow to the clustered index, and the row it fetches turns yellow.
  • Green entries answered the query by themselves (Acts 2 and 3). Grey rows on the right were never read.
  • The line under the drawing counts index entries read and clustered rows read. EXPLAIN's Extra column says Using index only when no clustered read is needed; Using index condition is a different feature.
  • Next step reads one entry at a time; the simplified code beside the drawing highlights the lines each step runs.

Live plan stats

Phase

—

EXPLAIN Extra ?The Extra column text MySQL prints for this access. 'Using index' = covering; anything else usually means a second lookup per row.

—

Index reads ?Secondary index entries read, including the first entry past the range (status = 'inactive'), which is how the read knows to stop.

—

Clustered (table) reads ?Rows read from the primary/clustered index. Zero means the plan is covering.

—

Learn more — the "Using index" vs "Using index condition" trap

Two strings in EXPLAIN's Extra column look similar and are routinely confused. They mean different things:

Using index — covering

The read is satisfied entirely from the secondary index. No lookup into the clustered index happens. In MySQL source this is pushed when table->key_read is true (see sql/opt_explain.cc around line 1615). A covering query's cost is proportional to the matching index entries — never to the table size.

Using index condition — ICP (not covering)

Index Condition Pushdown. Part of the WHERE clause is checked on the index entry inside the storage engine, so entries that fail it never cost a clustered-index lookup. The entries that pass still do. MySQL and MariaDB both skip ICP when the chosen index covers the query, so Using index condition always means full rows are still being read. Useful, but not the same as covering.

MySQL's EXPLAIN FORMAT=TREE says it in words: Covering index lookup on users using idx_status_email versus Index lookup on users using idx_status.

The InnoDB PK-append property

InnoDB's clustered-index primary key is silently appended to every secondary index. That's implemented in storage/innobase/dict/dict0dict.cc (dict_index_build_internal_non_clust): the internal index struct is created with index->n_fields + 1 + clust_index->n_uniq fields — the +n_uniq is the PK columns being appended. So:

CREATE TABLE users (id INT PRIMARY KEY, status VARCHAR(16), email VARCHAR(64));
CREATE INDEX idx_status ON users (status);
-- This index physically stores (status, id) — the id was appended for free.

SELECT id FROM users WHERE status = 'active';
-- EXPLAIN: Using index.  ← covering, even though `id` isn't in
--                           the index definition.

This property is why defining extra indexes "just for the id" is almost always wasted effort on InnoDB, in MySQL and in MariaDB. MyISAM and Aria secondary indexes point at a row position instead of holding the primary key, so on those engines write the primary key columns into the index yourself if you need them covered.