Indexing unique values (UUIDs) in MySQL 5.7 EFFECTIVELY

When the load starts to grow, any database has to make a trade-off between insert speed and read speed. Today a short article on how to get the highest possible insertion and reading speed with UUIDS

Jan 16, 2024

assorted brown and red beads lot — Photo by Eric Prouzet on Unsplash

At some point in the project, you will encounter the need to provide users with unique identifiers by which they can find data (e.g., application number). These identifiers should be unique, not reveal the real number of application numbers, quickly entered into the database and used for quick information retrieval.

Getting a unique identifier is easy enough - it's a UUID v4. Most languages, like PHP, have libraries for generating UUID v4.

Examples of UUIDs

undefined — I love all that green text :D

If you index a v4 UUID in the database, you can select a row by that UUID very quickly. But as soon as the flow of inserts and queries with these v4 UUIDs becomes large, MySQL starts to crash or run slowly (even with a large number of inserts). The reason is that v4 UUIDs are random values, they are literally random. Regardless of the data type for a field, indexing random values is very, very bad for MySQL. Once again: indexing random values is a terrible idea.

You can understand it in more detail by watching this video:

So, how to get unique identifiers indexed without overloading MySQL? We, on the other hand, want to get unique values, insert them quickly, and search through them quickly. Is there really no solution? There is!

The solution

UUID v7/ULIDS (we preferred UUIDs v7) was literally created to solve the problem of slow indexing in MySQL...I guess. But it definitely fits.

What's good about UUID v7? They are very similar to UUIDs 4, but there are a couple of differences: UUIDs v7 are unique (not random like v4 UUIDs, but unique), they increase monotonically, which means they are sortable and can be indexed by MySQL without problems.

Ok, how to work with this within MySQL 5.7?

Create a table with the following fields

uuid_text is a generated virtual field. It makes the UUID human readable and also reduces the chance of getting non-consistent data

CREATE TABLE `table_name` (
  `uuid_binary` binary(16) NOT NULL,
  `uuid_text` varchar(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`uuid_binary`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,
  PRIMARY KEY (`uuid_binary`)
)

Inserting data

INSERT INTO table_name (uuid_binary) VALUES
    (UNHEX(REPLACE("018d0ef0-63f4-7299-ad71-a519b6879e0f", "-","")))

Selecting data

select LOWER(CONCAT(
         SUBSTR(HEX(uuid_binary), 1, 8), '-',
         SUBSTR(HEX(uuid_binary), 9, 4), '-',
         SUBSTR(HEX(uuid_binary), 13, 4), '-',
         SUBSTR(HEX(uuid_binary), 17, 4), '-',
         SUBSTR(HEX(uuid_binary), 21)
     )) from table_name 
WHERE UUID = (UNHEX(REPLACE('018d0ef0-63f4-7299-ad71-a519b6879e0f', '-', '')))

Or you can do this query. It'll do the same thing

select uuid_text from table_name 
WHERE UUID = (UNHEX(REPLACE('018d0ef0-63f4-7299-ad71-a519b6879e0f', '-', '')))

To generate a v7 UUID in PHP, you will need this library
```
composer require ramsey/uuid:4.7.4
```
To get the v7 UUID, execute the following code in PHP
```
\Ramsey\Uuid\Uuid::uuid7()
```

That's it!

Now you can easily add a large number of unique identifiers in a small amount of time without putting a crazy load on MySQL. At the same time, thanks to the index you will always be able to quickly sample these values

Keep the following rule in mind for indexed field values:

the value must be sortable
value Must not be random (but can be unique)
the value better be monotonically increasing

Helpful articles:

Subscribe, like, comment!

Stantechi

Discussion about this post