Should I use int or char for id's/uuid's (primary keys) in MySQL?

I use id's for almost all my tables, you never know when they come handy. But today I read this...

Be extra careful to make sure that, according to convention, your ‘id’ column (or primary key) is:

char(36) and never varchar(36)

CakePHP will work with both definitions, however you will be sacrificing about 50% of the performance of your DB (MySQL in particular). This will be most evident in more complicated SELECT’s, which might require some JOIN’s or calculations.

I wonder... why even use something text-based, when you only have to save integers? I care a great deal about using the开发者_如何学C right formats for the right content, so I wonder if char gives any performance improvements over integers?

---

I would strongly suggestest using ints. I am doing some modelling for my thesis and I work on large datasets. I had to create a table with about ~70.000.000 rows. My primary key was varchar + int. At the beginning one cycle of creating 5-digit number of rows took 5 minutes, soon it became 40. Dropping the primary key fixed my performance issue. I guess that it is because ensuring uniqueness and it was becoming more and more time consuming. I had no similar issues when my primary key was int. it is personal experience though, so maybe someone can give more theoretic and reliable answer.

---

char doesn't give any improvement over integer. But it's useful when you need to prevent users from knowing or tampering with other rows that you don't want them to.

Let's say each user have a profile picture with the naming /img/$id.jpg (the simplest case, since you don't have to store any data in DB for this information. Of course, there are other ways) If you use integer, someone can loop through all profile pictures that you have. With UUID, they can't.

When you have a lot of records, the auto increment int is better for performance. You can put the uuid in another field (secret_key, for example).