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 > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
