[Building Sakai] MFR_MESSAGE_T table performance enhancement

[Earle Nietzel]

After digging into this some more I have come across a better solution for
those slow queries.

I began to break up the query into its different parts and execute them
individually until I found what was actually causing the slow down in
Forums.

My recommendation is to have index created on USER_ID for table
MFR_PVT_MSG_USR_T.

CREATE INDEX MFR_PVT_MSG_USR_T_USER_ID_I on MFR_PVT_MSG_USR_T (USER_ID);

This reduced query times from 1.5 seconds to .012 seconds.

BEFORE:
+----+-------------+--------------+--------+----------------------------+---------+---------+-----------------------------------------+--------+----------------------------------------------+

| id | select_type | table        | type   | possible_keys              |
key     | key_len | ref                                     | rows   |
Extra                                        |
+----+-------------+--------------+--------+----------------------------+---------+---------+-----------------------------------------+--------+----------------------------------------------+

|  1 | SIMPLE      | recipients1_ | ALL    | PRIMARY,FKC4DE0E1473D286ED |
NULL    | NULL    | NULL                                    | 299781 |
Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | privatemes0_ | eq_ref | PRIMARY                    |
PRIMARY | 8       | ilearn.recipients1_.messageSurrogateKey |      1 |
Using where                                  |
+----+-------------+--------------+--------+----------------------------+---------+---------+-----------------------------------------+--------+----------------------------------------------+


AFTER:
+----+-------------+--------------+--------+--------------------------------------------------------+-----------------------------+---------+-----------------------------------------+------+----------------------------------------------+

| id | select_type | table        | type   | possible_keys
| key                         | key_len | ref
| rows | Extra                                        |
+----+-------------+--------------+--------+--------------------------------------------------------+-----------------------------+---------+-----------------------------------------+------+----------------------------------------------+

|  1 | SIMPLE      | recipients1_ | ref    |
PRIMARY,FKC4DE0E1473D286ED,MFR_PVT_MSG_USR_T_USER_ID_I |
MFR_PVT_MSG_USR_T_USER_ID_I | 767     | const
|   93 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | privatemes0_ | eq_ref | PRIMARY
| PRIMARY                     | 8       |
ilearn.recipients1_.messageSurrogateKey |    1 | Using where
|
+----+-------------+--------------+--------+--------------------------------------------------------+-----------------------------+---------+-----------------------------------------+------+----------------------------------------------+



Earle

PS The index I mentioned previously is not needed.

-----sakai-dev-bounces at collab.sakaiproject.org wrote: -----

To: sakai-dev at collab.sakaiproject.org
From: Earle Nietzel <Earle.Nietzel at marist.edu>
Sent by: sakai-dev-bounces at collab.sakaiproject.org
Date: 04/24/2009 11:47AM
Subject: [Building Sakai] MFR_MESSAGE_T table performance enhancement


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

_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org
http://collab.sakaiproject.org/mailman/listinfo/sakai-dev

TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org
with a subject of "unsubscribe"