A table with a CHAR(36) UUID primary key is one of the few schema decisions that gets slower as the data grows, and it rarely shows up in a slow query log. The queries look fine. Inserts take two milliseconds instead of two hundred microseconds, the buffer pool hit rate drifts down over months, and the table on disk is twice the size you expected. By the time anyone measures it, the table has half a billion rows and the fix is a migration.
This walkthrough covers the mechanism, how to measure it on your own tables, and the two versions of the fix — the cheap one for new tables and the expensive one for existing ones.
The mechanism: InnoDB is a clustered index
In InnoDB the primary key is not a separate structure. The table is the primary key B-tree, with the full row stored in the leaf pages, in primary key order. Two consequences follow, and both of them matter.
Inserts go where the key says. With an AUTO_INCREMENT key, every new row belongs at the right edge of the tree. InnoDB fills one 16 KB page, moves to the next, and only that rightmost page needs to be in the buffer pool. This is the case InnoDB optimises for explicitly: a sequential insert into a page-splitting position splits 100/0 rather than 50/50, so pages end up nearly full.
With a random UUIDv4, the key is uniformly distributed across the whole keyspace. Each insert lands in an arbitrary leaf page, which must first be read from disk if it is not cached. Once that table exceeds the buffer pool, every insert becomes a read plus a write. And when a page in the middle is full, InnoDB splits it in half, leaving two pages about 50% full. A table built this way commonly settles near 60–70% page fill, so the same rows occupy 40–60% more pages, which means 40–60% more buffer pool consumed to cache the same working set.
Every secondary index carries the primary key. Secondary index leaf entries store the indexed columns plus the primary key value, because that is how InnoDB finds the row. A CHAR(36) UUID in utf8mb4 is up to 144 bytes per entry before overhead; stored as BINARY(16) it is 16 bytes; a BIGINT is 8. On a table with four secondary indexes and 200 million rows, the difference between CHAR(36) and BINARY(16) is tens of gigabytes of index that has to fit in memory to stay fast.
The storage cost is arithmetic and not arguable. The insert cost is the one worth measuring.
Measuring it on your tables
Three checks, all read-only, all safe on production.
1. Page splits per second. InnoDB counts them:
SELECT NAME, COUNT
FROM information_schema.INNODB_METRICS
WHERE NAME IN ('index_page_splits','index_page_merge_attempts','index_page_merge_successful');
If index_page_splits is disabled, enable the index module (SET GLOBAL innodb_monitor_enable = 'module_index';) and sample the counter twice, a minute apart. A write-heavy table on sequential keys produces splits roughly in proportion to pages filled. A random-key table produces far more, and the ratio of splits to rows inserted stops falling as the table grows.
2. Actual page fill. Compare logical row size to physical size:
SELECT TABLE_NAME,
TABLE_ROWS,
ROUND(DATA_LENGTH/1024/1024) AS data_mb,
ROUND(INDEX_LENGTH/1024/1024) AS index_mb,
ROUND(DATA_LENGTH/NULLIF(TABLE_ROWS,0)) AS bytes_per_row
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY DATA_LENGTH DESC
LIMIT 20;
TABLE_ROWS is an estimate, so treat bytes_per_row as an order of magnitude. If it is well above the sum of your column widths, you are paying for half-empty pages. For an exact answer on one table, innodb_ruby space-summary or a SELECT COUNT(*) against a known row width will settle it.
3. Where the insert time goes. The Performance Schema will tell you whether inserts are waiting on I/O rather than CPU:
SELECT EVENT_NAME, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1e12,2) AS secs
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/io/file/innodb%'
ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;
Random-insert pain shows up as read I/O on a workload that is logically write-only — the tell is that you are reading pages in order to insert into them.
The fix for new tables: time-ordered UUIDs
The reason teams choose UUIDs is usually sound: IDs generated in the application, in a mobile client, or across shards, without a round trip and without coordination. You do not have to give that up. You have to give up randomness in the high-order bits.
UUIDv7 (standardised in RFC 9562, which replaced RFC 4122 in May 2024) puts a 48-bit Unix millisecond timestamp in the leading bits, followed by random data. IDs generated on different machines still do not collide, but they sort roughly in creation order, so inserts land at the right edge of the B-tree again. Most language ecosystems now have a UUIDv7 generator; MySQL itself does not generate them as of 8.4, so generation stays in the application.
Store it as BINARY(16), not as text:
CREATE TABLE event (
id BINARY(16) NOT NULL,
-- ...
PRIMARY KEY (id)
);
If you need to read or write the canonical hyphenated form at the SQL boundary, add a generated column so applications and humans see text while InnoDB stores bytes:
ALTER TABLE event
ADD COLUMN id_text CHAR(36) GENERATED ALWAYS AS (BIN_TO_UUID(id)) VIRTUAL;
A virtual generated column costs no storage and can be indexed if you truly need lookups by text form.
If you are stuck on UUIDv1/v4 generation, MySQL 8's UUID_TO_BIN(uuid, 1) handles the v1 case: the second argument swaps the time-low and time-high fields so that the bytes sort in timestamp order. It does nothing useful for v4, which has no timestamp — for v4 the only fix is to change the generator.
A reasonable middle path when you cannot change ID generation at all: keep the UUID as a unique secondary key for external references, and give the table a BIGINT AUTO_INCREMENT primary key so the clustered index stays sequential. You pay one extra index and one extra lookup on UUID-based reads; you get back sequential inserts and an 8-byte key in every other secondary index. On a table with several secondary indexes that trade is usually favourable, and it is easy to reason about.
Migrating an existing table, honestly
Changing a primary key rewrites the table. There is no online path that avoids that: ALTER TABLE ... DROP PRIMARY KEY, ADD PRIMARY KEY is ALGORITHM=COPY in practice, and every secondary index is rebuilt because the PK they carry changed. Plan it as a data migration, not a schema tweak.
What that means in practice:
- Disk. You need free space for a full second copy of the table plus its indexes, whether you use
pt-online-schema-change,gh-ost, or a rebuild on a replica. On a 400 GB table that is 400+ GB free, and on cloud storage that may mean growing the volume first. - Time. Rewrites run at somewhere between 5 and 50 GB/hour depending on row width, index count, and how hard you are willing to push I/O. Measure on a restored copy before you schedule anything.
- Foreign keys. If other tables reference the old key, they must change too, in the same migration. This is usually the part that turns a one-table job into a three-week project.
- Replication. A table rewrite generates binlog proportional to the table size. Replicas will lag; a single-threaded apply path will lag badly. Check
replica_parallel_workersand yourbinlog_transaction_dependency_trackingsetting before you start, and throttle the copy on replica lag rather than on wall clock.
The cheaper alternative, when the pain is storage rather than insert rate: leave the key semantics alone and just change the type from CHAR(36) to BINARY(16). That is still a rewrite, but it is one table, no application-visible ID change beyond encoding, and it typically cuts index size by more than half. If inserts are already fast enough and your complaint is buffer pool pressure, this is the whole fix.
When random UUIDs are fine
They are fine on small tables — anything that comfortably fits in the buffer pool never pays the random-read cost, and a few thousand half-full pages are irrelevant. They are fine on low-write tables, where the split rate is negligible. They are fine when the table has no secondary indexes to inflate.
The cost is real on high-insert-rate tables that exceed memory. That is a narrow condition, and it describes most event, log, message, and audit tables in production.
What to do this week
Run the three measurements above against your top five tables by size. If a table is bigger than the buffer pool, takes heavy inserts, and has a random 36-character primary key, you have found a fix worth scheduling. If it is under a few gigabytes, write it down and move on — there is almost certainly a filesort somewhere costing you more.
If you want a second read on which of your tables this actually applies to, and what the rewrite would cost against your row widths and replication topology, our performance and health review ends in a ranked fix list with expected impact. We work read-only first, and we will tell you when the answer is to leave it alone.