Mysql - GUID vs Auto Increment

Posted on 2010-01-05
Last Modified: 2013-12-13
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'
Question by:jwzk
    LVL 34

    Accepted Solution

    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.
    LVL 107

    Expert Comment

    by: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
    LVL 23

    Expert Comment

    This post seems quite useful

    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.

    Author Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now