2

It's well known how the performance of random UUIDs as PKs in an InnoDB table degrades terribly as it increases in size. Would an UNIQUE index for a non-PK UUID column have the same impact?

UUIDs are Version 4, random, stored as binary(16).

3

I guess you are referring to the InnoDB clustered index performance. If I'm not mistaken, the PK is chosen as the clustered index by default, so any other, secondary index containing the UUID column should not suffer from the same problem.

Alternatively, I think you can create the table without declaring the PK at first, in which case MySQL will create an automatic clustered index based on row IDs. You can then alter the table and add the necessary primary key constraint.

3
  • To further clarify why performance degrades when using UUID as PKs: insertion in random order will produce more page splits, which will slow down insert performance. blog.jcole.us/2014/10/02/… Also, secondary keys may become larger (as they contain the PK), limiting contents that fit on the buffer pool (memory). slideshare.net/mysqlops/… More on the topic: percona.com/blog/2007/03/13/to-uuid-or-not-to-uuid
    – jynus
    Oct 16 '14 at 20:37
  • @jynus but the first blog post implies any random insertion would have the same impact, PK or not. Oct 16 '14 at 21:25
  • @PedroWerneck Hence the preference for SERIAL Primary KEYS (integers auto_increment), where new insert will be monotonically at the end. Other random values will not change the way the table is clustered (always on the PK on InnoDB). Random seconday keys may have an impact, but it is minimized if they are not unique with the change buffer, and less impacting for unique keys than for the whole row.
    – jynus
    Oct 16 '14 at 21:37

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.