Loud Baking

Are you concerned about how fast your queries are handled? Do you run a website with lots of AROs & ACOs? If you answered no to any of the previous questions, you can just skip reading because it’s not for you.

Ok - so let’s dive directly in, I am short on time but started liking this sharing knowledge stuff.

Right now, whenever DB_ACL::check() kicks in (whenever a user needs to get authorized for an action), there is 2 main SELECT queries and then, depending on the number of nodes in the path to get to that user’s group, there is another query for each (so a minimum of one). When I was running some tests (debug=2) today, I just couldn’t ignore the number of queries made on each page refresh, etc. Being me, I stopped coding and testing my code and instead jumped on MySQL. What I wanted to do? Run some EXPLAIN on those queries, see what’s really going on. Results, IMO, where bad. Let me explain.

First 3 queries sent look something like this: SELECT Aro.id, Aro.parentid, Aro.model, Aro.foreignkey, Aro.alias FROM aros AS Aro LEFT JOIN aros AS Aro0 ON Aro.lft <= Aro0.lft AND Aro.rght >= Aro0.rght WHERE Aro0.model = ‘User’ AND Aro0.foreign_key = 1 ORDER BY Aro.lft DESC;

SELECT Aco.id, Aco.parentid, Aco.model, Aco.foreignkey, Aco.alias FROM acos AS Aco LEFT JOIN acos AS Aco0 ON Aco0.alias = ‘Users’ WHERE (Aco.lft <= Aco0.lft AND Aco.rght >= Aco0.rght) ORDER BY Aco.lft DESC;

SELECT Permission.id, Permission.aro_id, Permission.aco_id, Permission._create, Permission._read, Permission._update, Permission._delete, Permission._max, Permission._credits, Aro.id, Aro.parent_id, Aro.model, Aro.foreign_key, Aro.alias, Aro.lft, Aro.rght, Aco.id, Aco.parent_id, Aco.model, Aco.foreign_key, Aco.alias, Aco.lft, Aco.rght FROM aros_acos AS Permission LEFT JOIN aros AS Aro ON (Permission.aro_id = Aro.id) LEFT JOIN acos AS Aco ON (Permission.aco_id = Aco.id) WHERE Permission.aro_id = 1 AND Permission.aco_id IN (1) ORDER BY Aco.lft desc;

So a quick EXPLAIN on each returns:

mysql> EXPLAIN
    -> SELECT Aro.id, Aro.parentid, Aro.model, Aro.foreignkey, Aro.alias
    ->        FROM aros AS Aro
    ->        LEFT JOIN aros AS Aro0 ON Aro.lft <= Aro0.lft AND Aro.rght >= Aro0.rght
    ->        WHERE Aro0.model = ‘User’ AND Aro0.foreign_key = 1
    ->        ORDER BY Aro.lft DESC;
+—-+————-+——-+——+—————+——+———+——+——+———————————+
| id | selecttype | table | type | possiblekeys | key  | key_len | ref  | rows | Extra                           |
+—-+————-+——-+——+—————+——+———+——+——+———————————+
|  1 | SIMPLE      | Aro   | ALL  | NULL          | NULL | NULL    | NULL |   17 | Using temporary; Using filesort |
|  1 | SIMPLE      | Aro0  | ALL  | NULL          | NULL | NULL    | NULL |   17 | Using where                     |
+—-+————-+——-+——+—————+——+———+——+——+———————————+
2 rows in set (0.01 sec)


mysql> EXPLAIN
    -> SELECT Aco.id, Aco.parentid, Aco.model, Aco.foreignkey, Aco.alias
    ->        FROM acos AS Aco
    ->        LEFT JOIN acos AS Aco0 ON Aco0.alias = ‘Users’
    ->        WHERE (Aco.lft <= Aco0.lft AND Aco.rght >= Aco0.rght)
    ->        ORDER BY Aco.lft DESC;
+—-+————-+——-+——+—————+——+———+——+——+———————————+
| id | selecttype | table | type | possiblekeys | key  | key_len | ref  | rows | Extra                           |
+—-+————-+——-+——+—————+——+———+——+——+———————————+
|  1 | SIMPLE      | Aco   | ALL  | NULL          | NULL | NULL    | NULL |   21 | Using temporary; Using filesort |
|  1 | SIMPLE      | Aco0  | ALL  | NULL          | NULL | NULL    | NULL |   21 | Using where                     |
+—-+————-+——-+——+—————+——+———+——+——+———————————+
2 rows in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT Permission.id, Permission.aro_id, Permission.aco_id, Permission._create, Permission._read, Permission._update, Permission._delete, Permission._max, Permission._credits, Aro.id, Aro.parent_id, Aro.model, Aro.foreign_key, Aro.alias, Aro.lft, Aro.rght, Aco.id, Aco.parent_id, Aco.model, Aco.foreign_key, Aco.alias, Aco.lft, Aco.rght
    -> FROM aros_acos AS Permission
    -> LEFT JOIN aros AS Aro ON (Permission.aro_id = Aro.id) LEFT JOIN acos AS Aco ON (Permission.aco_id = Aco.id)
    -> WHERE Permission.aro_id = 1 AND Permission.aco_id IN (1)
    -> ORDER BY Aco.lft desc;
+—-+————-+————+——-+—————+———+———+——-+——+———————————————-+
| id | selecttype | table      | type  | possiblekeys | key     | keylen | ref   | rows | Extra                                        |
+—-+————-+————+——-+—————+———+———+——-+——+———————————————-+
|  1 | SIMPLE      | Permission | ALL   | NULL          | NULL    | NULL    | NULL  |   20 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Aro        | const | PRIMARY       | PRIMARY | 4       | const |    1 |                                              |
|  1 | SIMPLE      | Aco        | const | PRIMARY       | PRIMARY | 4       | const |    1 |                                              |
+—-+————-+————+——-+—————+———+———+——-+——+———————————————-+
3 rows in set (0.00 sec)

WOW! For each of those queries, it goes through all the table rows? The numbers here might not represent how important that is, but check the column ‘rows’, 17,17,21,21,20,1,1 - and that’s with only 17 record in ‘aros’, 21 records in ‘acos’ and 20 in ‘arosacos’.

Looking back at the queries and the tables, first thing that struck me was the inexistent indexes (only PRIMARY). From the queries, one could identify an index for each.

Query 1: model and foreignkey for the aros table Query 2: alias for the acos table Query 3: aroid and acoid for the arosacos table

I created the following:

mysql> ALTER TABLE aros
    ->   ADD INDEX user (model(255),foreign_key);
Query OK, 17 rows affected (0.02 sec)
Records: 17  Duplicates: 0  Warnings: 0


mysql> ALTER TABLE acos
    ->   ADD INDEX alias (alias(255));
Query OK, 21 rows affected (0.02 sec)
Records: 21  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE aros_acos
    ->   ADD INDEX relation (aro_id,aco_id);
Query OK, 20 rows affected (0.01 sec)
Records: 20  Duplicates: 0  Warnings: 0

A quick check to the results right after this change:

mysql> EXPLAIN
    -> SELECT Aro.id, Aro.parentid, Aro.model, Aro.foreignkey, Aro.alias
    ->        FROM aros AS Aro
    ->        LEFT JOIN aros AS Aro0 ON Aro.lft <= Aro0.lft AND Aro.rght >= Aro0.rght
    ->        WHERE Aro0.model = ‘User’ AND Aro0.foreign_key = 1
    ->        ORDER BY Aro.lft DESC;
+—-+————-+——-+——+—————+——+———+————-+——+———————————————-+
| id | selecttype | table | type | possiblekeys | key  | key_len | ref         | rows | Extra                                        |
+—-+————-+——-+——+—————+——+———+————-+——+———————————————-+
|  1 | SIMPLE      | Aro0  | ref  | user          | user | 263     | const,const |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Aro   | ALL  | NULL          | NULL | NULL    | NULL        |   17 | Using where                                  |
+—-+————-+——-+——+—————+——+———+————-+——+———————————————-+
2 rows in set (0.00 sec)


mysql> EXPLAIN
    -> SELECT Aco.id, Aco.parentid, Aco.model, Aco.foreignkey, Aco.alias
    ->        FROM acos AS Aco
    ->        LEFT JOIN acos AS Aco0 ON Aco0.alias = ‘Users’
    ->        WHERE (Aco.lft <= Aco0.lft AND Aco.rght >= Aco0.rght)
    ->        ORDER BY Aco.lft DESC;
+—-+————-+——-+——+—————+——-+———+——-+——+———————————————-+
| id | selecttype | table | type | possiblekeys | key   | key_len | ref   | rows | Extra                                        |
+—-+————-+——-+——+—————+——-+———+——-+——+———————————————-+
|  1 | SIMPLE      | Aco0  | ref  | alias         | alias | 258     | const |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Aco   | ALL  | NULL          | NULL  | NULL    | NULL  |   21 | Using where                                  |
+—-+————-+——-+——+—————+——-+———+——-+——+———————————————-+
2 rows in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT Permission.id, Permission.aro_id, Permission.aco_id, Permission._create, Permission._read, Permission._update, Permission._delete, Permission._max, Permission._credits, Aro.id, Aro.parent_id, Aro.model, Aro.foreign_key, Aro.alias, Aro.lft, Aro.rght, Aco.id, Aco.parent_id, Aco.model, Aco.foreign_key, Aco.alias, Aco.lft, Aco.rght
    -> FROM aros_acos AS Permission
    -> LEFT JOIN aros AS Aro ON (Permission.aro_id = Aro.id) LEFT JOIN acos AS Aco ON (Permission.aco_id = Aco.id)
    -> WHERE Permission.aro_id = 1 AND Permission.aco_id IN (1)
    -> ORDER BY Aco.lft desc;
+—-+————-+————+——-+—————+———-+———+————-+——+———————————————-+
| id | selecttype | table      | type  | possiblekeys | key      | key_len | ref         | rows | Extra                                        |
+—-+————-+————+——-+—————+———-+———+————-+——+———————————————-+
|  1 | SIMPLE      | Permission | ref   | relation      | relation | 10      | const,const |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Aro        | const | PRIMARY       | PRIMARY  | 4       | const       |    1 |                                              |
|  1 | SIMPLE      | Aco        | const | PRIMARY       | PRIMARY  | 4       | const       |    1 |                                              |
+—-+————-+————+——-+—————+———-+———+————-+——+———————————————-+
3 rows in set (0.00 sec)

Aha! Now that’s what I call an improvement (1,17,1,21,1,1,1). Just imagine scaling those numbers to the thousands only (who said millions?), you just saved LOTS! Did you also notice that now, there is a type, a key and a ref for each query?

I know that some might wonder but what about when you are creating the record, that would now take more time? My answer to that: and what? It will take more time once while the other way around would take much more time on bigger tables and not once, but on every refresh (unless you have that cached, but in my case, every refresh counts - I am doing also limitations like max number of searches allowed, etc. all managed from the same permissions’ row). In any case, cached or not, I believe it will only do good.

In the coming days, I will definitely be testing some more (I have to make sure that my extended ACL for limitations works perfectly), so I probably will identify calls made differently. The following indexes are what I think will be next:

ARO: alias ACO: model and foreign_key

They are untested yet, and I can’t say they make any difference - but for the performance optimization I did today, I’m quite satisfied. What’s your say on all that? Did you run some tests like this on your production environments to see how much can be saved? Let me know in the comments.

This entry was posted on Sunday, September 30th, 2007 at 7:03 am and is filed under CakePHP, MySQL. 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.