For my part, in a database I designed that used a SHA-256 hash for a unique identifier that was then a foreign key from many other tables, I stored that as an integer and not as a hex string. If UUIDs are similarly numbers fundamentally, they possibly could do likewise. I agree with Mark's comment re binary. -- Darren Duncan
On 2015-12-12 1:12 PM, Mark Hamburg wrote: > Though to the extent that speed is proportional to data size, it would be > good to use something other than hexadecimal to store UUIDs. Binary blobs > would be the most compact, but ASCII85 encoding would work well if you need > strings. > > Also, if these values are reused repeatedly as I suspect projectID and > groupID might be, then it may be useful to intern them into a table and use > integer keys. We got a noticeable performance improvement when I made that > sort of change recently in our project. (I also implemented a > string-to-integer-to-string cache that sits ahead of hitting the database.) > > Mark > >> On Dec 12, 2015, at 1:07 PM, Darren Duncan <darren at darrenduncan.net> >> wrote: >> >> On 2015-12-12 12:56 PM, Cecil Westerhof wrote: >>>>> By the way: I am thinking about using UUID for projectID and groupID, >>>> but I >>>>> heard somewhere that it was a bad idea to use UUID for an indexed field. >>>> Is >>>>> this true?? >>>> >>>> I think you might have misunderstood. UUID is almost always a good >>>> field to index. >>> >>> ?I was told because of the nature of random UUID (what I will be using) it >>> is hard to create a good index. The article said that data that is really >>> random cannot be indexed very efficient. But I do not have to worry about >>> it then. :-) It has been a few years back, so it is also possible that the >>> problem is solved nowadays. >> >> Cecil, it isn't about randomness, it is about uniqueness or cardinality. >> The fields that index the best are ones with many different values, in >> particular key fields where every record has a different value from every >> other record. UUIDs have this quality in spades. It is even more important >> to index such fields if you will either be searching/filtering on them or if >> they are the parent in a foreign key constraint. This has always been the >> case, its not a new thing. -- Darren Duncan
