Recent Posts
- (09/10) Fixing Warning: the ECDSA host key for 'github.com' differs from the key for the IP addressTAGS:Web Server Admin
- (12/26) CakePHP 3 - Getting List of Column Definitions from a Table (like schema())TAGS:CakephpCake3
- (09/14) Change Order of Loaded Behaviors in CakePHP 3TAGS:Cake3CakephpWeb ProgrammingPhp
- (05/29) CakePHP 3 - Accept JSON Header Only Working When Debug FalseTAGS:Web ProgrammingCakephpCake3
- (05/23) Remove All Events from Google Calendar (Reset Calendar)TAGS:Web ProgrammingPhp
- (11/08) Google Tag Manager (GTM) Not Firing Default PageView EventTAGS:Web ProgrammingJavascriptGoogle Tag Manager
- (10/13) In PHP, how do you get __toString() Magic Method Result without calling echo?TAGS:CakephpCake3Cakephp 13PhpWeb Programming
- (11/14) Getting output from shell_exec() at all timesTAGS:Web ProgrammingWeb Server Admin
Subscribe to my feed
MainelyDesign.com Blog
Speeding Up Cakephp's ACL Component
Posted on 05/28/2010 at 12:12 pm by Kevin Wentworth
Viewed 18,038 times | 1 comment
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...)
- /* ACL Tables */
- CREATE TABLE acos (
- 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 TABLE aros (
- 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 (
- 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);
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
Cakephp | Database | Web Programming | Mysql | Forum Answers | Php | Example
Comments for this Posting
Sorry, comments are closed for this posting.
Please Email Kevin if you have any questions. Thanks!
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
www.edikonenterprises.com.ng