• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1516
  • Last Modified:

Mysql - GUID vs Auto Increment

Yeah I've read around on the topic, but I'm still not sure what the best solution is in my case.

The database will be a one-to-many type, and the primary key (GUID, or auto increment) will be used mainly for mysql UPDATES.

UPDATE `data` SET `so` =  'and so' WHERE `id` = 'GUID OR AUTO INC'
0
jwzk
Asked:
jwzk
1 Solution
 
Beverley PortlockCommented:
I think in MySQL it is UUID rather than GUID, but the principle is the same.

My rule of thumb is this - never use an autoincrement in a index that could be a foreign key in another table. I have seen too many databases get stuffed because somebody made a mess of exporting and importing the data and all the auto-increment field changed values thus breaking record relations. It is far better to have "fixed" but unique fields for such things.

The only problem with GUIDs or UUIDs is the sheer size of them, but as long as that is not an issue for you then they should work OK. The alternative is to use your own version of "auto increment" where you lock the table for write, check the highest existing number and then add one, write the record and unlock the table, but it is clumsy.
0
 
Ray PaseurCommented:
I agree with Brian, and while I have often used AUTO_INCREMENT keys as foreign keys, I have always had a queasy feeling about the backup / restore issues that could happen if somebody else (not me) did the restore.  

Also, if you're interested in a code sample that will generate a short, UNIQUE field that is meaningless and therefore perfect for a foreign key, please post a question about generating UNIQUE keys and I will be glad to share,  Best regards, ~Ray
0
 
Tony McCreathTechnical SEO ConsultantCommented:
This post seems quite useful

http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-not-to-uuid/

I think one issue is that mySql does not support UUID or GUID as a native type so it is often stored in a text field. This would be a lot slower than an integer used in auto increment.

Storing UUID in binary format seems to be the solution. A bit fiddly but would make UUID more comparable in performance to Auto Increment.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now