char vs varchar for UUIDs in MySQL and PostgreSQL
I spent some time today evaluating whether switching columns that store UUIDs from
binary, etc) would result in any noticeable performance improvement in MySQL. It does seem like it could make an improvement, but not enough to be worth the effort in our case.
What I learned from researching today:
- Both MySQL and PostgeSQL support the same syntax for declaring
varcharcolumns the same way, so any performance boost in MySQL wouldn’t be reflected in PostgreSQL (source) . Also, PostgreSQL has a native UUID type, so it’s kind of a wash.
- The number of variable-width columns in a row can make a significant difference in performance. (This post claimed a 20% speed improvement by switching to
ROW_FORMAT=fixed, which seems related but maybe not the same.)
- It’s more likely to make a difference if the column is indexed (source)
varchartakes extra memory to store a prefix, which I presume to describe the length which
charwould not need
- Storing UUIDs in a non-character column could make sense, but since
UUID()returns characters anyway, it’s going against the grain.
Conclusion: Use the
CHAR(36) type in MySQL. Use the
UUID type in PostgreSQL.
If I’m wrong about what I took away from reading today, please let me know in the comments. I’d love to learn more about this.