Comments on: Storing UUID Values in MySQL

By: wwwagners

wwwagners — Mon, 27 Apr 2020 19:55:55 +0000

In MySQL version 8 (documentation here: https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html), function ordered_uuid can be replaced by the new built-in function UUID_TO_BIN(UUID(), 1), where **1** is a flag to swap groups 1 and 3. To read stored primary key, we can use the function BIN_TO_UUID(id, 1)

By: Roland J

Roland J — Mon, 11 Feb 2019 10:19:06 +0000

Thanks for this, I have a question: are you storing 1d8eebc58e0a7d7 as bytes (meaning you are cutting off the other half of the UUID) orrdo you rearang the hexdecimal value to store them as BINARY(16).

By: Jhoan Borges

Jhoan Borges — Thu, 03 Jan 2019 15:10:37 +0000

Nice work

By: +B (@bradynapier)

+B (@bradynapier) — Fri, 21 Dec 2018 00:10:36 +0000

Thanks!! Added a simple JS lib for helping with this: https://github.com/odo-network/binary-uuid

By: Neil

Neil — Tue, 30 Oct 2018 10:47:45 +0000

I am producing the same value every time I try to create a uuid.

SELECT ordered_uuid(uuid());

is always returning :

ordered_uuid(uuid())
0x11e8dc2ddb202bef8d1d0800277ad90c

Mysql version is : 5.7.23-0ubuntu0.18.04.1

I have copy and pasted function ordered_uuid I am using here (which I think I copied correctly from this article) :
RETURN UNHEX(CONCAT(SUBSTR(uuid, 15, 4),SUBSTR(uuid, 10, 4),SUBSTR(uuid, 1, 8),SUBSTR(uuid, 20, 4),SUBSTR(uuid, 25)))

What am I missing please?

TIA.
Neil.

By: Ratel

Ratel — Thu, 17 Aug 2017 15:26:58 +0000

Seems You haven’t drawn any conclusions? Why do You compare table with 4 indexes to tables with 3 indexes?

By: Jouni Järvinen

Jouni Järvinen — Mon, 03 Jul 2017 16:30:45 +0000

In reply to Karthik Appigatla. Is it really worth playing with a fire called ID collision ? The VARBINARY to BINARY change is good, no problems there.

By: Rick James

Rick James — Mon, 03 Jul 2017 15:19:09 +0000

In reply to Karthik Appigatla. UNIQUE keys (including PRIMARY KEY) must be checked as you insert the row. Action on non-unique keys are delayed; search for "change buffer" for further discussion. Each row inserted must do a potentially cached read of a block of each UNIQUE key, but does not need such for non-unique keys. However, eventually the read-modify-write for each non-unique index block (that needs changing) will be done. That is, UNIQUE keys are sluggish at INSERT time; non-unique keys may be sluggish later. The ultimate question is "will the next block be cached?" For a small index, it could be cached. Once the index is grows bigger than the buffer_pool, it becomes less and less likely to be cached, hence more reads. Reads are I/O; I/O is the biggest factor in performance. My point is that it is more complicated than simply saying that "uniqueness is the killer". SPATIAL and FULLTEXT are the only indexes other than BTree in InnoDB; you must explicitly ask for them, else you get BTree.

By: Rick James

Rick James — Mon, 03 Jul 2017 15:07:05 +0000

In reply to Martin Streicher. Martin... You are sure they were Type 1? And rearranged? Did you look at HEX(uuid) to see that they were roughly ordered as they were inserted? And the uuid was the PRIMARY KEY?

By: Karthik Appigatla

Karthik Appigatla — Mon, 03 Jul 2017 14:50:04 +0000

Hi Martin,

Can you try removing the unique key constraint on uuid. That is a killer. If the application looks up based on uuid, you can create normal index and also change the column to binary from varbinary

uuid binary(16) DEFAULT NULL,
…
KEY index_fast_zids_on_uuid (uuid)

By default, the index is BTREE.

By: Martin Streicher

Martin Streicher — Thu, 29 Jun 2017 19:28:45 +0000

I have implemented this scheme. Running locally on an iMac on MySQL 5.6 and in a MySQL 5.7 QA environment, the code ran well. Today, when it was deployed to production, the code hammered the MySQL 5.6 server. In fact, it seems like an INSERT into a table with this binary UUID column crushed the machine. The code had to be rolled back because it was killing the client server and the DB server. I need some clues as to what might cause the DB server to suddenly get hammered trying to create UUIDs like this.

I am thinking this is responsible…

uuid varbinary(16) DEFAULT NULL,
…
UNIQUE KEY index_fast_zids_on_uuid (uuid) USING BTREE

Your schema above does not use BTREE… what does it default to?

By: Jouni Järvinen

Jouni Järvinen — Thu, 04 May 2017 12:15:20 +0000

What about splitting the UUID across columns dedicated for each section ? Can’t be totally bad way.

I can imagine splitting each character into its own column would be pretty bad, though.

By: Rick James

Rick James — Mon, 03 Apr 2017 23:27:20 +0000

In reply to haertlmike. As to rearranging -- That is what I do; see my Reply discussing "Type 1". That is also what MySQL 8.0 has functions to do.

By: Rick James

Rick James — Mon, 03 Apr 2017 23:25:22 +0000

In reply to haertlmike. Static for one server, but varies with server. That is the beauty of UUIDs -- they can (in theory) be created by different servers not talking to each other, yet still be unique.

By: haertlmike

haertlmike — Wed, 08 Mar 2017 14:07:49 +0000

I wonder a bit, why the fourth and fifth part of the original UUID are left untouched. Aren’t they pseudo-static, too? Why not move the changing parts over to the right? For example why not convert “AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE” to “DDDDEEEEEEEEEEEECCCCBBBBAAAAAAAA”? If that’s bad for indexing again, maybe someone could explain.

By: Rick James

Rick James — Sat, 12 Nov 2016 01:48:03 +0000

In reply to Martin Streicher. BINARY is better. The length is always 16, so there is no need for VARBINARY. VAR adds 1 or 2 bytes of length to the datatype.

By: Martin Streicher

Martin Streicher — Fri, 11 Nov 2016 14:50:01 +0000

In your example, what would the difference be between a BINARY and VARBINARY column in the table?

By: Rick James

Rick James — Tue, 25 Oct 2016 05:37:51 +0000

In reply to Brad. Thanks, Brad. That was nicely explained. Now to figure out how to add it to the standard.

By: jcmais

jcmais — Tue, 25 Oct 2016 02:02:01 +0000

How COMB GUIDs compare to that?

By: Gary Bisaga

Gary Bisaga — Fri, 23 Sep 2016 14:53:12 +0000

I do not get why data size is bigger in the events_uuid case than events_int. The differences in index size, total size, insert time, I get: but you’re storing 16 bytes/record for events_uuid vs 24 bytes for events_int. Seems like data size would be the same as events_uuid_ordered and less than events_int.