4

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

2 Answers2

3

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
  • 4,190
  • 2
  • 27
  • 33
  • 4
    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 Oct 19 '10 at 22:58
  • 1
    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 Oct 20 '10 at 01:21
  • 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.. –  Oct 20 '10 at 12:01
  • 1
    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 Oct 21 '10 at 14:24
  • @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 Oct 22 '10 at 18:09
  • 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 Oct 24 '10 at 06:52
  • 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 Oct 24 '10 at 16:31
0

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.

theZiki
  • 939
  • 1
  • 8
  • 14