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?

Improve this question
1
  • 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, 2016 at 16:11

1 Answer 1

Reset to default
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.

Improve this answer
2
  • 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, 2016 at 7:47
  • I edited my answer to address your question.
    – Rick James
    Apr 16, 2016 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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