Lesson 0001 · Internals & Performance

The clustered index

Why an InnoDB table is a B+tree ordered by its primary key — and why almost every index decision falls out of that one fact.

~9 minRetrieval quiz + hands-on EXPLAINCheat sheet: here

You already write SELECT, JOIN, and CREATE INDEX. This lesson turns a fact you may have half-heard — "InnoDB tables are clustered by primary key" — into the keystone mental model this whole course hangs off. Get this one idea and a surprising amount derives itself: why your primary-key choice is really a storage decision, why a lookup by a secondary index can cost twice as much as a lookup by primary key, what a "covering index" actually buys you, and why a random UUID primary key can quietly wreck write performance.

The one idea

In InnoDB, the table is not a pile of rows with indexes pointing at it. The table is an index — a B+tree ordered by the primary key, whose leaf nodes contain the entire row. This is the clustered index, and there is exactly one per table. — MySQL Manual: "Each InnoDB table has a special index called the clustered index that stores row data… the clustered index is synonymous with the primary key."

So what is a secondary index?

Every other index — the ones you make with CREATE INDEX — is a secondary index: its own B+tree, ordered by the columns you indexed. But here is the twist that everything else depends on. A secondary index leaf does not store a disk address for the row. It stores the primary-key value, and uses that to find the row back in the clustered index. — MySQL Manual: "each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index… InnoDB uses this primary key value to search for the row in the clustered index."

The consequence: the double lookup

Finding a full row through a secondary index is therefore two B+tree descents, not one:

1. Walk the secondary index to find the matching primary-key value.
2. Walk the clustered index by that primary key to fetch the rest of the row.

This second step has a name — a bookmark lookup — and it is why a query filtered by an indexed column can still be slower than you'd expect: each matched row pays for a second descent.

The other consequence: primary-key choice is a storage decision

Two rules that beginners memorize as arbitrary "best practices" are really just this model talking:

1. Keep the primary key short

Every secondary index copies the primary-key columns into every one of its entries (that's how it points back). So a fat PK — say a 40-byte string — is duplicated across every secondary index, inflating all of them. — MySQL Manual: "If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key."

2. Prefer a monotonically increasing primary key

Because rows are physically ordered by PK, an AUTO_INCREMENT integer means every insert lands at the right edge of the tree — append-only, cheap, compact. A random primary key (a random UUIDv4) forces inserts into the middle of the tree constantly, causing page splits and fragmentation. MySQL recommends adding an auto-increment column when you have no natural short key. — MySQL Manual: "If there is no logical unique and non-null column… add an auto-increment column."

Check yourself

Answer from memory — the effortful recall is what builds retention. Feedback is immediate.

Hands-on: watch the double lookup disappear

Recall is one thing; seeing EXPLAIN flip to Using index is another. You need a local MySQL 8 — e.g. docker run --rm -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=1 mysql:8, then mysql -h127.0.0.1 -uroot.

1 · A table with a secondary index

CREATE DATABASE lab; USE lab;
CREATE TABLE users (
  id     INT AUTO_INCREMENT PRIMARY KEY,   -- clustered index
  email  VARCHAR(120) NOT NULL,
  name   VARCHAR(120) NOT NULL,
  INDEX idx_email (email)                  -- secondary index
);
INSERT INTO users (email, name) VALUES
  ('a@x.com','Ann'), ('b@x.com','Bo'), ('c@x.com','Cy');

2 · Predict, then run — a NON-covering query

EXPLAIN SELECT name FROM users WHERE email = 'a@x.com';

3 · Now a covering query

EXPLAIN SELECT id, email FROM users WHERE email = 'a@x.com';

Bonus: run EXPLAIN SELECT * FROM users WHERE id = 1; — access type const/eq_ref by primary key, the cheapest read there is. Bring any surprising plan to your teacher and we'll read it together.