Making UUIDs More Performant in MySQL

MySQL does not have a native UUID type, so strings are often used instead, which come with large storage and performance costs.

UUIDs can take a lot of storage

Storing 36 characters, in an InnoDB table with a default utf8mb4 character set, is a lot of storage space:

• CHAR(36) is always 36x4 = 144 bytes
• VARCHAR(36) would be 36x4 + 1 = 145 bytes
• VARCHAR(255) would be 36x4 + 2 = 146 bytes

But storage is cheap, so why do we care?

Let's say we want to index our string UUID column, because it's probably an ID we'll use for lookups or joins. A larger column size means a larger index size, which means more difficulty fitting the index in working memory, which means slower lookups.

It's even worse if we use a string UUID for our primary key (see: "Why You Should Use UUIDs for Your Primary Keys"). Every other secondary index on InnoDB tables has to store the primary key with it, which means larger indexes for everything, again impacting performance.

Why You Should Use UUIDs for Your Primary Keys

Jan 30, 2020 · 4 min read

Universally unique identifiers (UUIDs) are 128-bit (16-byte) numbers that are designed to be globally unique, and as a result they make for great primary keys.

UUIDs are supposed to be only 16 bytes, can't we do better?

Binary UUID columns

If we strip the hyphens and convert the remaining 32 characters to BINARY, we can store UUIDs in only 16 bytes.

The BINARY type isn't affected by the table character set (such as utf8mb4), it uses the binary character set and collation. Even better, comparison and sorting will use the numeric value of the column, which will perform much better than a string.

All versions of MySQL can use UNHEX() and REPLACE() to do this:

SELECT UNHEX(REPLACE("f7c26694-aac6-4333-91c9-72c4441430e2", "-", "")) AS unhex_replace;

+------------------------------------+
| unhex_replace                      |
+------------------------------------+
| 0xF7C26694AAC6433391C972C4441430E2 |
+------------------------------------+

MySQL v8.0.0 (2016) added the function UUID_TO_BIN() which does the same thing:

SELECT uuid_to_bin("f7c26694-aac6-4333-91c9-72c4441430e2") AS uuid_bin;

+------------------------------------+
| uuid_bin                           |
+------------------------------------+
| 0xF7C26694AAC6433391C972C4441430E2 |
+------------------------------------+

And it's easy to perform the reverse here:

SELECT lower(insert(insert(insert(insert(
         hex(0xF7C26694AAC6433391C972C4441430E2)
         , 9, 0, '-'), 14, 0, '-'), 19, 0, '-'), 24, 0, '-')
       ) AS lower_insert_hex
     , bin_to_uuid(0xF7C26694AAC6433391C972C4441430E2) AS bin_uuid;

+--------------------------------------+--------------------------------------+
| lower_insert_hex                     | bin_uuid                             |
+--------------------------------------+--------------------------------------+
| f7c26694-aac6-4333-91c9-72c4441430e2 | f7c26694-aac6-4333-91c9-72c4441430e2 |
+--------------------------------------+--------------------------------------+

Putting it all together

Here's an example where a table users uses binary UUIDs for its primary key:

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

INSERT INTO users (id, name)
VALUES (uuid_to_bin(uuid()), 'Larry Page')
     , (uuid_to_bin(uuid()), 'Sergey Brin');

SELECT bin_to_uuid(id) AS id
     , name
FROM users;

If everything went well, at the end you should have output similar to:

+--------------------------------------+-------------+
| id                                   | name        |
+--------------------------------------+-------------+
| cf2dc9ef-26b0-11eb-a894-0242ac110003 | Larry Page  |
| cf2dcd93-26b0-11eb-a894-0242ac110003 | Sergey Brin |
+--------------------------------------+-------------+

See "Generating v4 UUIDs in MySQL" for a way to generate potentially more desirable UUIDs.

Generating v4 UUIDs in MySQL

Nov 13, 2020 · 3 min read

MySQL's UUID() function generates v1 UUIDs, which have a time component that make them unevenly distributed over short periods of time. We can define our own function to generate v4 UUIDs, which are random and therefore more evenly distributed.