[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:41:56 PDT 2011
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"
>> >> >
>> >
>
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: explain.csv
Type: text/csv
Size: 812 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20110907/2e40b6da/attachment.bin
More information about the sakai-dev
mailing list