Why Using UUID as a Primary Key Can Hurt MySQL Performance

1. Problems with UUID as Primary Key

Characteristics of UUID

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

Globally unique, suitable for distributed systems.

Disadvantages of UUID as Primary Key

Low index efficiency

When stored as VARCHAR(36) a UUID occupies 36 bytes, while a BIGINT primary key occupies only 8 bytes; larger indexes increase storage and reduce query speed.

UUIDs are unordered, so inserts cause frequent B‑tree splits and rebalancing.

Poor insert performance

Randomness: each new row may be placed anywhere in the index tree, triggering many adjustments.

Page splits in InnoDB’s B+‑tree increase disk I/O.

Poor query performance

String comparison is slower than integer comparison, especially on large tables.

The larger key widens the index scan range, further degrading query speed.

2. Index Refresh on Data Modification

Role of the index

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

Impact of updating data

Updating a primary‑key value requires deleting the old index entry and inserting a new one, which forces tree adjustments and extra I/O.

Updating an indexed non‑primary column also forces the corresponding index records to be updated, causing similar tree adjustments.

Extra cost of UUID primary‑key updates

Because UUIDs are unordered, changing a UUID may place the new value in a different position of the index tree, making the update more expensive than with ordered keys such as auto‑increment IDs.

3. Inefficiency of Character Primary Keys

Large storage space

Character keys like UUID consume more bytes (36 bytes as CHAR/VARCHAR) than integer keys (8 bytes as BIGINT), increasing disk I/O for both storage and index traversal.

Slower comparison

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

Frequent index splits

Unordered character keys cause random inserts, leading to frequent B‑tree splits and rebalancing, which degrades performance.

4. Optimizing UUID Primary Key Performance

Use ordered UUIDs

Generate ordered UUIDs (e.g., UUIDv7) based on timestamps to reduce index and page splits.

Store UUID in binary form

Storing UUID as BINARY(16) instead of CHAR(36) cuts storage size by more than half.

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

Combine auto‑increment primary key with UUID

Use an auto‑increment BIGINT as the physical primary key and a UUID column as a logical unique identifier.

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

Partition large tables

Partitioning reduces the size of each individual index tree, improving query performance on very large tables.