Mysql – How to use sequential UUID primary key for range query by date

indexMySQLuuid

I'm using a sequential timebased GUID as my primary key in MySQL 5.6 in a transaction table with over 1 billion rows. The application is written in JAVA and GUID is generated as described here: http://wiki.fasterxml.com/JugHome

The question that I have is whether or not it is possible to range query the data by date without additional indexes? I've seen this question and the answer which states that this is somehow possible: Sequential GUID or bigint for 'huge' database table PK

Existing implementation is using Type 1 UUID, as described here. Then I came across this article, which describes efficient way to save the UUID, by re-organizing its contents.

The question that I have is whether searching by such UUID for a date range (and how it can be done) will be efficient, in other words, is it worth to save storage space by not adding index on the Date column?

Best Answer

The code for such is here, but it only works for "Type 1" UUIDs. It's a matter of shuffling the bits around. And while you are at it, BINARY(16) is more compact than VARCHAR(36) or whatever you have now.

More

By rearranging the bits of a type-1 UUID, you can take advantage of "locality of reference" when using an index on it. For example, if you had News articles. Users tend to look at recent articles and not touch the old ones. New rows will be cached; old ones will drift into oblivion.

A suitably rearranged UUID would provide the equivalent of ORDER BY datetime.

Related Question