InnoDB and the clustered primary key
In InnoDB the table is the primary-key index. That one fact explains covering indexes, why wide or random keys hurt, and what happens to a table with no key.
After this lesson you can
- Explain what a clustered index is and what a secondary index leaf stores
- Predict when a secondary index answers a query without touching the table
- Say why a short, increasing primary key is the default choice
- Find tables that have no primary key
MySQL hands storage to a pluggable storage engine. Since MySQL 5.5 the
default has been InnoDB: transactional, row-locking, crash-safe. Older
schemas sometimes still carry ENGINE=MyISAM tables, and the difference is
not academic.
CREATE TABLE ledger_innodb (id INT PRIMARY KEY, note VARCHAR(20)) ENGINE=InnoDB;CREATE TABLE ledger_myisam (id INT PRIMARY KEY, note VARCHAR(20)) ENGINE=MyISAM;START TRANSACTION;INSERT INTO ledger_innodb VALUES (1, 'kept?');INSERT INTO ledger_myisam VALUES (1, 'kept?');ROLLBACK; SELECT 'InnoDB' AS engine, COUNT(*) AS rows_after_rollback FROM ledger_innodbUNION ALLSELECT 'MyISAM', COUNT(*) FROM ledger_myisam;MyISAM has no transactions at all. ROLLBACK cannot undo a write it never
tracked, so the row stays. Mixing the two engines inside one "transaction"
gives you half a rollback.
The table is the index
InnoDB stores every table as a B+tree ordered by the primary key. The leaf pages of that tree hold the complete rows. This is the clustered index: there is no separate heap of rows that the key points into, the key order is the storage order.
A secondary index (any other KEY, UNIQUE or not) is a second B+tree.
Its leaves hold the indexed columns plus the row's primary-key value,
which is how InnoDB finds the full row: search the secondary tree, read the
primary key, then search the clustered tree with it.
That has a consequence you can observe. A query that needs only the indexed columns and the primary key can be answered from the secondary index alone.
CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(100) NOT NULL, name VARCHAR(50) NOT NULL, KEY idx_email (email));INSERT INTO users VALUES (1, 'ann@example.com', 'Ann'), (2, 'bob@example.com', 'Bob'), (3, 'cy@example.com', 'Cy'), (4, 'dee@example.com', 'Dee'), (5, 'eve@example.com', 'Eve'), (6, 'fay@example.com', 'Fay');ANALYZE TABLE users;-- EXPLAIN ... INTO captures the plan as JSON so we can pick out two fields.EXPLAIN FORMAT=JSON INTO @a SELECT id, email FROM users WHERE email = 'cy@example.com';EXPLAIN FORMAT=JSON INTO @b SELECT id, name FROM users WHERE email = 'cy@example.com'; SELECT 'id, email' AS selected, JSON_UNQUOTE(JSON_EXTRACT(@a, '$.query_block.table.key')) AS index_used, IF(JSON_EXTRACT(@a, '$.query_block.table.using_index') = true, 'yes', 'no') AS answered_from_index_aloneUNION ALLSELECT 'id, name', JSON_UNQUOTE(JSON_EXTRACT(@b, '$.query_block.table.key')), IF(JSON_EXTRACT(@b, '$.query_block.table.using_index') = true, 'yes', 'no');idx_email was declared on email only, yet SELECT id, email never
touches the table: id is already sitting in every leaf of idx_email.
Ask for name and InnoDB must take the second step into the clustered
index for each matching row. EXPLAIN calls the first case Using index (a
covering index).
What this means for choosing a primary key
- Keep it short. The primary key is copied into every secondary index entry. A UUID stored as a 36-character string instead of a 4-byte integer makes every index on the table carry that string in every entry.
- Prefer increasing values. New rows with an
AUTO_INCREMENTkey go to the right-hand end of the tree. Random keys (UUIDv4 strings) land on arbitrary pages, so inserts split pages and touch a much larger part of the tree. If you need UUIDs, store them asBINARY(16); MySQL'sUUID_TO_BIN(uuid, 1)reorders the time fields of a version-1 UUID so they increase. - Never update it casually. Changing a primary-key value moves the row to another place in the tree and rewrites every secondary index entry.
A table with no primary key
InnoDB always clusters on something. Without a PRIMARY KEY it uses the
first UNIQUE index whose columns are all NOT NULL; failing that, it
invents a hidden 6-byte row id you cannot query or use. Replication tools,
online schema-change tools and clustered setups such as Group Replication
all want a real primary key, which is why finding keyless tables is a
routine audit, and the exercise below.
Try it yourself
2 visible tests · 2 hidden testsThe current database holds a mix of tables and views. Using
information_schema, return the name of every base table in the
current database (DATABASE()) that has no PRIMARY KEY, as a
single column table_name, ordered by table_name.
A table with only a UNIQUE key still counts as having no primary key,
and a view is not a base table.
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(50));CREATE TABLE audit_log (happened_at DATETIME, message VARCHAR(200));CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT);CREATE TABLE import_rows (line_no INT, payload TEXT);Sign up to run the tests, check the hidden ones and save your progress. Sign up