MariaDB Uuid as Primary Key Performance Problem / Tweaks im upgrading our mariadb database from PRIMARY KEY INT AUTO_INCREMENT to UUID PRIMARY KEY. We have around 80 tables with multiple relations between them. I've build a migration script to change the id field to BINARY(16) and created for every row a Uuid v4. Then i've updated the relations with the new uuid (also as BINARY(16)). When i run multiple queries to do a performance benchmark, i have an average performance decrease from 15% on my local docker environment.
The id field is a PRIMARY KEY. The relation fields are marked as index and have Foreign Key constraints.
What can i do to improve the performance?
Most of the tables has round about 200k entries. The whole database has a size of 3.2GB
UUIDs are not nice when it comes to performance.  In particular, there is no relationship between the 'next' row you fetch via a UUID and all the other rows that are currently cached.
Docker possibly has a &quot;small&quot; value for innodb_buffer_pool_size, thereby leading to I/O when random accesses into the table happen.
UUID v1 can have its bits shuffled to make them &quot;chronological&quot;, would remove most of the &quot;randomness&quot; of accesses.  But you are using v4.
Plan A:  Abandon UUIDs.
Plan B:  Use UUID v1 and shuffle the bits
Plan C:  Give docker enough room to have a big buffer_pool so that most of the indexes can be kept in RAM.
More:  http://mysql.rjweb.org/doc.php/uuid