[Building Sakai] MFR_MESSAGE_T table performance enhancement
Earle Nietzel
Earle.Nietzel at marist.edu
Fri Apr 24 08:47:02 PDT 2009
Found a query that was appearing in our slow query log rather frequently
recently and did some testing with the mysql profiler tool and found a
speed up for the MFR_MESSAGE_T table.
The query in question is a rather large query but i will list the important
pieces:
select ..... (left out for to save space)
from MFR_MESSAGE_T privatemes0_ left outer join MFR_PVT_MSG_USR_T
recipients1_ on privatemes0_.ID=recipients1_.messageSurrogateKey
where privatemes0_.MESSAGE_DTYPE='PM' and
recipients1_.USER_ID='27024526-9c61-4cef-9fd4-0840b52a300b' and
recipients1_.TYPE_UUID='e6d3664d-d75e-46c0-00f1-81cf3288a29e' and
recipients1_.CONTEXT_ID='a722e35d-ca3f-4192-a5de-0f2444a40b27'
order by privatemes0_.CREATED desc;
This query produce the following profiler ouput:
+--------------------+-----------+
| Status | Duration |
+--------------------+-----------+
| (initialization) | 0.000001 |
| Opening tables | 0.00001 |
| System lock | 0.000004 |
| Table lock | 0.000005 |
| init | 0.0000660 |
| optimizing | 0.00002 |
| statistics | 0.000025 |
| preparing | 0.000014 |
| executing | 0.000002 |
| Sorting result | 0.539899 |
| Sending data | 1.096705 |
| end | 0.000011 |
| query end | 0.000004 |
| freeing items | 0.000019 |
| closing tables | 0.000005 |
| logging slow query | 0.000002 |
+--------------------+-----------+
Notice the Sorting result took approx .54 seconds everything else appears
to be normal.
After adding the following index:
create index MFR_MESSAGE_T_CREATED_I on MFR_MESSAGE_T (CREATED);
The profiler then shows that Sorting was reduced to 0.000005 and the
overall query was reduced by .5 seconds.
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| (initialization) | 0.000001 |
| Opening tables | 0.00001 |
| System lock | 0.000003 |
| Table lock | 0.000006 |
| init | 0.000072 |
| optimizing | 0.000021 |
| statistics | 0.000029 |
| preparing | 0.000017 |
| executing | 0.000002 |
| Sorting result | 0.000005 |
|*Sending data | 1.444712 |
| end | 0.000012 |
| query end | 0.000003 |
| freeing items | 0.000019 |
| closing tables | 0.000006 |
| logging slow query | 0.000002 |
+--------------------+----------+
*note although the sending data time did go up i believe that to be linked
to the client I am using as I have seen varying times depending on the
client that is used.
Comments are welcome,
Earle
More information about the sakai-dev
mailing list