Performance: UUID vs auto-increment in cakephp-mysql

views:

answers:

+1 Q:

Performance: UUID vs auto-increment in cakephp-mysql

I was searching if UUID generated by cakePHP (32 char long) is faster in performance compared to auto-increment. Comparison in both inserts and Select operation. Which one should I use UUID generated by cakePHP or by using simple auto-increment of MySQL

Here's is a case-study I found but its not specific to cakePHP

http://krow.livejournal.com/497839.html

I doubt you're going to notice much of a performance issue in choice of primary key. Your bottlenecks will be somewhere else, almost guaranteed.

By default, I recommend just using an auto-increment primary key. This makes sense -- you will know which order records were inserted in at a glance, so you can more easily delete them if test data, etc. Also, it's easier to recite a number than a 32 char UUID. So usability goes to auto incremented INTs.

So when would you use an UUID? Any situation where you want to make sure that the key is globally unique (or pretty darn near it). One example would be on a sharded database; db1 and db2. You can't use auto increment INTs, because then you could end up having 2 records (one in db1, one in db2) that have the same primary key, which will lead to nightmares if you ever need to combine or reshard. So that's one example when using an UUID is necessary.

But generally just stick with auto incremented INTs. It just makes life better.

Travis Leleu 2010-10-19 21:48:51

Actually, I'd go exactly the other way. UUIDs for portability and reversibility. There's no compelling benefit to INTs other than performance which, as you've noted, is nominal at best in most situations.

Rob Wilkerson 2010-10-19 22:58:57

Perhaps this is one of those "each to their own" situations then. I find, especially during initial dev work, that I'm constantly sorting through my records by reverse order so I can delete the rows I just added. When you sort UUIDs, do they sort by order of creation? Again, it just seems easier for me (as a programmer) to look at INTs rather than a 32-length string.

Travis Leleu 2010-10-20 01:21:26

Yes I was curious as the link which i posted shows that UUID is efficient in comparision to auto-inc though I personally prefer using auto-inc..

Abhishek Dilliwal 2010-10-20 12:01:39

Using an autoincrement to determine record age is going to bite you in the ass. If you need to store a records creation date/time do so in a dedicated field. Using a surrogate key as metadata for something it isn't should just be avoided.

Abba Bryant 2010-10-21 14:24:59

@Abba I'm in agreement with you there. But when I'm inserting test data into a record and want to check it out, it's nice to be able to quickly sort by a human readable field (and easily parsable field, which is why I prefer int over a date field).

Travis Leleu 2010-10-22 18:09:33

So in that case why not just collect and sort on a created field? Especially if your using cake, which makes it happen for you. It also gives immediate semantic meaning to your model relationships or query defaults when you see ... 'sort' => 'created' in a declaration somewhere. I just think as a convenience or quick and dirty mechanism that it just doesn't offer any advantage over using uuids. Fixtures and testing are easier imo as a side affect.

Abba Bryant 2010-10-24 06:52:08

Because, as I've stated before, I have a personal preference to sort by integers. It's easier for me to read to make sure everything looks alright. For testing. Anyhow, as I think we've established this is a preference so I don't think there's much merit to continuing this discussion. Thanks for your opinions.

Travis Leleu 2010-10-24 16:31:56

ansaurus

tags:

views:

answers:

Performance: UUID vs auto-increment in cakephp-mysql

related questions