Why UUID Primary Keys Slow Down MySQL and How to Optimize Them

Problems with UUID as Primary Key

(1) Characteristics of UUID

UUID is a 128‑bit identifier usually rendered as a 36‑character string, e.g. 550e8400-e29b-41d4-a716-446655440000.

It is globally unique, which makes it attractive for distributed systems.

(2) Drawbacks of using UUID as a primary key

Index size : stored as a CHAR(36) string it occupies 36 bytes, while a BIGINT primary key uses only 8 bytes. Larger indexes increase storage and I/O.

Index fragmentation : UUIDs are random and unordered, causing frequent B‑tree page splits and rebalancing on inserts.

Insert performance : each new UUID can be placed anywhere in the index, leading to constant page splits and higher disk I/O.

Query performance : string comparison is slower than integer comparison, and the wider index scans more rows.

Why Updating Data Triggers Index Refresh

(1) Role of an index

Indexes (e.g., B+‑tree) accelerate query processing.

When data changes, the index must be updated to stay consistent.

(2) Impact of data modification

Updating a primary key : MySQL deletes the old index entry and inserts a new one, causing tree adjustments and extra I/O.

Updating a non‑primary indexed column : The corresponding index records are updated, also leading to tree adjustments.

(3) Extra overhead of UUID primary keys

Because UUIDs are unordered, changing a UUID often inserts the new value at a different position, forcing frequent rebalancing.

Ordered primary keys (e.g., AUTO_INCREMENT) have far lower modification cost.

Why Character Primary Keys Reduce Efficiency

(1) Large storage footprint

Character keys such as UUID consume more bytes than integer keys, inflating index size and I/O.

(2) Slower comparison

String comparison is slower than integer comparison; for example WHERE id = '550e8400-e29b-41d4-a716-446655440000' is slower than WHERE id = 12345.

(3) Index splitting

Unordered character keys cause frequent B‑tree splits and rebalancing during inserts.

How to Optimize UUID Primary Key Performance

(1) Use ordered UUIDs

Generate time‑based UUIDs (e.g., UUIDv7) so that values are inserted in order, reducing page splits.

(2) Store UUID as binary

CREATE TABLE users (
    id BINARY(16) PRIMARY KEY,
    name VARCHAR(255)
);

(3) Combine AUTO_INCREMENT primary key with UUID

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) UNIQUE,
    name VARCHAR(255)
);

(4) Partition large tables

Partitioning reduces the size of each index tree, improving query performance on massive datasets.