Mysql PK and FK char(36) vs int(10)

I have read that mysql can handle indexes and searches better if the tables keys are integers rather than char(36) UUID's.

Is it worth converting to int(10)开发者_Go百科?

NOTE: We only use 1 server and have currently have to plans to use multiple databases concurrently therefore removing one of the big reasons for using UUID. Our tables are also InnoDB if that makes a difference.

Thanks in advance.

Usually RDBMS can handle Integer keys as PK more efficient, than other datatypes. The reason is how it build up the index for the column, so yes: As long as you dont require string (or other typed) keys, you should always use integers.

However: CHAR(36) and INT(10) are far away from being equal, because a INT(10) is much smaller, than CHAR(36). I dont know, if you require so many different keys, but you should keep that in mind.

Update, to complete the last paragraph: INT(10) is 32-Bit, CHAR(36) is 36 - ^Byte^ (= 288-Bit). This does not only mean, that INT consumes less space, it also means, that CHAR(36) has about 4 times more different keys.

UUID's are an already indexed row, but as all people knows, character keys are slower than numeric keys because the index size.

But if you need an UUID or character join / index you have to take this into account:

1. CHAR keys are indeed slower for joins compared to integer keys
2. Performance degradation can range from few percent to couple of times for Innodb tables
3. MyISAM Tables may suffer significantly if key compression is not disabled
4. Joining on Shorter CHAR keys is significantly faster than Long keys
5. Latin1 (I guess any simple encoding) is significantly faster for joins compared to UTF8

I share this info and have tested it in a production environment and it can be found in http://forums.mysql.com/read.php?24,263462,263462

InnoDB makes huge difference. If you define a PRIMARY KEY on your tables, InnoDB uses it as the clustered index.

It's very expensive to do DML operations on huge innodb tables. If You have many joins, use integers. If You have many dml use int. If You have much more selects than dml use natural keys, no matter int or char