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

Problems with UUID Primary Keys

Characteristics of UUID

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

Globally unique, which makes it attractive for distributed systems.

Drawbacks of using UUID as the primary key

Index size : Storing a UUID as CHAR(36) consumes 36 bytes per row, whereas a BIGINT primary key uses only 8 bytes. Larger indexes increase storage cost and I/O.

Index splits : UUIDs are random and unordered, so inserts are distributed across the B‑tree. This causes frequent page splits and tree rebalancing, degrading write performance.

Insert performance : Random placement forces InnoDB to modify many leaf pages, leading to higher disk I/O and lower throughput.

Query performance : String comparison is slower than integer comparison, and the larger index range reduces cache efficiency.

Why Updating Data Triggers Index Refreshes

Role of indexes

Indexes (typically B+‑trees) accelerate point lookups and range scans.

When a row is modified, every index that references the changed columns must be updated to keep the index consistent with the table data.

Impact of updates

Primary‑key update : MySQL deletes the old index entry and inserts a new one, which may require tree rotations and additional I/O.

Non‑primary indexed column update : The same delete‑insert cycle occurs for each affected secondary index.

Extra cost for UUID primary keys

Because UUIDs are unordered, changing a UUID often moves the row to a different position in the index, causing more page splits and tree rebalancing than an ordered key such as an auto‑increment integer.

Why Character Primary Keys Reduce Efficiency

Storage overhead

Character keys (e.g., CHAR(36)) occupy more space than numeric keys, inflating the size of the primary‑key index and any secondary indexes that reference it.

Comparison cost

String comparison involves byte‑wise evaluation, which is slower than the single‑CPU‑instruction integer comparison used for BIGINT keys.

Example: WHERE id='550e8400-e29b-41d4-a716-446655440000' is slower than WHERE id=12345.

Index splits

Unordered character keys cause frequent B‑tree page splits, leading to additional I/O and reduced insert throughput.

Optimizing UUID Primary Key Performance

Use ordered UUIDs

Adopt time‑based UUID versions (e.g., UUIDv7) that generate values in roughly increasing order, reducing page splits.

Ordered UUIDs can be built from a timestamp plus a random component, preserving uniqueness while keeping inserts sequential.

Store UUID as binary

Saving the UUID in a BINARY(16) column cuts the storage requirement from 36 bytes to 16 bytes and improves index density.

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

Combine an auto‑increment key with a UUID column

Use a small, sequential BIGINT as the physical primary key for fast inserts, and keep a separate CHAR(36) (or BINARY(16)) column with a UNIQUE index for logical identification.

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

Partition large tables

Partitioning the table (by range, hash, or list) limits the size of each individual index tree, which can improve both insert and query performance on very large datasets.

Summary

UUID primary keys increase index size, cause random inserts, and make updates expensive because of frequent B‑tree rebalancing.

Character‑based primary keys suffer from the same storage and comparison inefficiencies.

Mitigation strategies include using ordered UUIDs, storing UUIDs as BINARY(16), employing a surrogate auto‑increment key together with a UUID, and partitioning large tables to keep index trees manageable.