Table and indexes (fixed for this lesson)
About this exampleusers: 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.
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 indexonly when no clustered read is needed;Using index conditionis 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.