Why UUIDs Are Bad for MySQL Performance and How to Mitigate Them

UUIDs (Universally Unique Identifiers) are 128‑bit values defined by RFC 4122 that are popular because they can be generated independently on remote devices with a negligible chance of collision.

When used as primary keys in InnoDB, UUIDs cause performance problems: random inserts force page splits, increase I/O operations, inflate storage (each UUID stored six times across primary and secondary indexes), and slow down comparisons because the CPU must compare many bytes instead of a single integer.

Benchmarks show integer comparisons can be up to 28× faster than UUID string comparisons, and even with base64 or binary representations the random nature still leads to I/O bottlenecks once the table exceeds the buffer pool.

Several mitigation approaches are discussed:

Solution 1 – Pseudo‑ordered UUIDs: Generate UUIDs with a time‑based prefix that remains constant for a short interval (e.g., a week), reducing randomness and allowing more rows to stay in memory. Example MySQL function:

DROP FUNCTION IF EXISTS f_new_uuid;

DELIMITER ;;

CREATE DEFINER=`root`@`%` FUNCTION `f_new_uuid`() RETURNS char(36)

BEGIN DECLARE cNewUUID char(36); DECLARE cMd5Val char(32); SET cMd5Val = MD5(CONCAT(RAND(),NOW(6))); SET cNewUUID = CONCAT(LEFT(MD5(CONCAT(YEAR(NOW()),WEEK(NOW()))),4), LEFT(cMd5Val,4), '-', MID(cMd5Val,5,4), '-4', MID(cMd5Val,9,3), '-', MID(cMd5Val,13,4), '-', MID(cMd5Val,17,12)); RETURN cNewUUID; END;;

DELIMITER ;

Solution 2 – Map UUID to Integer: Store UUIDs in a separate mapping table and use an auto‑increment integer as the primary key. Example tables and functions are provided, including a locking mechanism to avoid duplicate inserts.

Alternative identifiers such as UUID_SHORT() or custom 64‑bit IDs based on timestamp and MAC address are also mentioned.

Overall, while pseudo‑ordered UUIDs improve insert rates, the most effective way to eliminate scalability issues is to replace UUID primary keys with integer keys, optionally using a mapping layer to keep UUIDs for external references.