Using UUID in CakePHP, What DataType Is Recommended?

Question

My Scenario:

• New Cake (2.x) Project, No DB Yet
• MySQL Clustered, and maybe Oracle Clustered Prod
• No DATA needs to be migrated/imported
• Data can look like: Users -> HABTM -> Groups -> HABTM -> Other Groups

I've been doing a little research on how to use UUIDs with CakePHP, and I have found the following:

Cake has Native Support for UUID, but it assumes CHAR(36):

http://book.cakephp.org/2.0/en/getting-started/cakephp-conventions.html

This Stack Answer points out that:

The cost of doing UUID as CHAR(36) is ridiculously high, and becomes stupid at 1+ milion, 10+ million, 100+ millions of rows, in my humble experience

This Blog Post claims that BINARY(36) is better than CHAR(36):

Although CakePHP does not support the 16 byte hex encoded UUID with the key type of BINARY(16), it does support BINARY(36) which is still better than using CHAR(36) which can be slowed down by collation.

...but the Cake Docs don't say that...

My Question is, given CakePHP/MySQL (or CakePHP/Oracle), is CHAR(36) the only reasonable choice here, or is there a better, more efficient way to use UUIDs with CakePHP (or any other PHP Framework for that matter)?

Answer 1

You probably already know this, but you could generate an id with String::uuid() for new records, and have BINARY(36) in your table - I don't think cake has any automagic past the uuid generation.

Answer 2

If CakePHP is given a model with an id field whose SQL type is Binary(36), then every time you create new records, the id field will be given a new GUID value. This behaviour is here since version 1.3.15.

Regarding your other issue, since CHAR involves collation, I would go for the binary solution.