What is the best identifier for a UserID? 64 bit integers, UUID V5, or 64 character SHA256 UID?

I wish to future-proof my application. What is the best type of identifier to use?

I am considering:

• 64 bit Integer

• UUID

• 64 character SHA256

I am not developing a distributed system, so is using a UUID necessary?

Also, How good are INTEGER joins vs VARCHAR joins?

Finally, is storing the username (unique) and message data in the same MySQL table better than storing the user id?

Use integers. This is the simplest and most efficient way (especially for InnoDB).

That is

• don't use hashes or UUIDs or varchars in place of int

• don't use the natural key when you have foreign keys to it

Reasons?

• varchar comparisons require collation + case tests

• index architecture in InnoDB favours integer fields (ideal clustered index is narrow, numeric, monotonically increasing)

• hashes and GUIDs and varchar cause fragmentation

• wider data + indexes rows when not using integer

• 64 characters requires 64/66 bytes to store. 64 bit in requires 8 bytes

Other observations:

• Do you need 64 bit numbers?

StackExchange Database Administrators Q#16040, answer score: 8