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.

It is always better to put that logic on database, it will be faster for inserting and selecting. Because when MySQL makes PK with an auto increment value than automatically makes index on that field and for better performance puts it on right place on drive., so if you using sorting it will be faster with indexed PK than 32 long characters.