asked on

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'

ASKER CERTIFIED SOLUTION

Beverley Portlock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

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

Tony McCreath

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.

jwzk

ASKER