1

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?

  • You may want to include some details on how the GUIDs are generated in your question, instead of asking folks to read the IETF specification. To answer your question, yes, it is possible if you can somehow derive date values from your GUIDs. – mustaccio Apr 14 '16 at 16:11
1

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.

  • Yes, existing implementation is using Type 1 UUID, as described here: famkruithof.net/guid-uuid-timebased.html Then I came accross this article, which describes efficient way to save the UUID, by re-organizing its contents: percona.com/blog/2014/12/19/store-uuid-optimized-way 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? – Akaki Meladze Apr 16 '16 at 7:47
  • I edited my answer to address your question. – Rick James Apr 16 '16 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.