3 ms·

So, you would rather have your primary keys be a 36 character UUID string instead of a 4-8 byte integer? This is for every single row in your database and will

So, you would rather have your primary keys be a 36 character UUID string instead of a 4-8 byte integer? This is for every single row in your database and will increase the size of your database substantially.

jasode 9y ago
>36 character UUID string To clarify, UUID is stored in the db as 16 bytes (128 bits): https://en.wikipedia.org/wiki/Universally_unique_identifier#Format https://en.wikipedia.org/wiki/Universally_unique_identifier#... You would not store the 32 hex characters plus 4 hyphen characters as strings in the db. That UUID string format is for human readable display not disk storage. (To downvoters, if 16 bytes is incorrect, please reply with what you believe to be the correct # of bytes for UUID.)
daigoba66 9y ago
Don’t store UUIDs as strings. Use the native UUID data type, which only uses 16 bytes (UUIDs are basically just 128 bit integers). That is still double the size of a 64 bit integer, but the trade off is that it can be random and universally unique.
- 33degrees 9y ago
  ... for databases that have a native UUID type. MySQL doesn't, and the choices are either store them as strings or store them as non readable bytes. Not the greatest options.
  - dragonwriter 9y ago
    You can store them as bytes and use a function to convert them to/from a display format (which is what DBs with a built in UUID type do under the covers.)
    - 33degrees 9y ago
      Yes, and I've done that in the past; it's still somewhat laborious though.
tkyjonathan 9y ago
on MySQL or any database that doesn't have it as a datatype, its 36 char on latin charset. If you leave everything as UTF-8, then its x3 that amount. Try it for yourself.
- jasode 9y ago
  I didn't downvote your previous comment but I'm guessing it got downvotes because the blog post you're criticizing is about PostgreSQL not MySQL. PostgreSQL can store UUID in 16 bytes. As a side comment about MySQL, there are various techniques to store UUID more compactly as 16 bytes inside of a "binary(16)" type like this: https://stackoverflow.com/a/28252188 https://stackoverflow.com/a/28252188 ... or similar technique shown at the bottom of the Percona blog: https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/ https://www.percona.com/blog/2014/12/19/store-uuid-optimized...

jasode 9y ago

>36 character UUID string To clarify, UUID is stored in the db as 16 bytes (128 bits): https://en.wikipedia.org/wiki/Universally_unique_identifier#Format https://en.wikipedia.org/wiki/Universally_unique_identifier#... You would not store the 32 hex characters plus 4 hyphen characters as strings in the db. That UUID string format is for human readable display not disk storage. (To downvoters, if 16 bytes is incorrect, please reply with what you believe to be the correct # of bytes for UUID.)

daigoba66 9y ago

Don’t store UUIDs as strings. Use the native UUID data type, which only uses 16 bytes (UUIDs are basically just 128 bit integers). That is still double the size of a 64 bit integer, but the trade off is that it can be random and universally unique.

33degrees 9y ago
... for databases that have a native UUID type. MySQL doesn't, and the choices are either store them as strings or store them as non readable bytes. Not the greatest options.
- dragonwriter 9y ago
  You can store them as bytes and use a function to convert them to/from a display format (which is what DBs with a built in UUID type do under the covers.)
  - 33degrees 9y ago
    Yes, and I've done that in the past; it's still somewhat laborious though.

tkyjonathan 9y ago

on MySQL or any database that doesn't have it as a datatype, its 36 char on latin charset. If you leave everything as UTF-8, then its x3 that amount. Try it for yourself.

jasode 9y ago
I didn't downvote your previous comment but I'm guessing it got downvotes because the blog post you're criticizing is about PostgreSQL not MySQL. PostgreSQL can store UUID in 16 bytes. As a side comment about MySQL, there are various techniques to store UUID more compactly as 16 bytes inside of a "binary(16)" type like this: https://stackoverflow.com/a/28252188 https://stackoverflow.com/a/28252188 ... or similar technique shown at the bottom of the Percona blog: https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/ https://www.percona.com/blog/2014/12/19/store-uuid-optimized...