Efficiently Store and Query UUIDs in MySQL

Universally Unique Identifiers (UUIDs) are excellent for generating unique keys across distributed systems, but storing them as standard 36-character strings can severely degrade MySQL database performance. This article explains how to optimize MySQL storage and query performance for UUIDs by utilizing the BINARY(16) data type, leveraging built-in MySQL 8.0 functions, and structuring indexes to prevent fragmentation.

The Problem with String Storage

Storing a UUID as a VARCHAR(36) or CHAR(36) is highly inefficient for two primary reasons: 1. Size Overhead: A CHAR(36) column requires 36 bytes of storage. Since UUIDs are mathematically 128-bit numbers, they can be represented in just 16 bytes. 2. Index Fragmentation: Standard UUIDs (specifically UUIDv4) are completely random. When used as a primary key in a B-Tree index (MySQL’s default), random insertions force frequent page splits and heavy disk I/O, drastically slowing down write performance as the table grows.

The Solution: Use `BINARY(16)`

To store UUIDs efficiently, you should always store them as BINARY(16). This reduces the storage footprint by over 50% and improves index lookup speeds.

Database Schema Example

Here is how to define a table that uses BINARY(16) for its primary key:

CREATE TABLE users (
    id BINARY(16) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

Reading and Writing UUIDs in MySQL 8.0+

MySQL 8.0 introduced helper functions to seamlessly convert between human-readable UUID strings and efficient binary storage.

Inserting Data

To insert a UUID, use the UUID_TO_BIN() function to convert the 36-character string into a 16-byte binary format:

INSERT INTO users (id, name, email) 
VALUES (UUID_TO_BIN('63f84df0-b2f7-11ed-afa1-0242ac120002'), 'Alice', 'alice@example.com');

Querying Data

To retrieve the UUID in its readable string format, use the BIN_TO_UUID() function:

SELECT BIN_TO_UUID(id) AS id, name, email 
FROM users 
WHERE id = UUID_TO_BIN('63f84df0-b2f7-11ed-afa1-0242ac120002');

Optimizing Insertion Performance (UUIDv1 vs UUIDv4)

If you are using UUIDv1 (time-based UUIDs), MySQL provides an optimization flag to reorder the time-low and time-high parts of the UUID. This rearranges the bits so that sequential UUIDs are stored sequentially on disk, eliminating random index inserts.

To Store (Rearranged): Pass 1 as the second argument to UUID_TO_BIN().
To Retrieve (Rearranged): Pass 1 as the second argument to BIN_TO_UUID().

-- Inserting with time-shuffling optimized for sequential indexes
INSERT INTO users (id, name, email) 
VALUES (UUID_TO_BIN('63f84df0-b2f7-11ed-afa1-0242ac120002', 1), 'Bob', 'bob@example.com');

-- Querying the shuffled UUID
SELECT BIN_TO_UUID(id, 1) AS id FROM users;

Note: This optimization flag only works for time-based UUIDs (v1) and has no effect on random UUIDs (v4).

Modern Alternative: UUIDv7

If your application allows it, migrate to UUIDv7. UUIDv7 natively combines a Unix timestamp with random data, ensuring that the identifiers are naturally ordered chronologically.

Because UUIDv7 is naturally sequential, you can store it directly in a BINARY(16) column using standard UUID_TO_BIN() without any shuffling flags, achieving both distributed uniqueness and excellent database write performance.