Is Mysql UUID_SHORT() comparable to UUID()

Question

A quick question or opinion if you will.

I need to generate some UUID's for a database table.

Auto incrementing keys won't cut it as I need the key to be unique across databases and systems also. UUID works fine however its output is too long for some of the systems that the rows will be exported to. UUID_SHORT() does the job fine and I have read MYSQL's conditions on guaranteeing its uniqueness.

ButI just want to double check that if I use UUID_SHORT() to generate UUID's for rows from time to time that they will indeed be unique in time and space as with UUID().

Cheers.

Answer 1

uuid_short() produces a bitwise conglomeration of the server ID, a fairly static time component, and a sequentially increasing 24 bit integer. These bits are stuffed into an 8 byte integer. The time component is based on the server's boot time.

uuid() produces hex string that represents a 16 byte version1 UUID. Version 1 UUIDs are a bitwise conglomeration of the server ID, the current timestamp, a few bytes that come into play if you generation IDs at hyperspeed, and a few utility bits.

To answer your question: does uuid_short provide time and space uniqueness that rivals uuid? The answer is no. Case in point, the server ID in a uuid_short is only one byte. So if you have 256 or more servers, at least a few of them will have the same node id, which means you lose space uniqueness. For comparison, the server ID in version 1 UUID is 6 bytes long, effectively killing the chance of duplicates for all but the largest of corporate server farms :)

A better question is whether uuid_short is good enough. You could see ID collisions if you :

1. Generate more than 16 million IDS from the same server in little time. ***
2. Boot servers with the same server ID all at exactly the same time, and share data between them.
3. Fiddle with the system clock and then reboot your server.

The second issue seems unlikely for most people, but the first one is worth investigating before you commit to making uuid_short the basis of your keys.

*** Based on the mysql docs for uuid_short, it seems like you would see collisions if you generated more than 16 million IDs during the uptime of a single server. But that would be silly. The mysql docs go on to say that you are fine as long as you don't generate 16 million IDs per second. That implies that they must bump some of the bits in timestamp if you exhaust the 16 million sequential IDs. I have not tested this.