Re: PRIMARY KEYs: UUID / GUID vs BIGINT (timestamp+random)

[Rick James]

You have pretty summarized the pros and cons of the various alternatives.

BIGINT is 64 bits, you may as well use time + 32 bits.

UUIDs -- I prefer to put them in BINARY(36) as hex with dashes. It's adequately readable. It avoids the collation overhead (especially if you are defaulted to utf8).

UUIDs hit the fan when the table/index gets bigger than can be cached. Until then, it is not too bad. Finding any item in a BTree is just as cheap as finding any other item. It's when you have to go to disk that performance suffers, big time. The length and datatype of a key is much less critical to performance than the cacheability of the records.

Consider having the server_id as part of the unique number -- this lets you completely be sure it is unique because you can double check on the current server. The server_id might be an 8-bit value that is hand-assigned when you start a new server.

Maybe 32-bit time + 8-bit server_id + 24-bit sequence. Messy to build, but guaranteed unique.

How random are your accesses? With UUID, very random. With something starting with time, 'recent' rows are clumped together. Is this useful for your application? Or will you be running all over the table anyway?