Why Using UUIDs as MySQL Primary Keys Can Hurt Performance—and How to Fix It

UUID Versions

There are five official UUID versions (v1‑v5) and three proposed versions (v6‑v8). Each version differs in how the 128‑bit value is generated.

UUIDv1

Time‑based UUID. It uses a Gregorian calendar start date (October 15, 1582) as the epoch and increments a 100‑nanosecond counter. The fields time_low, time_mid and time_hi store the timestamp, while the final node field holds the generating system's MAC address.

UUIDv2

Similar to v1 but replaces the low_time portion with a POSIX UID. This makes the UUID traceable to a user account, but the mutable low_time increases collision risk, so v2 is rarely used.

UUIDv3 and UUIDv5

Both generate deterministic UUIDs from a namespace UUID and a name. The only difference is the hash algorithm: v3 uses MD5, while v5 uses SHA‑1.

UUIDv4

Random‑based UUID. All 128 bits are generated randomly except the version field (the first character of the third segment) which is set to 4.

UUIDv6

Almost identical to v1 but reorders the timestamp bits so the most significant part appears first, improving sortability.

UUIDv7

Time‑based like v1/v6 but uses the modern Unix epoch (milliseconds since 1970) and replaces the node field with random data, making the source harder to trace.

UUIDv8

Latest version that allows custom, vendor‑specific fields while still complying with the RFC; the version number must occupy the first character of the third segment.

UUID and MySQL

Using UUIDs as primary keys guarantees global uniqueness across distributed systems, but compared with auto‑increment integers they introduce several trade‑offs.

Insert performance

MySQL indexes primary keys using B+‑trees. When a new row is inserted, the tree must be updated. Random UUID values cause frequent page splits, slowing inserts, especially under high write load.

Storage utilization

Binary UUID (BINARY(16)) occupies 128 bits – four times the space of a 32‑bit integer.

String UUID (CHAR(36)) occupies 288 bits – about nine times the space of a 32‑bit integer.

Secondary indexes also store the primary key, so using UUIDs inflates index size and reduces page fill factor. Random UUIDs can drop the B+‑tree fill factor from ~94 % to ~50 %.

Best Practices for UUID Primary Keys in MySQL

Store UUIDs in binary form

Convert the 36‑character string to native binary using BINARY(16). This reduces storage per value to 16 bytes.

CREATE TABLE uuids(
  UUIDAsChar CHAR(36) NOT NULL,
  UUIDAsBinary BINARY(16) NOT NULL
);

INSERT INTO uuids SET
  UUIDAsChar = 'd211ca18-d389-11ee-a506-0242ac120002',
  UUIDAsBinary = UUID_TO_BIN('d211ca18-d389-11ee-a506-0242ac120002');

SELECT * FROM uuids;

Use ordered UUID variants

Choose UUID versions that preserve temporal order (e.g., v6 or v7). Ordered values keep inserts near the end of the B+‑tree, reducing page splits.

Leverage MySQL's built‑in UUID functions

MySQL can generate version‑1 UUIDs with UUID(). The helper UUID_TO_BIN(uuid, swap_flag) converts a UUID string to binary and, when swap_flag = 1, reorders the bytes to improve sortability.

SET @uuidvar = 'd211ca18-d389-11ee-a506-0242ac120002';
-- Without swap flag
SELECT HEX(UUID_TO_BIN(@uuidvar)) AS UUIDAsHex;
-- With swap flag (ordered)
SELECT HEX(UUID_TO_BIN(@uuidvar, 1)) AS UUIDAsHex;

Consider alternative ID schemes

If UUID drawbacks are unacceptable, other globally unique identifiers exist, such as Snowflake IDs, ULIDs, or NanoID, which can offer better performance or shorter representations.

# Snowflake ID
7167350074945572864

# ULID
01HQF2QXSW5EFKRC2YYCEXZK0N

# NanoID
kw2c0khavhql

By selecting the appropriate UUID version, storing it efficiently, and optionally switching to an alternative ID type, you can retain the benefits of global uniqueness while mitigating the performance and storage penalties in MySQL.