[Building Sakai] MSGCNTR Forums mysql picks a slow query plan over a faster query plan
Bryan Holladay
holladay at longsight.com
Wed Sep 7 11:47:22 PDT 2011
Here are the create statements for the selected index's in the explain:
CREATE INDEX FKE03761CB7DEF8466 USING BTREE
ON MFR_MEMBERSHIP_ITEM_T(t_surrogateKey);
CREATE INDEX FK863DC0BE74C7E92B USING BTREE
ON MFR_TOPIC_T(of_surrogateKey);
CREATE INDEX FKC17608478B5E2A2F USING BTREE
ON MFR_OPEN_FORUM_T(surrogateKey);
CREATE UNIQUE INDEX MFR_AREA_CONTEXT_UUID_UNIQUE USING BTREE
ON MFR_AREA_T(CONTEXT_ID, TYPE_UUID);
All but the last index is from the Foreign-key contraints
-Bryan
On Wed, Sep 7, 2011 at 2:41 PM, Bryan Holladay <holladay at longsight.com> wrote:
> It still looks like your index's are off... I ran an explain on a huge
> db and got better results than the straight_join query. I noticed
> your explain is using the PRIMARY index for TOPIC, AREA and OPENFORUM
> while my explain only shows PRIMARY for MEMBERSHIP. The results are
> pasted below and I've also attached the csv file to make it easier to
> read.
>
> Thanks,
> Bryan
>
> id select_type table type possible_keys key key_len ref rows Extra
> 1 SIMPLE areaimpl3_ ref PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE MFR_AREA_CONTEXT_UUID_UNIQUE 767 const 1 Using
> where; Using index
> 1 SIMPLE openforumi2_ ref PRIMARY,FKC17608478B5E2A2F FKC17608478B5E2A2F 9 sakai28.areaimpl3_.ID 1 Using
> where; Using index
> 1 SIMPLE topicimpl0_ ref PRIMARY,FK863DC0BE74C7E92B FK863DC0BE74C7E92B 9 sakai28.openforumi2_.ID 37 Using
> where
> 1 SIMPLE membership4_ ref PERMISSION_LEVEL,FKE03761CB7DEF8466,FKE03761CB88085F8E FKE03761CB7DEF8466 9 sakai28.topicimpl0_.ID 6 Using
> where
> 1 SIMPLE permission1_ eq_ref PRIMARY,MFR_MODERATE_POSTINGS_IDX PRIMARY 8 sakai28.membership4_.PERMISSION_LEVEL 1 Using
> where
>
> On Wed, Sep 7, 2011 at 10:33 AM, Earle Nietzel <earle.nietzel at gmail.com> wrote:
>> Hi Bryan,
>>
>> (I had also included it the original post)
>>
>> The only thing I added was the straight_join (MySQL specific). Take a look
>> last query seems to be the better performer.
>>
>> Original query:
>>
>> 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
>>
>>
>>
>> Query with straight_join added:
>>
>> select straight_join 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
>>
>>
>> This query with a straight_join seems to out perform the query above and its
>> query plan matches that of the original query when the original runs well:
>> (I would recommend this query)
>>
>> select straight_join count(topicimpl0_.ID) as col_0_0_
>> from MFR_AREA_T areaimpl3_
>> inner join MFR_OPEN_FORUM_T openforumi2_ on
>> openforumi2_.surrogateKey=areaimpl3_.ID
>> inner join MFR_TOPIC_T topicimpl0_ on
>> topicimpl0_.of_surrogateKey=openforumi2_.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
>>
>> Earle
>>
>> On Wed, Sep 7, 2011 at 8:12 AM, Bryan Holladay <holladay at longsight.com>
>> wrote:
>>>
>>> Earle,
>>>
>>> Could you send me both queries:
>>>
>>> 1) the slow query that Sakai uses (the actual SQL query, not the
>>> hibernate query. You can get this by setting hibernate.show_sql=true
>>> in the sakai.properties (DO NOT SET THIS IN PRD!!!) and watch the
>>> logs).
>>>
>>> 2) your new join query to resolve this (actual SQL)
>>>
>>> Then I can look at the query plans on my local machine to see how the
>>> index's pan out.
>>>
>>> Thanks,
>>> Bryan
>>>
>>> On Tue, Sep 6, 2011 at 5:48 PM, Earle Nietzel <earle.nietzel at gmail.com>
>>> wrote:
>>> > Hi Bryan.
>>> >
>>> > I have included the create statements from our DB (see below inline).
>>> >
>>> > Everything matches up except we have a few additional Foreign Keys
>>> > while you didn't mention the other tables I am assuming that they are
>>> > fine.
>>> >
>>> > On the side I did a hbm2ddl and it seems that more indexes are
>>> > definitely created according to hibernate.
>>> >
>>> > I realize this issue is a bit strange but I don't believe there to be
>>> > a problem with sakai but rather mysql.
>>> >
>>> > So I suggested a workaround for mysql using a straight_join.
>>> >
>>> > I am not sure if that is the best idea or maybe rework the query so
>>> > that mysql doesn't get into a situation of picking a bad query plan.
>>> >
>>> > Earle
>>> >
>>> > BTW We are using mysql-5.1.52-1.el6_0.1.x86_64
>>> >
>>> > On Tue, Sep 6, 2011 at 8:24 AM, Bryan Holladay <holladay at longsight.com>
>>> > wrote:
>>> >>
>>> >> What index's do you have set? This has a huge effect on query plans
>>> >> and most people have too many index's set. Make sure you only have
>>> >> these index's and then look at the query plan:
>>> >>
>>> >> CREATE INDEX MFR_UNREAD_STATUS_I2 USING BTREE
>>> >> ON MFR_UNREAD_STATUS_T(MESSAGE_C, USER_C, READ_C);
>>> >>
>>> >> CREATE INDEX MFR_UNREAD_STATUS_I1 USING BTREE
>>> >> ON MFR_UNREAD_STATUS_T(TOPIC_C, MESSAGE_C, USER_C, READ_C);
>>> >>
>>> >> CREATE UNIQUE INDEX PRIMARY USING BTREE
>>> >> ON MFR_UNREAD_STATUS_T(ID);
>>> >>
>>> >> ALTER TABLE MFR_UNREAD_STATUS_T
>>> >> ADD CONSTRAINT TOPIC_C
>>> >> UNIQUE (TOPIC_C, MESSAGE_C, USER_C);
>>> >
>>> > | MFR_UNREAD_STATUS_T | CREATE TABLE `mfr_unread_status_t` (
>>> > `ID` bigint(20) NOT NULL AUTO_INCREMENT,
>>> > `VERSION` int(11) NOT NULL,
>>> > `TOPIC_C` bigint(20) NOT NULL,
>>> > `MESSAGE_C` bigint(20) NOT NULL,
>>> > `USER_C` varchar(255) NOT NULL,
>>> > `READ_C` bit(1) NOT NULL,
>>> > PRIMARY KEY (`ID`),
>>> > UNIQUE KEY `TOPIC_C` (`TOPIC_C`,`MESSAGE_C`,`USER_C`),
>>> > KEY `MFR_UNREAD_STATUS_I2` (`MESSAGE_C`,`USER_C`,`READ_C`),
>>> > KEY `MFR_UNREAD_STATUS_I1` (`TOPIC_C`,`MESSAGE_C`,`USER_C`,`READ_C`)
>>> > USING BTREE
>>> > ) ENGINE=InnoDB AUTO_INCREMENT=5750248 DEFAULT CHARSET=utf8 |
>>> >
>>> >
>>> >>
>>> >> ALTER TABLE MFR_MESSAGE_T
>>> >> ADD CONSTRAINT FK80C1A316A2D0BE7B
>>> >> FOREIGN KEY(surrogateKey)
>>> >> REFERENCES MFR_TOPIC_T(ID);
>>> >>
>>> >
>>> > | MFR_MESSAGE_T | CREATE TABLE `mfr_message_t` (
>>> > `ID` bigint(20) NOT NULL AUTO_INCREMENT,
>>> > `MESSAGE_DTYPE` varchar(2) NOT NULL,
>>> > `VERSION` int(11) NOT NULL,
>>> > `UUID` varchar(36) NOT NULL,
>>> > `CREATED` datetime NOT NULL,
>>> > `CREATED_BY` varchar(36) NOT NULL,
>>> > `MODIFIED` datetime NOT NULL,
>>> > `MODIFIED_BY` varchar(36) NOT NULL,
>>> > `TITLE` varchar(255) NOT NULL,
>>> > `BODY` text,
>>> > `AUTHOR` varchar(255) NOT NULL,
>>> > `HAS_ATTACHMENTS` bit(1) NOT NULL,
>>> > `GRADEASSIGNMENTNAME` varchar(255) DEFAULT NULL,
>>> > `LABEL` varchar(255) DEFAULT NULL,
>>> > `IN_REPLY_TO` bigint(20) DEFAULT NULL,
>>> > `TYPE_UUID` varchar(36) NOT NULL,
>>> > `APPROVED` bit(1) DEFAULT NULL,
>>> > `DRAFT` bit(1) NOT NULL,
>>> > `surrogateKey` bigint(20) DEFAULT NULL,
>>> > `EXTERNAL_EMAIL` bit(1) DEFAULT NULL,
>>> > `EXTERNAL_EMAIL_ADDRESS` varchar(255) DEFAULT NULL,
>>> > `RECIPIENTS_AS_TEXT` text,
>>> > `DELETED` bit(1) NOT NULL DEFAULT b'0',
>>> > `NUM_READERS` int(11) DEFAULT NULL,
>>> > `THREADID` bigint(20) DEFAULT NULL,
>>> > `LASTTHREADATE` datetime DEFAULT NULL,
>>> > `LASTTHREAPOST` bigint(20) DEFAULT NULL,
>>> > PRIMARY KEY (`ID`),
>>> > KEY `FK80C1A31650339D56` (`IN_REPLY_TO`),
>>> > KEY `FK80C1A316A2D0BE7B` (`surrogateKey`),
>>> > KEY `MFR_MESSAGE_DELETED_I` (`DELETED`),
>>> > KEY `MFR_MESSAGE_T_IN_REPLY_TO_I` (`IN_REPLY_TO`),
>>> > KEY `mfrmessageindex2` (`CREATED_BY`),
>>> > CONSTRAINT `FK80C1A31650339D56` FOREIGN KEY (`IN_REPLY_TO`)
>>> > REFERENCES `mfr_message_t` (`ID`),
>>> > CONSTRAINT `FK80C1A316A2D0BE7B` FOREIGN KEY (`surrogateKey`)
>>> > REFERENCES `mfr_topic_t` (`ID`)
>>> > ) ENGINE=InnoDB AUTO_INCREMENT=1196102 DEFAULT CHARSET=utf8 |
>>> >
>>> >
>>> >>
>>> >> ALTER TABLE MFR_TOPIC_T
>>> >> ADD CONSTRAINT FK863DC0BE74C7E92B
>>> >> FOREIGN KEY(of_surrogateKey)
>>> >> REFERENCES MFR_OPEN_FORUM_T(ID);
>>> >>
>>> >
>>> > | MFR_TOPIC_T | CREATE TABLE `mfr_topic_t` (
>>> > `ID` bigint(20) NOT NULL AUTO_INCREMENT,
>>> > `TOPIC_DTYPE` varchar(2) NOT NULL,
>>> > `VERSION` int(11) NOT NULL,
>>> > `UUID` varchar(36) NOT NULL,
>>> > `CREATED` datetime NOT NULL,
>>> > `CREATED_BY` varchar(36) NOT NULL,
>>> > `MODIFIED` datetime NOT NULL,
>>> > `MODIFIED_BY` varchar(36) NOT NULL,
>>> > `DEFAULTASSIGNNAME` varchar(255) DEFAULT NULL,
>>> > `TITLE` varchar(255) NOT NULL,
>>> > `SHORT_DESCRIPTION` varchar(255) DEFAULT NULL,
>>> > `EXTENDED_DESCRIPTION` text,
>>> > `MODERATED` bit(1) NOT NULL,
>>> > `MUTABLE` bit(1) NOT NULL,
>>> > `SORT_INDEX` int(11) NOT NULL,
>>> > `TYPE_UUID` varchar(36) NOT NULL,
>>> > `of_surrogateKey` bigint(20) DEFAULT NULL,
>>> > `pf_surrogateKey` bigint(20) DEFAULT NULL,
>>> > `USER_ID` varchar(255) DEFAULT NULL,
>>> > `CONTEXT_ID` varchar(36) DEFAULT NULL,
>>> > `pt_surrogateKey` bigint(20) DEFAULT NULL,
>>> > `LOCKED` bit(1) DEFAULT NULL,
>>> > `DRAFT` bit(1) DEFAULT NULL,
>>> > `CONFIDENTIAL_RESPONSES` bit(1) DEFAULT NULL,
>>> > `MUST_RESPOND_BEFORE_READING` bit(1) DEFAULT NULL,
>>> > `HOUR_BEFORE_RESPONSES_VISIBLE` int(11) DEFAULT NULL,
>>> > `AUTO_MARK_THREADS_READ` bit(1) NOT NULL,
>>> > `AVAILABILITY_RESTRICTED` bit(1) NOT NULL DEFAULT b'0',
>>> > `AVAILABILITY` bit(1) NOT NULL DEFAULT b'1',
>>> > `OPEN_DATE` datetime DEFAULT NULL,
>>> > `CLOSE_DATE` datetime DEFAULT NULL,
>>> > PRIMARY KEY (`ID`),
>>> > KEY `FK863DC0BE82FAB29` (`pf_surrogateKey`),
>>> > KEY `FK863DC0BEFF3B3AE9` (`pt_surrogateKey`),
>>> > KEY `FK863DC0BE74C7E92B` (`of_surrogateKey`),
>>> > KEY `MFR_TOPIC_T_UUID_I` (`UUID`),
>>> > CONSTRAINT `FK863DC0BE74C7E92B` FOREIGN KEY (`of_surrogateKey`)
>>> > REFERENCES `mfr_open_forum_t` (`ID`),
>>> > CONSTRAINT `FK863DC0BE82FAB29` FOREIGN KEY (`pf_surrogateKey`)
>>> > REFERENCES `mfr_private_forum_t` (`ID`),
>>> > CONSTRAINT `FK863DC0BEFF3B3AE9` FOREIGN KEY (`pt_surrogateKey`)
>>> > REFERENCES `mfr_topic_t` (`ID`)
>>> > ) ENGINE=InnoDB AUTO_INCREMENT=574313 DEFAULT CHARSET=utf8 |
>>> >
>>> >
>>> >>
>>> >> ALTER TABLE MFR_OPEN_FORUM_T
>>> >> ADD CONSTRAINT FKC17608478B5E2A2F
>>> >> FOREIGN KEY(surrogateKey)
>>> >> REFERENCES MFR_AREA_T(ID);
>>> >>
>>> >
>>> > | MFR_OPEN_FORUM_T | CREATE TABLE `mfr_open_forum_t` (
>>> > `ID` bigint(20) NOT NULL AUTO_INCREMENT,
>>> > `FORUM_DTYPE` varchar(2) NOT NULL,
>>> > `VERSION` int(11) NOT NULL,
>>> > `UUID` varchar(36) NOT NULL,
>>> > `CREATED` datetime NOT NULL,
>>> > `CREATED_BY` varchar(36) NOT NULL,
>>> > `MODIFIED` datetime NOT NULL,
>>> > `MODIFIED_BY` varchar(36) NOT NULL,
>>> > `DEFAULTASSIGNNAME` varchar(255) DEFAULT NULL,
>>> > `TITLE` varchar(255) NOT NULL,
>>> > `SHORT_DESCRIPTION` varchar(255) DEFAULT NULL,
>>> > `EXTENDED_DESCRIPTION` text,
>>> > `TYPE_UUID` varchar(36) NOT NULL,
>>> > `SORT_INDEX` int(11) NOT NULL,
>>> > `LOCKED` bit(1) NOT NULL,
>>> > `DRAFT` bit(1) DEFAULT NULL,
>>> > `surrogateKey` bigint(20) DEFAULT NULL,
>>> > `MODERATED` bit(1) NOT NULL,
>>> > `AUTO_MARK_THREADS_READ` bit(1) NOT NULL,
>>> > `AVAILABILITY_RESTRICTED` bit(1) NOT NULL DEFAULT b'0',
>>> > `AVAILABILITY` bit(1) NOT NULL DEFAULT b'1',
>>> > `OPEN_DATE` datetime DEFAULT NULL,
>>> > `CLOSE_DATE` datetime DEFAULT NULL,
>>> > PRIMARY KEY (`ID`),
>>> > KEY `FKC17608478B5E2A2F` (`surrogateKey`),
>>> > CONSTRAINT `FKC17608478B5E2A2F` FOREIGN KEY (`surrogateKey`)
>>> > REFERENCES `mfr_area_t` (`ID`)
>>> > ) ENGINE=InnoDB AUTO_INCREMENT=17835 DEFAULT CHARSET=utf8 |
>>> >
>>> >
>>> >>
>>> >> ALTER TABLE MFR_AREA_T
>>> >> ADD CONSTRAINT MFR_AREA_CONTEXT_UUID_UNIQUE
>>> >> UNIQUE (CONTEXT_ID, TYPE_UUID);
>>> >>
>>> >
>>> > | MFR_AREA_T | CREATE TABLE `mfr_area_t` (
>>> > `ID` bigint(20) NOT NULL AUTO_INCREMENT,
>>> > `VERSION` int(11) NOT NULL,
>>> > `UUID` varchar(36) NOT NULL,
>>> > `CREATED` datetime NOT NULL,
>>> > `CREATED_BY` varchar(36) NOT NULL,
>>> > `MODIFIED` datetime NOT NULL,
>>> > `MODIFIED_BY` varchar(36) NOT NULL,
>>> > `CONTEXT_ID` varchar(255) NOT NULL,
>>> > `NAME` varchar(255) NOT NULL,
>>> > `HIDDEN` bit(1) NOT NULL,
>>> > `TYPE_UUID` varchar(36) NOT NULL,
>>> > `ENABLED` bit(1) NOT NULL,
>>> > `LOCKED` bit(1) NOT NULL,
>>> > `MODERATED` bit(1) NOT NULL,
>>> > `SENDEMAILOUT` bit(1) NOT NULL,
>>> > `AUTO_MARK_THREADS_READ` bit(1) NOT NULL,
>>> > `AVAILABILITY_RESTRICTED` bit(1) NOT NULL DEFAULT b'0',
>>> > `AVAILABILITY` bit(1) NOT NULL DEFAULT b'1',
>>> > `OPEN_DATE` datetime DEFAULT NULL,
>>> > `CLOSE_DATE` datetime DEFAULT NULL,
>>> > PRIMARY KEY (`ID`),
>>> > UNIQUE KEY `MFR_AREA_CONTEXT_UUID_UNIQUE` (`CONTEXT_ID`,`TYPE_UUID`),
>>> > KEY `CONTEXT_ID` (`CONTEXT_ID`(20))
>>> > ) ENGINE=InnoDB AUTO_INCREMENT=12227 DEFAULT CHARSET=utf8 |
>>> >
>>> >
>>> >>
>>> >> On Fri, Sep 2, 2011 at 4:15 PM, Earle Nietzel <earle.nietzel at gmail.com>
>>> >> wrote:
>>> >> > Here is the version from the pom:
>>> >> >
>>> >> > <name>Sakai Message Center Base POM</name>
>>> >> > <groupId>org.sakaiproject.msgcntr</groupId>
>>> >> > <version>2.8.0-rc04</version>
>>> >> > <artifactId>msgcntr</artifactId>
>>> >> > <packaging>pom</packaging>
>>> >> >
>>> >> > Earle
>>> >> >
>>> >> > On Fri, Sep 2, 2011 at 4:08 PM, Sam Ottenhoff
>>> >> > <ottenhoff at longsight.com>
>>> >> > wrote:
>>> >> >>
>>> >> >> What version of MsgCntr? Do you see the same issue on trunk
>>> >> >> MsgCntr?
>>> >> >> Note that MsgCntr 2.7.0 has major performance issues and should not
>>> >> >> be run
>>> >> >> in any type of serious production environment.
>>> >> >> --Sam
>>> >> >>
>>> >> >> On Fri, Sep 2, 2011 at 3:51 PM, Earle Nietzel
>>> >> >> <earle.nietzel at gmail.com>
>>> >> >> wrote:
>>> >> >>>
>>> >> >>> 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
>>> >> >>>
>>> >> >>> _______________________________________________
>>> >> >>> 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"
>>> >> >>
>>> >> >
>>> >> >
>>> >> > _______________________________________________
>>> >> > 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"
>>> >> >
>>> >
>>
>>
>
More information about the sakai-dev
mailing list