What are the various options and their tradeoffs for storing a UUID in a MYSQL table?

Question

I'm planning on using client provided UUID's as the primary key in several tables in a MySQL Database.

I've come across various mechanisms for storing UUID's in a MySQL database but nothing that compares them against each other. These include storage as:

• BINARY(16)
• CHAR(16)
• CHAR(36)
• VARCHAR(36)
• 2 x BIGINT

Are there any better options, how do the options compare against each other in terms of:

• storage size?
• query overhead? (index issues, joins etc.)
• ease of inserting and updating values from client code? (typically Java via JPA)

Are there any differences based on which version of MySQL your running, or the storage engine? We're currently running 5.1 and were planning on using InnoDB. I'd welcome any comments based on practical experience of trying to use UUIDs. Thanks.

Answer 1

I would go with storing it in a Binary(16) column, if you are indeed set on using UUIDs at all. something like 2x bigint would be quite cumbersome to manage. Also, i've heard of people reversing them because the start of the UUIDs on the same machine tend to be the same at the beginning, and the different parts are at the end, so if you reverse them, your indexes will be more efficient.

Of course, my instinct says that you should be using auto increment integers unless you have a really good reason for using the UUID. One good reason is generating unique keys accross different databases. The other option is that you plan to have more records than an INT can store. Although not many applications really need things like this. THere is not only a lot of efficiency lost when not using integers for your keys, and it's also harder to work with them. they are too long to type in, and passing them around in your URLs make the URLs really long. So, go with the UUID if you need it, but try to stay away.

Answer 2

I have used UUIDs for smart client online/offline storage and data synchronization and for databases that I knew would have to be merged at some point. I have always used char(36) or char(32)(no dashes). You get a slight performance gain over varchar and almost all databases support char. I have never tried binary or bigint. One thing to be aware of, is that char will pad with spaces if you do not use 36 or 32 characters. Point being, don't write a unit test that sets the ID of an object to "test" and then try to find it in the database. ;)