[Building Sakai] MSGCNTR Forums mysql picks a slow query plan over a faster query plan

[Earle Nietzel]

Hello everyone,

Got a bit of an issue here with forums and a particular query that mysql is
coming up with different query plans. Depending on which one it selects it
can take a significant amount of time to complete.

The hql in question (added from MSGCNTR-364):

  <query
name="findNumModeratedTopicsForSiteByUserByMembershipWithPermissionLevelName">
    <![CDATA[select count(*) from
org.sakaiproject.component.app.messageforums.dao.hibernate.TopicImpl as
topic,

org.sakaiproject.component.app.messageforums.dao.hibernate.PermissionLevelImpl
as pl
            join topic.openForum as forum
            join forum.area as area
            join topic.membershipItemSet as membershipItem
            where area.contextId = :contextId and
            topic.moderated = true and
            membershipItem.name in ( :membershipList ) and
            pl.moderatePostings = true and
            (pl.typeUuid != :customTypeUuid and
               pl.name = membershipItem.permissionLevelName)
        ]]>
  </query>

Which then is compiled into this SQL:

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='517f8f28-f68e-42ee-b168-919a06f82229'
and topicimpl0_.MODERATED=1
and (membership4_.NAME in ('Student' , 'MATH_111L_114_11F'))
and permission1_.MODERATE_POSTINGS=1
and permission1_.TYPE_UUID!='00000000-0000-0000-7777-000000000000'
and permission1_.NAME=membership4_.PERMISSION_LEVEL_NAME

The efficient query plan looks like:
+----+-------------+--------------+------+-------------------------------------------------+------------------------------+---------+-------------------------------------------+-------+--------------------------+
| id | select_type | table        | type |
possible_keys                                   |
key                          | key_len |
ref                                       | rows  | Extra
|
+----+-------------+--------------+------+-------------------------------------------------+------------------------------+---------+-------------------------------------------+-------+--------------------------+
|  1 | SIMPLE      | areaimpl3_   | ref  |
PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE,CONTEXT_ID |
MFR_AREA_CONTEXT_UUID_UNIQUE | 767     |
const                                     |     2 | Using where; Using index
|
|  1 | SIMPLE      | openforumi2_ | ref  |
PRIMARY,FKC17608478B5E2A2F                      |
FKC17608478B5E2A2F           | 9       |
ilearn.areaimpl3_.ID                      |     3 | Using where; Using index
|
|  1 | SIMPLE      | topicimpl0_  | ref  |
PRIMARY,FK863DC0BE74C7E92B                      |
FK863DC0BE74C7E92B           | 9       |
ilearn.openforumi2_.ID                    |    35 | Using where
|
|  1 | SIMPLE      | membership4_ | ref  |
FKE03761CB7DEF8466,MFRMEMITEM_COMPOSITE_IDX1    |
FKE03761CB7DEF8466           | 9       |
ilearn.topicimpl0_.ID                     |     3 | Using where
|
|  1 | SIMPLE      | permission1_ | ref  |
MFRPERMLVL_COMPOSITE_IDX1                       |
MFRPERMLVL_COMPOSITE_IDX1    | 152     |
ilearn.membership4_.PERMISSION_LEVEL_NAME | 10167 | Using where
|
+----+-------------+--------------+------+-------------------------------------------------+------------------------------+---------+-------------------------------------------+-------+--------------------------+

The problem occurs when mysql selects a query plan where tables membership4_
and permission1_ are not at the end but towards the top of the query plan
(this query takes several minutes to run):
+----+-------------+--------------+--------+-------------------------------------------------+---------------------------+---------+-------------------------------------------+-------+-------------+
| id | select_type | table        | type   |
possible_keys                                   | key
| key_len | ref                                       | rows  | Extra
|
+----+-------------+--------------+--------+-------------------------------------------------+---------------------------+---------+-------------------------------------------+-------+-------------+
|  1 | SIMPLE      | membership4_ | range  |
FKE03761CB7DEF8466,MFRMEMITEM_COMPOSITE_IDX1    | MFRMEMITEM_COMPOSITE_IDX1
| 767     |NULL                                       | 67305 | Using where
|
|  1 | SIMPLE      | permission1_ | ref    |
MFRPERMLVL_NAME_IDX                             | MFRPERMLVL_NAME_IDX
| 152     | ilearn.membership4_.PERMISSION_LEVEL_NAME | 22004 | Using where
|
|  1 | SIMPLE      | topicimpl0_  | eq_ref |
PRIMARY,FK863DC0BE74C7E92B                      | PRIMARY
| 8       | ilearn.membership4_.t_surrogateKey        |     1 | Using where
|
|  1 | SIMPLE      | openforumi2_ | eq_ref |
PRIMARY,FKC17608478B5E2A2F                      | PRIMARY
| 8       | ilearn.topicimpl0_.of_surrogateKey        |     1 | Using where
|
|  1 | SIMPLE      | areaimpl3_   | eq_ref |
PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE,CONTEXT_ID | PRIMARY
| 8       | ilearn.openforumi2_.surrogateKey          |     1 | Using where
|
+----+-------------+--------------+--------+-------------------------------------------------+---------------------------+---------+-------------------------------------------+-------+-------------+


At the moment the only way I've been able to make sure mysql doesn't select
the slow query plan is to use straight_join but it appears this is specific
to mysql and therefore not supported by hibernate.

Do we need a to implement a native query specific for MySQL in this case?

Here is some psuedo code to represent what I am thinking:

HibernateCallback hcb = new HibernateCallback() {
  public Object doInHibernate(Session session) throws HibernateException,
SQLException {
    if
("mysql".equals(serverConfigurationService.getString(vendor at org.sakaiproject.db.api.SqlService
))
      Query q =
session.createSQLQuery(MYSQL_QUERY_GET_NUM_MOD_TOPICS_WITH_MOD_PERM_BY_PERM_LEVEL_NAME);
    else
          Query q =
session.getNamedQuery(QUERY_GET_NUM_MOD_TOPICS_WITH_MOD_PERM_BY_PERM_LEVEL_NAME);
    q.setParameterList("membershipList", membershipList);
    q.setParameter("contextId", getContextId(), Hibernate.STRING);
    q.setParameter("customTypeUuid", typeManager.getCustomLevelType(),
Hibernate.STRING);
    return q.uniqueResult();
  }
};

Ideas and thoughts most welcome,
Earle
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20110902/3f64990b/attachment.html