MainelyDesign.com Blog

Speeding Up Cakephp's ACL Component

Posted on 05/28/2010 at 12:12 pm by Kevin Wentworth
Viewed 18,150 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...)

Show Plain Text
  1. /* ACL Tables */
  2.  
  3. CREATE TABLE acos (
  4.     id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  5.     parent_id INT DEFAULT NULL,
  6.     model VARCHAR(255) DEFAULT '',
  7.     foreign_key INT UNSIGNED DEFAULT NULL,
  8.     alias VARCHAR(255) DEFAULT '',
  9.     lft INT DEFAULT NULL,
  10.     rght INT DEFAULT NULL
  11. ) ENGINE = INNODB;
  12. -- table name is quoted because it is a reserved word
  13. CREATE INDEX idx_acos_lft_rght ON `acos`(lft,rght);
  14. CREATE INDEX idx_acos_alias ON `acos`(alias);
  15. CREATE INDEX idx_acos_model_foreign_key ON `acos`(model(255),foreign_key);
  16.  
  17. CREATE TABLE aros (
  18.     id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  19.     parent_id INT DEFAULT NULL,
  20.     model VARCHAR(255) DEFAULT '',
  21.     foreign_key INT UNSIGNED DEFAULT NULL,
  22.     alias VARCHAR(255) DEFAULT '',
  23.     lft INT DEFAULT NULL,
  24.     rght INT DEFAULT NULL
  25. ) ENGINE = INNODB;
  26. -- table name is quoted because it is a reserved word
  27. CREATE INDEX idx_aros_lft_rght ON `aros`(lft,rght);
  28. CREATE INDEX idx_aros_alias ON `aros`(alias);
  29. CREATE INDEX idx_aros_model_foreign_key ON `aros`(model(255),foreign_key);
  30.  
  31. CREATE TABLE aros_acos (
  32.     id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  33.     aro_id INT UNSIGNED NOT NULL,
  34.     aco_id INT UNSIGNED NOT NULL,
  35.     _create CHAR(2) NOT NULL DEFAULT 0,
  36.     _read CHAR(2) NOT NULL DEFAULT 0,
  37.     _update CHAR(2) NOT NULL DEFAULT 0,
  38.     _delete CHAR(2) NOT NULL DEFAULT 0
  39. ) ENGINE = INNODB;
  40. -- table names are quoted because they are reserved words
  41. CREATE UNIQUE INDEX idx_aros_acos_aro_id_aco_id ON `aros_acos`(aro_id, aco_id);
  42. ALTER TABLE aros_acos ADD CONSTRAINT FOREIGN KEY (aro_id) REFERENCES `aros`(id);
  43. 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

Bookmark and Share

1 Related Articles

Tags for Speeding Up Cakephp's ACL Component

Cakephp | Database | Web Programming | Mysql | Forum Answers | Php | Example

Comments for this Posting

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

Sorry, comments are closed for this posting.

Please Email Kevin if you have any questions. Thanks!

Meet Site Avenger - Hosted Content Management System

Powered By: Site Avenger | Site Production: Saco Design