Loadsys creates a lot of larger web applications for clients that have many user types with many privileges.  The ACL tables out of the box, ACOS and AROS, do not contain indexes except for the PRIMARY KEY on id.

Since the ACL uses Binary Search Trees, a lot of complex queries take place with the left and right nodes.

So, let’s take a look at how one of the most intensive ACL queries acts with no changes made.

mysql> explain SELECT `Aco`.`id`, `Aco`.`parent_id`, `Aco`.`model`, `Aco`.`foreign_key`, `Aco`.`alias` FROM `acos` AS `Aco` LEFT JOIN `acos` AS `Aco0` ON (`Aco0`.`alias` = 'controllers') LEFT JOIN `acos` AS `Aco1` ON (`Aco1`.`lft` > `Aco0`.`lft` AND `Aco1`.`rght` < `Aco0`.`rght` AND `Aco1`.`alias` = 'Applicants') LEFT JOIN `acos` AS `Aco2` ON (`Aco2`.`lft` > `Aco1`.`lft` AND `Aco2`.`rght` < `Aco1`.`rght` AND `Aco2`.`alias` = 'admin_view') WHERE ((`Aco`.`lft` <= `Aco0`.`lft` AND `Aco`.`rght` >= `Aco0`.`rght`) OR (`Aco`.`lft` <= `Aco2`.`lft` AND `Aco`.`rght` >= `Aco2`.`rght`)) ORDER BY `Aco`.`lft` DESC ;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | Aco   | ALL  | NULL          | NULL |    NULL | NULL |  548 | Using temporary; Using filesort |
|  1 | SIMPLE      | Aco0  | ALL  | NULL          | NULL |    NULL | NULL |  548 |                                 |
|  1 | SIMPLE      | Aco1  | ALL  | NULL          | NULL |    NULL | NULL |  548 |                                 |
|  1 | SIMPLE      | Aco2  | ALL  | NULL          | NULL |    NULL | NULL |  548 | Using where                     |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
4 rows in set (0.00 sec)

Every joined table is getting all rows. No indexing at all. This query will happen on most page loads.

So, let’s get this tuned up


create index acos_idx1 on acos (lft, rght);
create index acos_idx2 on acos (alias);
create index acos_idx3 on acos (model, foreign_key);

create index aros_idx1 on aros (lft, rght);
create index aros_idx2 on aros (alias);
create index aros_idx3 on aros (model, foreign_key);


mysql> explain SELECT `Aco`.`id`, `Aco`.`parent_id`, `Aco`.`model`, `Aco`.`foreign_key`, `Aco`.`alias` FROM `acos` AS `Aco` LEFT JOIN `acos` AS `Aco0` ON (`Aco0`.`alias` = 'controllers') LEFT JOIN `acos` AS `Aco1` ON (`Aco1`.`lft` > `Aco0`.`lft` AND `Aco1`.`rght` < `Aco0`.`rght` AND `Aco1`.`alias` = 'Applicants') LEFT JOIN `acos` AS `Aco2` ON (`Aco2`.`lft` > `Aco1`.`lft` AND `Aco2`.`rght` < `Aco1`.`rght` AND `Aco2`.`alias` = 'admin_view') WHERE ((`Aco`.`lft` <= `Aco0`.`lft` AND `Aco`.`rght` >= `Aco0`.`rght`) OR (`Aco`.`lft` <= `Aco2`.`lft` AND `Aco`.`rght` >= `Aco2`.`rght`)) ORDER BY `Aco`.`lft` DESC ;
+----+-------------+-------+------+---------------------+-----------+---------+-------+------+----------------+
| id | select_type | table | type | possible_keys       | key       | key_len | ref   | rows | Extra          |
+----+-------------+-------+------+---------------------+-----------+---------+-------+------+----------------+
|  1 | SIMPLE      | Aco   | ALL  | acos_idx1           | NULL      |    NULL | NULL  |  548 | Using filesort |
|  1 | SIMPLE      | Aco0  | ref  | acos_idx2           | acos_idx2 |     256 | const |    1 | Using where    |
|  1 | SIMPLE      | Aco1  | ref  | acos_idx1,acos_idx2 | acos_idx2 |     256 | const |    1 | Using where    |
|  1 | SIMPLE      | Aco2  | ref  | acos_idx1,acos_idx2 | acos_idx2 |     256 | const |   30 | Using where    |
+----+-------------+-------+------+---------------------+-----------+---------+-------+------+----------------+
4 rows in set (0.00 sec)

Now we are in business! Now that web application can scale a little further and you won’t go too crazy wondering why your pages are loading so slowly while doing CakePHP Development.

Loadsys is a CakePHP Development team specializing W3C xhtml/css standards and the CakePHP framework

Tags: ACL Tuning, cakephp developer, CakePHP Developers, cakephp development, CakePHP Performance, CakePHP Tuning

This entry was posted on Wednesday, March 19th, 2008 at 1:34 pm and is filed under CakePHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.