AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Mysql uuid vs long index performance2/29/2024 Now one other thought regarding UUID: when doing mass inserts there is a notable difference between "random" order of primary keys and "ordered" primary key values. but all of that once you reach gigabytes or something. Also at some size halfing the size of a backup reduces time for backup and restore. 100000*36 byte is just 3.6 Megabyte which is tiny for most things) loading it from disk and keeping it in memory is costing. However with some amount of rows (relative to available memory. One is about double the size (16 vs 36) on a relatively small set that makes little difference in total. As such, converting a UUID into a Byte Array requires little more than a binaryDecode() call.Ĭonverting a binary value back into a UUID is a little more work since we have to re-insert the dashes ( -) after we generate the String.The difference between those is the size on storage, thus amount of IO. This technique is based on the fact that a UUID is already a HEX-encoded value. Not to mention that any other table using said UUID as a foreign key will also need 35-bytes.Ī common suggestion for reducing storage size is to persist the value as a VARBINARY(16) instead of a VARCHAR(35). When you consider that the primary key is implicitly stored as the suffix on a secondary index, the storage requirements of a "UUID as String" is multiplied by the number of indexes on the table. If a UUID is a 35-character String, storing said UUID as a String requires 35-bytes (1 byte per character).Īnd, that's just for the column value itself. Part of the index-size issue comes from how the value is stored. This post doesn't tackle all of those issues - I'm here to noodle on just one of them: larger indexes. Percona Blog: Storing UUID Values in MySQLįrom what I've seen in these articles - which is echoed in many StackOverflow posts - is that using Strings as primary keys is a trade-off: in return for having system-independent uniqueness, you incur larger indexes, larger working memory, possible performance hits, less intuitive values (pro-or-con depending on how you see it), and more complex workflows.Rick James: GUID/UUID Performance Breakthrough. MySQL Blog: Storing UUID Values in MySQL Tables.To start learning about storing Strings as primary keys, I did some reading: Consider this post a note-to-self more than anything. But, I'm not one of those people who knows much about low-level storage details, engine ramifications, data replication, or any of the many complex topics that go into database management. And yes, I love thinking deeply about database index design. To be clear, I am not a database expert! Yes, I love writing SQL. This post looks at performing this String-to-Binary conversion in ColdFusion. And, based on what I've been reading, it seems that being able to convert a UUID string to and from a binary value is an important point of know-how. As such, I wanted to start building up some foundational knowledge. Or, more specifically, using something like a UUID (Universally Unique Identifier), GUID, or CUID as a table's primary key. The other day, while recording a Working Code podcast episode, I mentioned to Adam that a big blind-spot in my database mental model was storing non- AUTO_INCREMENT primary keys.
0 Comments
Read More
Leave a Reply. |