[Building Sakai] Forums moderated query taking too long to execute

[Qu, Yuanhua]

We observed in our mysql db log that following query took too long to execute (quite often 400 to 500 secs)

SELECT count( * ) AS col_0_0_
FROM MFR_TOPIC_T topicimpl0_
INNER JOIN MFR_OPEN_FORUM_T openforumi2_ ON topicimpl0_.of_surrogateKey = openforumi2_.ID
INNER JOIN MFR_AREA_T areaimpl3_ ON openforumi2_.surrogateKey = areaimpl3_.ID
INNER JOIN MFR_MEMBERSHIP_ITEM_T membership4_ ON topicimpl0_.ID = membership4_.t_surrogateKey, MFR_PERMISSION_LEVEL_T permission1_
WHERE areaimpl3_.CONTEXT_ID = 'dc127270-2f54-432e-8d1f-705359ac625a'
AND topicimpl0_.MODERATED =1
AND (
membership4_.NAME
IN (
'instructor', '201012.SOWK5322.1'
)
)
AND permission1_.MODERATE_POSTINGS =1
AND (
permission1_.TYPE_UUID <> '00000000-0000-0000-7777-000000000000'
AND permission1_.NAME = membership4_.PERMISSION_LEVEL_NAME
OR membership4_.PERMISSION_LEVEL = permission1_.ID
)

We have indexes on those big tables and it seems don't help much for this case.   Does this occur to anyone too? If yes, do you have solution for this?  Above query is called to check if moderated topics are there for the user to moderate. Therefore, it is called frequently.  Here is the calling sequence when dfForums.jsp is loaded.

isDisplayPendingMsgQueue -->

getNumModTopicsWithModPermission -->

getNumModTopicCurrentUserHasModPermFor(List)  -->

QUERY_GET_NUM_MOD_TOPICS_WITH_MOD_PERM = findNumModeratedTopicsForSiteByUserByMembership

We are checking if  there is a quick opimization solution in db to help out that we are not aware of.  Or do we have to improve performance from the application, like:

 Add relative light weight checks (a, b), reducing the frequency of executing that query .  seudo code:

    a. check if user has permission for forum/topic moderate
    b. if yes, check if moderate of the permitted topic/forum is enabled
    c. if yes, get the number of moderated topics by executing the above query

Any guidance will be appreciated.

-Qu






show index from MFR_TOPIC_T
Table     Non_unique     Key_name     Seq_in_index     Column_name     Collation     Cardinality     Sub_part     Packed     Null     Index_type     Comment
MFR_TOPIC_T     0     PRIMARY     1     ID     A     427111     NULL     NULL           BTREE
MFR_TOPIC_T     1     FK863DC0BE82FAB29     1     pf_surrogateKey     A     427111     NULL     NULL     YES     BTREE
MFR_TOPIC_T     1     FK863DC0BEFF3B3AE9     1     pt_surrogateKey     A     15     NULL     NULL     YES     BTREE
MFR_TOPIC_T     1     FK863DC0BE74C7E92B     1     of_surrogateKey     A     17     NULL     NULL     YES     BTREE
MFR_TOPIC_T     1     MFR_TOPIC_T_UUID_I     1     UUID


show index from MFR_OPEN_FORUM_T

MFR_OPEN_FORUM_T      0      PRIMARY      1      ID      A      11422      NULL      NULL             BTREE
MFR_OPEN_FORUM_T     1     FKC17608478B5E2A2F     1     surrogateKey     A     11422     NULL     NULL     YES     BTREE

show index from  MFR_AREA_T

Table     Non_unique     Key_name     Seq_in_index     Column_name     Collation     Cardinality     Sub_part     Packed     Null     Index_type     Comment
MFR_AREA_T     0     PRIMARY     1     ID


show index from  MFR_MEMBERSHIP_ITEM_T

Table     Non_unique     Key_name     Seq_in_index     Column_name     Collation     Cardinality     Sub_part     Packed     Null     Index_type     Comment
MFR_MEMBERSHIP_ITEM_T     0     PRIMARY     1     ID     A     217407     NULL     NULL           BTREE
MFR_MEMBERSHIP_ITEM_T     0     PERMISSION_LEVEL     1     PERMISSION_LEVEL     A     54351     NULL     NULL     YES     BTREE
MFR_MEMBERSHIP_ITEM_T     1     FKE03761CBA306F94D     1     a_surrogateKey     A     6211     NULL     NULL     YES     BTREE
MFR_MEMBERSHIP_ITEM_T     1     FKE03761CB7DEF8466     1     t_surrogateKey     A     24156     NULL     NULL     YES     BTREE
MFR_MEMBERSHIP_ITEM_T     1     FKE03761CB74C7E92B     1     of_surrogateKey     A     21740     NULL     NULL     YES     BTREE
MFR_MEMBERSHIP_ITEM_T     1     MFR_MEMBERSHIP_ITEM_I01_I     1     t_surrogateKey     A     54351     NULL     NULL     YES     BTREE
MFR_MEMBERSHIP_ITEM_T     1     MFR_MEMBERSHIP_ITEM_I02_I     1     a_surrogateKey     A     9     NULL     NULL     YES     BTREE
MFR_MEMBERSHIP_ITEM_T     1     NAME     1     NAME     A

show index from  MFR_PERMISSION_LEVEL_T

Table     Non_unique     Key_name     Seq_in_index     Column_name     Collation     Cardinality     Sub_part     Packed     Null     Index_type     Comment
MFR_PERMISSION_LEVEL_T     0     PRIMARY     1     ID     A     16548     NULL     NULL           BTREE
MFR_PERMISSION_LEVEL_T     1     NAME     1     NAME     A     1     NULL     NULL           BTREE
MFR_PERMISSION_LEVEL_T     1     ID     1     ID     A     16548     NULL     NULL           BTREE
MFR_PERMISSION_LEVEL_T     1     MODERATE_POSTINGS     1     MODERATE_POSTINGS     A     1     NULL     NULL           BTREE
MFR_PERMISSION_LEVEL_T     1     MODERATE_POSTINGS     2     NAME     A     1     NULL     NULL           BTREE
MFR_PERMISSION_LEVEL_T     1     MODERATE_POSTINGS     3     ID     A     16548     NULL     NULL           BTREE


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20100831/b06ec05b/attachment.html