How an Index Works
An index is an ordered B+ tree beside your table; InnoDB clusters rows on the primary key and every other index points back to it.
SHOW INDEX FROM tbl_name;An index is a separate, ordered structure, kept in sync with the table, that lets the server find rows without reading all of them. InnoDB builds every index as a B+ tree: a shallow, wide tree whose leaf pages are chained in key order, so a single-row lookup costs a handful of page reads and a range scan walks the leaves in order.
Clustered and secondary
InnoDB stores the table itself inside one B+ tree, the clustered index, keyed on the PRIMARY KEY. Its leaf pages are the rows. Every other index is a secondary index, and its leaf entries hold the indexed columns plus the primary key value — not a physical row address. So reading a column that is not in the secondary index costs two tree descents: one to find the primary key, then one in the clustered index to fetch the row. That second descent is the hidden cost behind a cheap-looking EXPLAIN line.
What an index costs
Every INSERT, every DELETE, and every UPDATE of an indexed column must maintain each affected index, and each index takes disk and buffer pool. A wide primary key makes this worse, because it is copied into every secondary index leaf.
The optimizer will also decline an index it has. When it estimates that a query touches a large fraction of a large table, reading sequentially beats thousands of random lookups, and you get a full scan on purpose.
Example
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id BIGINT UNSIGNED NOT NULL,
status VARCHAR(20) NOT NULL,
total_cents INT NOT NULL,
placed_at DATETIME NOT NULL,
PRIMARY KEY (id), -- clustered index: its leaves ARE the rows
KEY idx_customer (customer_id) -- secondary: leaves hold customer_id + id
) ENGINE=InnoDB;
-- Answered inside idx_customer alone. customer_id is the key and id rides
-- along in every leaf entry, so the clustered index is never touched.
SELECT id FROM orders WHERE customer_id = 42;
-- Same index finds the entries, but total_cents is not in it. Each matching
-- entry now costs a second descent into the clustered index, keyed by id.
SELECT id, total_cents FROM orders WHERE customer_id = 42;
-- What actually exists on the table, with the optimizer's cardinality
-- estimate per key part (higher cardinality = more selective).
SHOW INDEX FROM orders;When to use it
- Deciding whether a slow lookup needs a new index or a narrower primary key.
- Explaining why a query that reads one extra column suddenly got much slower.
- Auditing a write-heavy table to find indexes that cost more than they return.
- Choosing between a natural composite primary key and an AUTO_INCREMENT surrogate.
More examples
The table with no primary key
InnoDB always clusters on something. Without a PRIMARY KEY or a UNIQUE NOT NULL index it invents a hidden row ID that you cannot reference from SQL, which rules out keyset pagination, replication-friendly lookups, and useful foreign keys. Reach for this comparison whenever you inherit a table and SHOW CREATE TABLE shows no PRIMARY KEY line.
-- A non-unique index is not a clustering candidate, so this table gets
-- InnoDB's hidden 6-byte GEN_CLUST_INDEX on insertion order.
CREATE TABLE events_bad (
event_uuid CHAR(36) NOT NULL,
payload JSON NOT NULL,
KEY idx_uuid (event_uuid)
) ENGINE=InnoDB;
-- Explicit surrogate key, narrow and monotonic, plus the uniqueness you
-- actually wanted on the business key.
CREATE TABLE events_good (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
event_uuid CHAR(36) NOT NULL,
payload JSON NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_uuid (event_uuid)
) ENGINE=InnoDB;
-- Confirm which key InnoDB is clustering on.
SELECT NAME, TYPE FROM information_schema.INNODB_INDEXES
WHERE TABLE_ID = (
SELECT TABLE_ID FROM information_schema.INNODB_TABLES
WHERE NAME = CONCAT(DATABASE(), '/events_bad')
);Why primary key width propagates
Every secondary index leaf carries the primary key so it can find the row. A 40-byte natural key is therefore paid for once per secondary index entry, not once per row. This is the concrete reason to prefer a narrow surrogate key on wide, heavily indexed tables — and the reason an audit of an oversized table often starts at the PRIMARY KEY line.
-- Natural composite key: CHAR(36) + DATETIME, carried into idx_taken and
-- every other secondary index on this table.
CREATE TABLE readings_natural (
sensor_uuid CHAR(36) NOT NULL,
taken_at DATETIME NOT NULL,
celsius DECIMAL(5,2) NOT NULL,
PRIMARY KEY (sensor_uuid, taken_at),
KEY idx_taken (taken_at) -- leaf entries also carry the primary key columns
) ENGINE=InnoDB;
-- Narrow surrogate key: 8 bytes in each secondary leaf instead of 40+,
-- with the natural key kept as a constraint.
CREATE TABLE readings_surrogate (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
sensor_uuid CHAR(36) NOT NULL,
taken_at DATETIME NOT NULL,
celsius DECIMAL(5,2) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_sensor_time (sensor_uuid, taken_at),
KEY idx_taken (taken_at)
) ENGINE=InnoDB;
-- Compare what you are actually storing.
SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME IN ('readings_natural', 'readings_surrogate');Auditing what you maintain on every write
Before a bulk load or a write-latency investigation, list the indexes the table is paying for. Invisible indexes (MySQL 8.0 and later) let you test a drop safely: the optimizer stops considering the index, but InnoDB keeps maintaining it, so reverting is one instant DDL statement instead of a multi-hour rebuild.
-- Every index on the table, key part by key part.
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;
-- Retire a suspected-unused index without dropping it. The optimizer ignores
-- it, so you learn whether anything regresses.
ALTER TABLE orders ALTER INDEX idx_customer INVISIBLE;
-- Still need it for one query while it is hidden? Opt in per statement.
SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ id
FROM orders WHERE customer_id = 42;
-- Reverting is metadata only.
ALTER TABLE orders ALTER INDEX idx_customer VISIBLE;
Discussion