For version 2 of one of our projects we made the decision to scrap the customized access control from version 1 and make use of the ACL functionality built-in to Cake. Which seemed like the right way to go, that way we’d benefit from improvements to this feature in Cake itself and would have one less thing to worry about. That was almost certainly the right decision, but we very quickly ran into issues with page load times. For most Cake applications the existing ACL functionality is probably fine, but in this case we wanted to know if a user had permission to access certain functionality before displaying a link to it. So we were running multiple calls to Acl->check()
on every page load, which quickly bogged things down.
Looking into things made it pretty obvious we weren’t the only ones with this problem. There were suggestions for tuning that we implemented, but that didn’t solve the problem. So today I sat down to dig into this again. The root of the problem was with a particular slow query that (in the case of my development machine) took around 200 ms to run:
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` = ’App’ ) LEFT JOIN `acos` AS `Aco2` ON ( `Aco2`.`lft` > `Aco1`.`lft` AND `Aco2`.`rght` < `Aco1`.`rght` AND `Aco2`.`alias` = ’Menus’ ) LEFT JOIN `acos` AS `Aco3` ON ( `Aco3`.`lft` > `Aco2`.`lft` AND `Aco3`.`rght` < `Aco2`.`rght` AND `Aco3`.`alias` = ’index’ ) WHERE ( ( `Aco`.`lft` <= `Aco0`.`lft` AND `Aco`.`rght` >= `Aco0`.`rght` ) OR ( `Aco`.`lft` <= `Aco3`.`lft` AND `Aco`.`rght` >= `Aco3`.`rght` ) ) ORDER BY `Aco`.`lft` DESC
In this query Cake’s ACL routine is looking for rules matching controllers/App/Menus/index
to see whether or not the current user should be allowed in. My first thought was caching the results of these queries, but when I looked at what was running I found that it wasn’t the same query over and over, but rather a large number of similar queries that each took around 200ms to run. So I’d need to speed up the query the first time it ran, not the second time.
So I tried messing around with the SQL directly. Surprisingly, the first thing I tried seemed to work. I moved the lft
and rght
conditions out of the ON
clauses and into the WHERE
clause:
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`.`alias` = ’App’) LEFT JOIN `acos` AS `Aco2` ON ( `Aco2`.`alias` = ’Menus’) LEFT JOIN `acos` AS `Aco3` ON (`Aco3`.`alias` = ’index’) WHERE `Aco1`.`lft` > `Aco0`.`lft` AND `Aco1`.`rght` < `Aco0`.`rght` AND `Aco2`.`lft` > `Aco1`.`lft` AND `Aco2`.`rght` < `Aco1`.`rght` AND `Aco3`.`lft` > `Aco2`.`lft` AND `Aco3`.`rght` < `Aco2`.`rght` AND ( ( `Aco`.`lft` <= `Aco0`.`lft` AND `Aco`.`rght` >= `Aco0`.`rght` ) OR ( `Aco`.`lft` <= `Aco3`.`lft` AND `Aco`.`rght` >= `Aco3`.`rght` ) ) ORDER BY `Aco`.`lft` DESC
So, now I knew what the problem was, but I didn’t yet know where it was coming from or how to solve the problem. A bit more digging revealed that the SQL was generated in cake/libs/model/db_acl.php in the node()
method of class AclNode
. This allowed me to add in a custom model MyAcl that did the following:
-
Defined custom
MyAco
andMyAro
classes that extendedAco
andAro
and redefined thenode()
method to use the faster SQL from above. -
Defined a custom
MyAcl
class that extendedDbAcl
and redefined the constructor to useMyAco
andMyAro
instead ofAro
andAco
. -
Changed core.php to use
MyAcl
asAcl.classname
. I added anApp::import("model","MyAcl")
before theConfigure::write()
statement to ensure the model would be available.
With these changes in place things ran much, much faster. Now I just need to figure out some of proving to myself that my new SQL is actually equivalent to what was being generated before.