Storing UUID and Generated Columns

A lot of things have been said about UUID, and storing UUID in an optimized way. Now that we have generated columns, we can store the decomposed information inside the UUID and merge it again with generated columns. This blog post demonstrates this process.

First, I used a simple table with one char field that I called uuid_char to establish a base case. I used this table with and without a primary key:

I performed the tests on a local VM over MySQL 5.7.17 for 30 seconds, with only two threads, because I wanted to just compare the executions:

One pair of executions is with the UUID generated by sysbench, which simulates the UUID that comes from the app:

An alternative execution is for when the UUID is generated by the MySQL function uuid():

Below we can see the results: 

The inserts are faster without a PK (but only by 5%), and using the uuid() function doesn’t impact performance.

Now, let’s see the alternative method, which is decomposing the UUID. It has four main information sets:

• Timestamp: this is a number with seven decimals.
• MAC: the MAC address of the device that creates the UUID
• Unique value: this value avoids duplicate cases scenarios
• UUID version: this will always be “1”, as we are going to use version 1. If you are going to use another version, you will need to review the functions that I used.

The structure of the table that we’ll use is:

To understand how a UUID is unwrapped, I used this store procedure (which receives a UUID and inserts it into the table):

Explanation:

• @hex_timestamp is a temporary variable that collects the timestamp in hexadecimal format from the different sections of the UUID
• @timestamp transforms the hexadecimal timestamp to a decimal number
• @mac pulls the last number in the UUID (a MAC) so we can store it in as a bigint
• @temp_uniq is a value to conserve the uniqueness, which is why we store it as binary and it is at the end of the Primary Key

If I wanted to get the UUID again, I can use these two generated columns:

We performed tests over five scenarios:

• Without the generated columns, the insert used data generated dynamically
• Same as before, but we added a char field that stores the UUID
• With the char field, and adding the generated column
• We used the store procedure detailed before to insert the data into the table
• We also tested the performance using triggers

The difference between the Base and the previous table structure with Primary Keys is very small. So, the new basic structure has no impact on performance.

We see that Base and +Char Field have the same performance. So leaving a char field has no performance impact (it just uses more disk space).

Using generated columns impact performance. This is expected, as the columns are generated to validate the type before the row is inserted.

Finally, the use of triggers and store procedure has the same impact in performance.

These are the three structures to the tables:

And this is the trigger:

Conclusions

Decomposing the UUID is an alternative to storing them in order, but it won’t speed up inserts. It is simpler to execute queries over a range of dates, and look at the row for a particular device, as you will be able to use the MAC (it is recommended to add an index for it). Generated columns give you the possibility to build the UUID back in just one string.