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

1. Problems with using UUID as a primary key

Characteristics of UUID

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

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

Disadvantages

Index size : Stored as CHAR(36) a UUID occupies 36 bytes, while a BIGINT primary key occupies only 8 bytes. Larger indexes increase storage and I/O costs.

Index fragmentation : UUIDs are essentially random and unordered. Inserting new rows can cause frequent B‑tree splits and rebalancing, degrading insert performance.

Insert performance : Random placement of rows forces InnoDB to split pages, leading to extra disk I/O.

Query performance : Comparing 36‑byte strings is slower than comparing 8‑byte integers, and the larger index widens the scan range. Example: WHERE id = '550e8400-e29b-41d4-a716-446655440000' is noticeably slower than WHERE id = 12345.

2. Why updating data triggers index refresh

Indexes (typically B+ trees) accelerate query processing.

Updating a primary‑key value : MySQL must delete the old index entry and insert a new one, causing tree adjustments and additional I/O.

Updating an indexed non‑primary column : The corresponding index record also needs to be updated, incurring the same overhead.

Because UUIDs are unordered, a change often moves the row to a different position in the index tree, making the operation more expensive than with an ordered key such as an auto‑increment integer.

3. Why character (string) primary keys reduce efficiency

Larger storage footprint : String keys consume more bytes, increasing the amount of data read/written during scans and updates.

Slower comparison : String comparison is computationally heavier than integer comparison, especially on large tables.

Frequent index splits : Unordered string keys cause the B‑tree to split more often, adding overhead to inserts and updates.

4. Optimising UUID primary‑key performance

Use ordered UUIDs (e.g., UUIDv7) that embed a timestamp, preserving insert order and reducing page splits.

Store UUIDs in binary form . Define the column as BINARY(16) instead of CHAR(36) to halve the storage size.

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

Hybrid auto‑increment + UUID : Use an auto‑increment BIGINT as the physical primary key and keep a UUID column for logical uniqueness.

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

Partition large tables : Split a massive table into partitions (by range, hash, etc.) to keep each index tree smaller, which improves both insert and query performance.