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).

  • Why would you attempt to duplicate the column like that? No point! The VARBINARY(16) version IS stored without dashes. – Max Vernon Dec 30 '14 at 22:35
  • @MaxVernon not my decision to make :( – Matthew Herbst Dec 30 '14 at 22:41
  • 2
    But you'll be the one asked to make it run quickly once it's too late. :-( – Max Vernon Dec 30 '14 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 Dec 31 '14 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 Dec 31 '14 at 12:52
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?

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

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.