After seeing that several blogs discuss storage of UUID values into MySQL, and that this topic is recurrent on forums, I thought I would compile some sensible ideas I have seen, and also add a couple new ones.

See also follow up post called Mysql 8.0: UUID support , which explains the 8.0 solution.

Different techniques

Say we have a table of users, and each user has a UUID. MySQL has a UUID() function, which makes MySQL generate a UUID value and return it in a human-readable form, of type VARCHAR(36). Let’s try it on MySQL 5.7.8:

So the first idea would be to simply do this:

But this human-readable form of UUID isn’t compact; let’s observe that:

• the fours dashes are superfluous
• each pair of characters is actually a hexadecimal number in the range 00-FF; that makes 16 numbers in total (above: 0xAA, 0xB5, etc), each of them can be stored in a byte.

So we can use REPLACE() to remove dashes, and UNHEX() to transform each
two-char pair into a byte:

This binary form uses 16 bytes which is much smaller than VARCHAR(36) used by the human-readable form (which I’ll call the “text” form now). If the UUID has to be a primary  key, the gain is even greater, as in InnoDB the primary key value is copied into all secondary index values.

BINARY(16) is… well… just binary! No character set, no collation, just sixteen bytes. Perfect for our need.

Perhaps the text form is still necessary in some application, so let’s keep it as an additional column in the table; but, to minimize disk occupation, let’s make the text form a virtual generated column (that’s a new feature of MySQL 5.7, described in the documentation of CREATE TABLE). This column will be calculated by a formula of the binary-form column: we convert the binary form back to hexadecimal digits and insert dashes.

I didn’t include id_bin in the SELECT because it would come out as cryptic characters (of ASCII code 0xC2, 0x77, etc: generally not in the human-readable range of characters). There’s no reason we should need to look at the content of id_bin; but, if you do, you can visualize its hexadecimal codes by using HEX(id_bin).

Note that id_text is declared VIRTUAL, so takes no space in the table on disk.

Another benefit of making id_text a generated column, is that it eliminates any risk of inconsistency between the two columns. Indeed, if id_text were a plain column, one could do

without updating id_text, accidentally. But as a generated column, id_text is never updatable directly: instead, it is automatically updated when one updates id_bin. In other words, information is in only one place (id_bin) and the database guarantees consistency.

Then, what about queries? for example, we could want to find a user by UUID:

Should the WHERE clause specify the binary or the text form? It depends:

• If we create an index over the binary form:
  
  alter table users add unique(id_bin);

  1	alter table users add unique(id_bin);

  
  then, for this index to be used, WHERE should specify the binary form:
  
  WHERE id_bin = binary_form_of_XYZ

  1	WHERE id_bin = binary_form_of_XYZ

• If instead we create an index over the text form:
  
  alter table users add unique(id_text);

  1	alter table users add unique(id_text);

  
  then, WHERE should specify the text form:
  
  WHERE id_text = text_form_of_XYZ

  1	WHERE id_text = text_form_of_XYZ

Even though id_text is a virtual column, it is possible, as above, to add an index on it (in that case, the index does occupy disk space). That is a new feature introduced in MySQL 5.7.8.

However, if we have a choice, as the binary form is shorter, it looks more logical to index it, not the text form – the index will be smaller and thus faster to traverse, faster to backup…

Finally, there is the question of how to smartly re-arrange bytes in the binary form.

To understand that, we need to learn more about UUIDs. They exist in several versions, and different sources can generate different versions. MySQL’s UUID() uses version 1, which implies, as explained in paragraph 4.1.2 of the RFC, that the three leftmost dash-separated groups are a 8-byte timestamp: leftmost group is the low four bytes of the timestamp; second group is the middle two bytes, third group is the high (most significant) two bytes of the timestamp. Thus the leftmost group varies the fastest (10 times per microsecond). We can verify that:

You can see how the 8 leftmost characters changed while the others did not.

So, in a sequence of UUIDs continuously generated by a single machine, all UUIDs have different first bytes. Inserting this sequence into an indexed column (in binary or text  form) will thus modify a different index page each time, preventing in-memory caching. So it makes sense to re-arrange the UUID, making the rapidly-changing parts go last, before we store into id_bin. Again, note that this idea applies only to UUIDs of version 1.

This idea isn’t mine; I saw it first in this blog and that one.

Below, the binary form is re-arranged, by changing time-low/time-mid/time-high to time-high/time-mid/time-low.

I used a user variable above (@u), because each SUBSTR() invokation needs to reference the UUID value, but I cannot afford to write UUID() four times: it would generate a new UUID each time! So I call UUID() once, remove dashes, convert it to binary, store it in a variable, and do the four SUBSTR on it.

However, I still want the text form to be in “non-rearranged” order, because… perhaps this  text form will be used for some error logging, debugging? If humans are to read it, I don’t  want to confuse them by using a rearranged order.
Adding id_text can be done in CREATE TABLE, or as a follow-up ALTER TABLE:

which takes the parts out of the binary form (with SUBSTR), puts them in the “normal” position (CONCAT), converts bytes to hexadecimal digits (HEX) and inserts dashes. Right, it’s a complex expression, but you type it only once, when creating the generated column.

Now look at the data:

Columns are:

1. the cryptic characters (rearranged binary UUID)
2. the corresponding hex codes for the first column
3. the name
4. the non-rearranged text UUID. See how the leftmost 3A059CCB of this text form, the rapidly-changing part, is in the middle of the binary form (column 2), so the binary form will give more efficient indexing.

Conclusion

This mere blog post has recapitulated some common good practices for storage of UUIDs, on top of making a use case for indexed virtual columns. And, it also reminded us of the power of simple string functions 🙂

Creative thinking

If we had bit-wise operations on the BINARY type, as envisioned in Morgan’s blog, then they could be an alternative to CONCAT/SUBSTR. For example, this expression taken from above:

would become  – @u being VARBINARY(16) :

The bit masks could also be generated with functions; for example x’000000000000FFFF0000000000000000′ is the same thing as RPAD(LPAD(x’FFFF’,8,x’00’),16,x’00’)).

Between CONCAT/SUBSTR and &|<<, which one would be best… is probably a matter of taste.

Will we have bit-wise operations on BINARY one day? I am not a decider, but I hope we will have them or some equivalent. They would allow operations on “bigger than 64 bits”… stay tuned!

That’s it for now. THANK YOU for using MySQL!