UUID performance in MySQL?

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

Leave a Comment