4

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?

Improve this question
7
  • 1
    Chwck this article that has some tests: percona.com/blog/2014/12/19/store-uuid-optimized-way
    – ypercubeᵀᴹ
    Commented Dec 3, 2015 at 19:05
  • 1
    So you have a MySQL Database, not a SQL Server database? Why is this question tagged sql-server ? SQL Server is not MySQL.
    – Hannah Vernon ♦
    Commented Dec 3, 2015 at 19:12
  • 2
    How does a GUID make migration easier? And what kind of migration?
    – Hannah Vernon ♦
    Commented Dec 3, 2015 at 19:14
  • I have the sql server tag because while I am using mySql, the same issue would arise in a SQL server environment
    – jordan koskei
    Commented Dec 3, 2015 at 19:18
  • 1
    Migration has many problems. You are prematurely trying to solve a problem that might be insignificant in the big picture.
    – Rick James
    Commented Dec 12, 2015 at 5:49

3 Answers 3

Reset to default
2

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.

Improve this answer
6
  • 3
    Out of curiosity why are you using the GUID in the WHERE clause? Surely it's faster to search a 4-byte integer column than a binary(16) column?
    – Hannah Vernon ♦
    Commented Dec 3, 2015 at 19:15
  • because the GUID field is essentialy the Unique identifier. I do not want to use the autoincrementing Integer. As such, foreign keys reference the GUID field. However, I dont want to have the GUID field indexed due to serious the problems associated with that that you can read elsewhere Data portability is what is causing me to adopt GUID over the autoincrementing integer.
    – jordan koskei
    Commented Dec 3, 2015 at 19:23
  • @MaxVernon indeed it is, but unfortunately it is what is submitted to the database by the client.
    – Mr.Brownstone
    Commented Dec 3, 2015 at 20:01
  • @jordankoskei using the GUID does cause problems if you use it as the PK in MySQL due to page-splits (although there is a solution for this in SQL Server: NEWSEQUENTIALID()) however, that does not prevent you from placing a non-clustered index on it instead.
    – Mr.Brownstone
    Commented Dec 3, 2015 at 20:06
  • @Mr.Brownstone thanks for your 2 responses. Your commment raises a new question. Is there any advantage in having the GUID field having the nun clustered index over not indexing it at all since the Int field is already indexed?
    – jordan koskei
    Commented Dec 3, 2015 at 20:48
1

MySQL...

If your UUID index gets too big to be cached, performance will go down severely. This is because of the randomness of a UUID, hence the unlikeliness of the next block you need being in cache.

If you do stick with UUIDs, convert them to BINARY(16) in order to get closer to the performance and storage requirements of INT.

But, why have two UNIQUE keys for a table? If you are having FKs to the UUID, then make it the PK.

In InnoDB, a secondary index is a BTree. After drilling down it, there is another drill down in the BTree that contains the PRIMARY KEY and data. That is extra overhead that can be avoided if you look up by, and JOIN by, the PRIMARY KEY, not a secondary key.

I vote against UUIDs in almost every situation.

More on UUIDs in MySQL: http://mysql.rjweb.org/doc.php/uuid

Improve this answer
1
  • And I have several comments on the link @ypercube mentioned.
    – Rick James
    Commented Dec 12, 2015 at 5:59
0

As i understand your question you have 2 unique ids in your table. If they are both truly unique and have no other purpose (ie no business meaning) you have a redundant field.

If you're developing a system that will merge data later and you want to assign a persistent unique id now then use a GUID. If you want performance and simplicity use an int.

Other than enforcing uniqueness... Putting an index on a column that you won't search by is pointless.

Searching on columns that aren't indexed will perform poorly.

Ideally if you can enforce uniqueness and improve query performance with a single column you will have a more efficient design.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.