0

Imagine you have a dozen tables with each table using a UUID-ish varchar(190) as a primary key and most tables having 1-4 varchar(190) FKs to other tables in the database. Table sizes are expected to grow to millions of entries.

I've been searching around looking for recommendations but I'm struggling to find anything definitive apart from potentially insertion costs to random keys. I understand there are trade-offs to using UUIDs as database primary keys, but what I'm particularly interested in is whether having such a large 190 varchar based UUID in particular will cause additional performance issues. I did find information about index prefixes which would lead me to believe that there is a performance impact. I'm guessing that it could result in really big indexes and lower performance, but I'd like to know for sure.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
elchuppa
  • 53
  • 5
  • Have you already reviewed https://stackoverflow.com/questions/2365132/uuid-performance-in-mysql ? – PM 77-1 Aug 29 '22 at 19:23
  • @PM77-1 thanks for the pointer. I hadn't seen that one in particular but it looks like similar trade offs to the varchar searches I've run. What I'm wondering is if there's something particularly degenerate about having a really large varchar as a key that would make it unambiguously bad. – elchuppa Aug 29 '22 at 20:38
  • The longer the key, the worse the performance. Simple as that. I don't know what other kind of answer you're expecting here. – user207421 Aug 29 '22 at 23:50
  • I'm just hoping for something more specific. if queries or inserts are going to be 1-5% slower maybe I don't care, if the impact is an order of magnitude more than that than it's clear it's not the right way to go. – elchuppa Aug 30 '22 at 16:16
  • 2
    The impact of UUID is bigger than the length of the key. Being both UUID and long is a double whammy. I can devise a case where UUID slows down things by _10x_; length is only a few _percent_. – Rick James Aug 30 '22 at 17:58
  • 1
    I can even devise a case a slowdown due to artificially adding an AUTO_INCREMENT in place of a... perfectly good (albeit long) index – Rick James Aug 30 '22 at 18:02
  • See also https://stackoverflow.com/a/73546838/1766831 – Rick James Aug 30 '22 at 18:12
  • 1
    I can discuss further when and why length matters; but you will have to start another question that makes no mention of UUIDs. – Rick James Aug 30 '22 at 18:13
  • Thanks @RickJames your response WRT UUIDs being an order of magnitude greater than length is really what I wanted to know. My concern was that length could have a greater than linear impact on performance. – elchuppa Aug 31 '22 at 13:21
  • 1
    @elchuppa - In general, `VARCHAR(n)` performs identically regardless of the value of `n`. The tipping points are for n>191. VARCHAR stores the values as a 1- or 2-byte _actual_ length, plus the necessary bytes. So, for a given set of strings, the bytes on disk are identical for different values on n. – Rick James Aug 31 '22 at 20:21
  • @RickJames thanks for the clarification I hadn't realized that the 191 was the new 255 :) https://www.grouparoo.com/blog/varchar-191 – elchuppa Aug 31 '22 at 20:40
  • @elchuppa - The 191 goes away with 5.7. "255" as a max shows up in at least 2 ways over the years. More "limits": http://mysql.rjweb.org/doc.php/limits – Rick James Aug 31 '22 at 21:09

0 Answers0