1

I have a MySQL InnoDB table with tens of millions of rows. Each row has uuid column. They are stored in the standard VARBINARY(16). I was recently asked to add a column to this table of VARCHAR(32) that stores the non-dashed UUID hex.

While doing this, I discovered that about half our UUIDs are v1, and the other half are v4. This means that for the first half, since they were all generated on a single machine, the randomness is a bit lacking (only the left-bytes timestamps are different). On the other hand, the newer half are basically completely random.

Is it worth putting an index on this column? If so, I'm struggling to decide how large that index should be (or maybe even what type of index).

Improve this question
6
  • Why would you attempt to duplicate the column like that? No point! The VARBINARY(16) version IS stored without dashes.
    – Hannah Vernon ♦
    Commented Dec 30, 2014 at 22:35
  • @MaxVernon not my decision to make :(
    – Matthew Herbst
    Commented Dec 30, 2014 at 22:41
  • 2
    But you'll be the one asked to make it run quickly once it's too late. :-(
    – Hannah Vernon ♦
    Commented Dec 30, 2014 at 22:45
  • 2
    "I was under the impression that MySQL doesn't inline functions" not at all true if done correctly: WHERE c1 = UNHEX('expr') will resolve the result of UNHEX() to a constant and use an index on c1 in a perfectly optimal way ... but WHERE hex(c1) = 'expr' will scan every row of the table, evaluating HEX(c1) on each row to see if it matches 'expr'. The general rule is that if you do not use a column as the argument to a function, the optimizer will get it right.
    – Michael - sqlbot
    Commented Dec 31, 2014 at 12:43
  • 2
    ...so, no, given that an index on the binary column is half the size of an index on the hex column, it should not be faster, or otherwise better, to add this new column and index, particularly when you consider the cumulative time each write query will need, to update two indexes instead of one.
    – Michael - sqlbot
    Commented Dec 31, 2014 at 12:52

1 Answer 1

Reset to default
2

Both v1 and v4 (or the mix) UUIDs will build a good B+ tree index for faster SELECTs. From normalization point of view duplicating the column is a bad idea. But if we put this aside, and any SELECTs will benefit from the new index, and impact on writes is acceptable - why not?

Improve this answer
2
  • So you mean just a standard index, nothing fancy about the size or anything?
    – Matthew Herbst
    Commented Dec 30, 2014 at 23:37
  • 1
    yep, I don't see any reason for prefixed index.
    – akuzminsky
    Commented Dec 30, 2014 at 23:42

Your Answer

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

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