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
Great tip. I was doing some research about possible cakePHP performance issues and found some post concerning slowness with cakePHP ACL’s. Now this seems to be the solution.
Thanks for the tip.
After having some troubles, I added this index on the ACO table :
ALTER TABLE `acos` ADD INDEX ( `foreign_key` , `lft` )
It helps the many selects that have to be done to find the parent node when inserting large number of related objects.
Boy, that saved me a good 3500 milisecons…
Thanks a bunch. I was running up against the JOIN_LIMIT set by my host and getting the “Warning (512): DbAcl::check() – Failed ARO/ACO node lookup in permissions check. Node references: Aro: Array” error. This fixed the issue. Appreciate it.
Thanks! Shaved 16000 ms (!!!) of one single ACL-query! Now I can proclaim major success in making the app faster 😉
Ensuring indexes in place is always the obvious step.
What you really want to watch out for with CakePHP ACLs is granularity.
Make sure you aren’t storing users in the aro tree if you don’t need per user permissions.
If your site is intended to have more than a few dozen users I highly recommend groupwise permissions only. Whether that improves performance or not isn’t important.
You will avoid allot of maintenance problems down the road. Looking after the permissions tree for thousands/millions of users is potentially a major undertaking. Once it gets large it is not an easy structure to debug or repair. You may fine yourself facing bizarre problems that are best solved by rebuilding the entire tree.