I'd love to hear everyone's opinion on this issue. I currently use Innodb mysql v8 in RDS. Our db is moderate, with ~130 tables, most are small, with our largest table having ~30 million rows over 6 years. We've currently scaled our RDS instant vertically way larger than our database should need and we're still CPU bound and can't use replicas because the slave gets too far behind. I'm convinced it's because we have UUID version 1 as our primary keys for our tables and have to do lots of string comparisons for our application.
What I'd like to do is prove it by creating a subset of tables that use an auto-incrementing INT as the primary key and foreign keys but I'm struggling to see how this could work. If I just add an auto-incrementing column to the current tables, the queries that our ORM uses won't include it so it wouldn't be that much of a gain. What I am wondering is how I could use a transition table to do essentially swap out the values from UUIDs to INT. For instance, an ORM query now says something like:
select id_primary_key from table_old where id_foreign_key in (UUID1, UUID2, UUID3) with an output of :1d3sed4a-5812-a35c-0204-515f6at42b46
If I create a transition table that has auto_inc INT, and old UUID, what would be the next step so that when the ORM runs that query, it queries against INTs instead of UUIDs?
select id_primary_key from table_new where id_foreign_key in (1, 2, 3) output: 58743
Any thoughts?
UPDATE-- It looks like we're going to be looking into more of a global model in the future, where have multiple dbs and as such will keep the UUIDs. We'll try to go the sharding route as well as trying to store them as VARBINARY(16) and using UUIDtoBIN() w/ the swap flag set to true because we're using UUID v1. Thank you all for your answers and time! I learned a lot from testing it.
memcmp()
call internally, instead of comparing character-by-character using the collation's equivalency lookups. In my experience, binary collation can gain up to 30% performance improvement. But that's still only incremental. Optimizations that gain orders of magnitude improvement are worth more.