[Building Sakai] Site Stats 2 Performance

[Earle Nietzel]

Site Statistics 2 was showing some long queries against table 
SST_RESOURCES.

select SITE_ID,
      USER_ID,
      sum(resourcest0_.RESOURCE_COUNT) as col3
from SST_RESOURCES
where SITE_ID='33964def-1fd2-4384-97ae-5194b7ed4f02' 
      and RESOURCE_ACTION='read' 
      and (RESOURCE_REF like 
'/content/group/33964def-1fd2-4384-97ae-5194b7ed4f02/%') 
      and RESOURCE_DATE>='2009-10-21' 
      and RESOURCE_DATE<'2009-12-29' 
group by SITE_ID,
      USER_ID 
order by col3 DESC;

After profiling this query:

+------+----------------------+----------+---------+---------+---------+---------+
| seq  | state                | numb_ops | sum_dur | avg_dur | sum_cpu | 
avg_cpu |
+------+----------------------+----------+---------+---------+---------+---------+
|    1 | starting             |        1 | 0.00006 | 0.00006 | 0.00000 | 
0.00000 | 
|    2 | Opening tables       |        1 | 0.00001 | 0.00001 | 0.00000 | 
0.00000 | 
|    3 | System lock          |        1 | 0.00000 | 0.00000 | 0.00000 | 
0.00000 | 
|    4 | Table lock           |        1 | 0.00001 | 0.00001 | 0.00000 | 
0.00000 | 
|    5 | init                 |        1 | 0.00003 | 0.00003 | 0.00000 | 
0.00000 | 
|    6 | optimizing           |        1 | 0.00001 | 0.00001 | 0.00000 | 
0.00000 | 
|    7 | statistics           |        1 | 0.00002 | 0.00002 | 0.00000 | 
0.00000 | 
|    8 | preparing            |        1 | 0.00001 | 0.00001 | 0.00000 | 
0.00000 | 
|    9 | Creating tmp table   |        1 | 0.00004 | 0.00004 | 0.00000 | 
0.00000 | 
|   10 | Sorting for group    |        1 | 0.00000 | 0.00000 | 0.00000 | 
0.00000 | 
|   11 | executing            |        1 | 0.00000 | 0.00000 | 0.00000 | 
0.00000 | 
|   12 | Copying to tmp table |        1 | 5.50261 | 5.50261 | 5.50816 | 
5.50816 | 
|   13 | Sorting result       |        1 | 0.00003 | 0.00003 | 0.00000 | 
0.00000 | 
|   14 | Sending data         |        1 | 0.00004 | 0.00004 | 0.00000 | 
0.00000 | 
|   15 | end                  |        3 | 0.00001 | 0.00000 | 0.00000 | 
0.00000 | 
|   16 | removing tmp table   |        1 | 0.00002 | 0.00002 | 0.00000 | 
0.00000 | 
|   19 | query end            |        1 | 0.00000 | 0.00000 | 0.00000 | 
0.00000 | 
|   20 | freeing items        |        1 | 0.00001 | 0.00001 | 0.00000 | 
0.00000 | 
|   21 | closing tables       |        1 | 0.00001 | 0.00001 | 0.00000 | 
0.00000 | 
|   22 | logging slow query   |        1 | 0.00000 | 0.00000 | 0.00000 | 
0.00000 | 
|   23 | cleaning up          |        1 | 0.00001 | 0.00001 | 0.00000 | 
0.00000 | 
+------+----------------------+----------+---------+---------+---------+---------+

Step 12 is the culprit which leads to believe that there is possibly no 
index on field used in GROUP BY.

CREATE TABLE `SST_RESOURCES` (
  `ID` bigint(20) NOT NULL auto_increment,
  `USER_ID` varchar(99) NOT NULL,
  `SITE_ID` varchar(99) NOT NULL,
  `RESOURCE_REF` varchar(255) NOT NULL,
  `RESOURCE_ACTION` varchar(12) NOT NULL,
  `RESOURCE_DATE` date NOT NULL,
  `RESOURCE_COUNT` bigint(20) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `SST_RESOURCES_USER_ID_I` (`USER_ID`)
)

hmmm no index on SITE_ID.

Adding the following index:

CREATE INDEX SST_RESOURCES_SITE_ID_I ON SST_RESOURCES (SITE_ID);

Reduced the query from 5.5 seconds to .07 seconds.

Nice speed up,
Earle
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20091229/699962e6/attachment.html