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).
Best Answer
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?