Storage Engines

Article · 3:00

InnoDB against MyISAM, and the clustered index that shapes everything about InnoDB.

The storage engine decides how rows are physically kept, and the choice has been settled for years: InnoDB, for everything. It has transactions, foreign keys, row-level locking, crash recovery and a buffer pool that caches both data and indexes. MyISAM has none of those - its locking is table-level, so one write blocks every reader - and it survives only in old schemas and some system tables.

The structural fact worth understanding about InnoDB is that it is clustered: the table is its primary key index. Rows are stored in the leaf pages of that B-tree, in primary key order. Three consequences follow, and they explain a lot of otherwise mysterious advice.

Looking a row up by primary key is one descent, with the row itself at the bottom - there is no second lookup. An increasing primary key makes inserts append to the end, while a random one makes them land in the middle of the tree, causing page splits and fragmentation - which is why AUTO_INCREMENT beats a random UUID. And every secondary index stores the primary key as its pointer, so a long primary key makes every other index on that table larger.

That last point also explains the covering index: when a secondary index happens to contain every column a query needs, the query can be answered from the index alone, without going back to the clustered index for the row. EXPLAIN reports it as Using index, and it is often the single biggest win available.

-- Which engine each table is on. A legacy MyISAM table silently ignores
-- foreign keys and locks the whole table on every write.
SELECT table_name, engine, row_format,
       ROUND(data_length  / 1024 / 1024) AS data_mb,
       ROUND(index_length / 1024 / 1024) AS index_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length DESC;

ALTER TABLE legacy_logs ENGINE = InnoDB;

-- InnoDB is clustered: the table IS the primary key index, with the row
-- in the leaf. So this is one descent and the row is there.
SELECT * FROM orders WHERE id = 42;

-- And every secondary index carries the primary key as its pointer, so
-- a long primary key inflates every other index on the table.
CREATE INDEX idx_orders_placed ON orders (placed_at);

-- A COVERING index: every column the query needs is in the index, so
-- the row itself is never fetched. EXPLAIN says "Using index".
CREATE INDEX idx_orders_cover ON orders (customer_id, placed_at, total);

EXPLAIN SELECT customer_id, placed_at, total
FROM orders WHERE customer_id = 7 ORDER BY placed_at DESC;

-- The buffer pool is the cache that decides whether a read hits disk.
SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb;