uuid v4 vs v6 (ordered), which more efficient for MySQL?

user_6003883 0
Viewed 916

I have a question and I would like to know your opinion.

I have a model with id as the primary key and uuid as the secondary key, noting that all my external calls, that is, calls via API, I use uuid to identify the record, so I wonder, is it more efficient to use standard version 4 or version 6 (ordered)? Because I imagine the following, despite the uuid column being indexed, when making a request via API using uuid as an identifier, the database still needs to look for it in the index records, it would not be easier for the database to find this record if were "ordered" (v6)?

DB: MySQL/MariaDB

mariadb uuid mysql
1 Answers

Michael Coburn posted a brilliant blog about this: https://www.percona.com/blog/2015/04/03/illustrating-primary-key-models-in-innodb-and-their-impact-on-disk-usage/

The blog was written in 2015, but the idea is still true.

He showed that inserting records in "random" order by using an unordered UUID results in a great deal of fragmentation, because insertion in random order results in many page splits. Whereas when records are stored in insertion order because the primary key is monotonically increasing (e.g. an auto-increment integer or an ordered UUID), then there are fewer page-splits and the table ends up stored more compactly.

To the extent that a more compact table stored more records per page on average, and therefore a given amount of RAM allocated for cache holds a greater portion of your table makes it more efficient.

That blog is about primary keys. You asked about secondary keys.

The same would apply to any B-tree stored in pages. If you insert in random order, you're more likely to cause fragmentation and page splits. If you insert in increasing order, it will be more compact.

Bill_Karwin_208600
Related