zen of coding

If you use UUID’s…

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.

  • PostgreSQL has dedicated column type for UUID called… uuid ;-)

    Regards

  • Charles Patterson

    MySQL will automatically convert any CHAR field to VARCHAR if the row contains any other field that is VARCHAR.

  • teknoid

    @Charles Patterson

    This is the kind of work you’d want to avoid.
    Not to mention the indexing is slower on varchar. Besides a few other things that happen under the hood, which I am not aware of. (Stick to char, for things that are well known, such UUID() which has a guaranteed length).

  • Stefan

    Thanks for the tipp. But how can I enforce cakephp to use sql’s char(36) instead of varchar(36) when using cake’s schema management features and schema.php?

    ‘id’ => array(‘type’ => ‘string’, ‘null’ => false, ‘default’ => NULL, ‘length’ => 36, ‘key’ => ‘primary’, ‘collate’ => ‘utf8_general_ci’, ‘charset’ => ‘utf8’)

    in schema.php always gives me varchar(36) id column when using

    cake schema create…?

  • teknoid

    @Stefan

    Unfortunately, IMO, that is an optimization that needs to be made in cake.
    I believe there was a request at some point, but not sure what happened after…

  • Ashsih Mahana

    thank you for suggestion, its helpful

  • Ashsih Mahana

    its really helpful, thanks a lot

%d bloggers like this: