UUID performance in MySQL?

by

At my job, we use UUID as PKs. What I can tell you from experience is DO NOT USE THEM as PKs (SQL Server by the way).

It’s one of those things that when you have less than 1000 records it;s ok, but when you have millions, it’s the worst thing you can do. Why? Because UUID are not sequential, so everytime a new record is inserted MSSQL needs to go look at the correct page to insert the record in, and then insert the record. The really ugly consequence with this is that the pages end up all in different sizes and they end up fragmented, so now we have to do de-fragmentation periodic.

When you use an autoincrement, MSSQL will always go to the last page, and you end up with equally sized pages (in theory) so the performance to select those records is much better (also because the INSERTs will not block the table/page for so long).

However, the big advantage of using UUID as PKs is that if we have clusters of DBs, there will not be conflicts when merging.

I would recommend the following model:
1. PK INT Identity
2. Additional column automatically generated as UUID.

This way, the merge process is possible (UUID would be your REAL key, while the PK would just be something temporary that gives you good performance).

NOTE: That the best solution is to use NEWSEQUENTIALID (like I was saying in the comments), but for legacy app with not much time to refactor (and even worse, not controlling all inserts), it is not possible to do.
But indeed as of 2017, I’d say the best solution here is NEWSEQUENTIALID or doing Guid.Comb with NHibernate.

Hope this helps

More Related Contents:

  1. MyISAM versus InnoDB [closed]
  2. Is there a REAL performance difference between INT and VARCHAR primary keys?
  3. Why is MySQL InnoDB insert so slow?
  4. MYSQL OR vs IN performance
  5. MySQL foreign key constraints, cascade delete
  6. How do I see all foreign keys to a table or column?
  7. How to Perform an UPSERT so that I can use both new and old values in update part
  8. JDBC batch insert performance
  9. TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes
  10. SQL Performance UNION vs OR
  11. Howto: Clean a mysql InnoDB storage engine?
  12. MySQL InnoDB not releasing disk space after deleting data rows from table
  13. MySQL InnoDB foreign key between different databases
  14. What is better in MYSQL count(*) or count(1)?
  15. Bogus foreign key constraint fail
  16. What is MySQL’s default ON DELETE behavior?
  17. How to optimize COUNT(*) performance on InnoDB by using index
  18. How do I repair an InnoDB table?
  19. How to enable INNODB in mysql
  20. How to disable index in innodb
  21. MySQL: Inner join vs Where [duplicate]
  22. Higher cardinality column first in an index when involving a range?
  23. How to regex in a MySQL query
  24. Why doesn’t MySQL’s MyISAM engine support Foreign keys?
  25. MySQL: Many tables or many databases?
  26. sql group by versus distinct
  27. Database corruption with MariaDB : Table doesn’t exist in engine
  28. ALTER TABLE ADD COLUMN takes a long time
  29. “Unknown column in ‘field list'”, but column does exist
  30. Should I always prefer MySQL InnoDB over MyISAM?

Leave a Comment