MainelyDesign.com Blog

Speeding Up Cakephp's ACL Component

Posted on 05/28/2010 at 12:12 pm by Kevin Wentworth
Viewed 2,992 times | 2 comments

I came across a posting today that changed the performance of my application tremendously and reinforced a concept I had forgotten about- mysql indices. I didn't realize my app was running slow until I implemented the mysql indexes below- no formal benchmark testing, but I would say speed improved by about 300%. The improvement was most noticeble when you are building your ACL using the CakePHP suggested method.

MySQL Query to Create CakePHP ACL Indexes

This code was originally found at http://pastie.org/823086 via the CakePHP Group. Thanks Jon Bennet! (You are my mysql performance guru too...)

Show Plain Text

/* ACL Tables */
 
CREATE TABLE acos (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    parent_id INT DEFAULT NULL,
    model VARCHAR(255) DEFAULT '',
    foreign_key INT UNSIGNED DEFAULT NULL,
    alias VARCHAR(255) DEFAULT '',
    lft INT DEFAULT NULL,
    rght INT DEFAULT NULL
) ENGINE = INNODB;
-- table name is quoted because it is a reserved word
CREATE INDEX idx_acos_lft_rght ON `acos`(lft,rght);
CREATE INDEX idx_acos_alias ON `acos`(alias);
CREATE INDEX idx_acos_model_foreign_key ON `acos`(model(255),foreign_key);
 
CREATE TABLE aros (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    parent_id INT DEFAULT NULL,
    model VARCHAR(255) DEFAULT '',
    foreign_key INT UNSIGNED DEFAULT NULL,
    alias VARCHAR(255) DEFAULT '',
    lft INT DEFAULT NULL,
    rght INT DEFAULT NULL
) ENGINE = INNODB;
-- table name is quoted because it is a reserved word
CREATE INDEX idx_aros_lft_rght ON `aros`(lft,rght);
CREATE INDEX idx_aros_alias ON `aros`(alias);
CREATE INDEX idx_aros_model_foreign_key ON `aros`(model(255),foreign_key);
 
CREATE TABLE aros_acos (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    aro_id INT UNSIGNED NOT NULL,
    aco_id INT UNSIGNED NOT NULL,
    _create CHAR(2) NOT NULL DEFAULT 0,
    _read CHAR(2) NOT NULL DEFAULT 0,
    _update CHAR(2) NOT NULL DEFAULT 0,
    _delete CHAR(2) NOT NULL DEFAULT 0
) ENGINE = INNODB;
-- table names are quoted because they are reserved words
CREATE UNIQUE INDEX idx_aros_acos_aro_id_aco_id ON `aros_acos`(aro_id, aco_id);
ALTER TABLE aros_acos ADD CONSTRAINT FOREIGN KEY (aro_id) REFERENCES `aros`(id);
ALTER TABLE aros_acos ADD CONSTRAINT FOREIGN KEY (aco_id) REFERENCES `acos`(id);

MySQL Indexing Saves Time (and Resources)

There it is! Mysql indexing can make a huge performance difference for any application. I've been inspired... I will consider mysql performance moving forward in all development. Even though CakePHP can do almost everything, it can't optimize your database tables.

Cheers,
-Kevin Wentworth

Tags for Speeding Up Cakephp's ACL Component

Comments for this Posting

Posted by pixelcone

on 4/9/10

Wow.. Really!
I'll tried this.. thank you!

http://pixelcone.com

Posted by David Umoh

on 29/3/11

Great stuff, i tried it and time taken for the acl queries to run was reduced by half...
Thanks man

Recent Posts

Subscribe to my feed