patternsqlMinor
Having both a integer Primary Key and a GUID/UUID column on same table-is this bad
Viewed 0 times
guidthissameprimarycolumnhavingbadbothuuidand
Problem
I have my MySQL database having tables where on each table, we have a ID (int, auto increment) clustered primary key and a UUID (varchar36) column that's just a GUID.
The ID is there just for indexing purposes and nothing more.
Joins and all queries will run against the UUID column. That means foreign keys will reference the UUID columns.
Reason I do this is because I want to be able to migrate more easily.
I am aware of the size penalty of the GUID.
Question: is this bad architecture? What are the performance implications beyond size?
The ID is there just for indexing purposes and nothing more.
Joins and all queries will run against the UUID column. That means foreign keys will reference the UUID columns.
Reason I do this is because I want to be able to migrate more easily.
I am aware of the size penalty of the GUID.
Question: is this bad architecture? What are the performance implications beyond size?
Solution
I personally don't see a problem with having both a ID and a UID, I use this technique quite a lot actually. However, I still use the primary key to perform the JOIN on as this performs better - the UID is used mainly in the WHERE clause when searching for records.
In your example, the biggest point I would make is not to use a VARCHAR(36) for your UID - I would use BINARY(16) as this fits a UUID perfectly. The CHAR type is 1 byte and so you are using an extra 20 bytes of storage to persist your UUID. Also, joining on this column will be slower than a join on the BINARY(16) equivalent.
In your example, the biggest point I would make is not to use a VARCHAR(36) for your UID - I would use BINARY(16) as this fits a UUID perfectly. The CHAR type is 1 byte and so you are using an extra 20 bytes of storage to persist your UUID. Also, joining on this column will be slower than a join on the BINARY(16) equivalent.
Context
StackExchange Database Administrators Q#122833, answer score: 2
Revisions (0)
No revisions yet.