The differences between INT and UUID in MySQL

by

UUID returns a universal unique identifier (hopefuly also unique if imported to another DB as well).

To quote from MySQL doc (emphasis mine):

A UUID is designed as a number that is globally unique in space and
time. Two calls to UUID() are expected to generate two different
values, even if these calls are performed on two separate computers
that are not connected to each other.

On the other hand a simply INT primary id key (e.g. AUTO_INCREMENT) will return a unique integer for the specific DB and DB table, but which is not universally unique (so if imported to another DB chances are there will be primary key conflicts).

In terms of performance, there shouldn’t be any noticeable difference using auto-increment over UUID. Most posts (including some by the authors of this site), state as such. Of course UUID may take a little more time (and space), but this is not a performance bottleneck for most (if not all) cases. Having a column as Primary Key should make both choices equal wrt to performance. See references below:

  1. To UUID or not to UUID?
  2. Myths, GUID vs Autoincrement
  3. Performance: UUID vs auto-increment in cakephp-mysql
  4. UUID performance in MySQL?
  5. Primary Keys: IDs versus GUIDs (coding horror)

(UUID vs auto-increment performance results, adapted from Myths, GUID vs Autoincrement)

enter image description here

UUID pros / cons (adapted from Primary Keys: IDs versus GUIDs)

GUID Pros

  • Unique across every table, every database, every server
  • Allows easy merging of records from different databases
  • Allows easy distribution of databases across multiple servers
  • You can generate IDs anywhere, instead of having to roundtrip to the database
  • Most replication scenarios require GUID columns anyway

GUID Cons

  • It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if
    you’re not careful
  • Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
  • The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of
    clustered indexes.

Note

I would read carefully the mentioned references and decide whether to use UUID or not depending on my use case. That said, in many cases UUIDs would be indeed preferable. For example one can generate UUIDs without using/accessing the database at all, or even use UUIDs which have been pre-computed and/or stored somewhere else. Plus you can easily generalise/update your database schema and/or clustering scheme without having to worry about IDs breaking and causing conflicts.

In terms of possible collisions, for example using v4 UUIDS (random), the probability to find a duplicate within 103 trillion version-4 UUIDs is one in a billion.

Related Contents:

  1. Is there a REAL performance difference between INT and VARCHAR primary keys?
  2. Composite Primary Key performance drawback in MySQL
  3. MyISAM versus InnoDB [closed]
  4. MySQL vs MongoDB 1000 reads
  5. difference between primary key and unique key
  6. Difference between Key, Primary Key, Unique Key and Index in MySQL
  7. How to properly create composite primary keys – MYSQL
  8. ALTER TABLE to add a composite primary key
  9. Remove Primary Key in MySQL
  10. Why does MYSQL higher LIMIT offset slow the query down?
  11. MySQL: Fastest way to count number of rows
  12. Updating MySQL primary key
  13. MySQL “IN” operator performance on (large?) number of values
  14. How can i optimize MySQL’s ORDER BY RAND() function?
  15. ALTER TABLE ADD COLUMN takes a long time
  16. Best MySQL cache settings for 8gb RAM dedicated MySQL server using only InnoDB (5gb database)
  17. How fast is MySQL replication?
  18. How do I profile MySQL?
  19. How to improve MySQL INSERT and UPDATE performance?
  20. MySQL process goes over 100% of CPU usage
  21. MySQL Replication Performance
  22. How to Perform an UPSERT so that I can use both new and old values in update part
  23. MySql views performance [closed]
  24. SQL Performance UNION vs OR
  25. Can I use VARCHAR as the PRIMARY KEY?
  26. Should I COUNT(*) or not?
  27. MySQL stored procedures use them or not to use them
  28. JDBC batch insert performance
  29. Why is MySQL InnoDB insert so slow?
  30. MySQL: Many tables or many databases?
  31. Strings as Primary Keys in MYSQL Database [closed]
  32. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  33. InnoDB takes over an hour to import 600MB file, MyISAM in a few minutes
  34. Faster way to delete matching rows?
  35. What is MySQL “Key Efficiency”
  36. “SELECT COUNT(*)” is slow, even with where clause
  37. Optimize mySql for faster alter table add column
  38. How to make a primary key start from 1000?
  39. MySQL: is primary key unique by default?
  40. How many rows are ‘too many’ for a MySQL table? [duplicate]
  41. Calculated column in where-clause – performance
  42. Best MySQL performance tuning tool? [closed]
  43. MySql – slow sending data phase
  44. Is EXISTS more efficient than COUNT(*)>0?
  45. Creating tables and problems with primary key in Rails
  46. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  47. Changing Large MySQL InnoDB Tables
  48. Database: Best performance way to query geo location data?
  49. MySQL performance optimization: order by datetime field
  50. MySQL performance: multiple tables vs. index on single table and partitions

Leave a Comment