[Building Sakai] MSGCNTR Forums mysql picks a slow query plan over a faster query plan
Earle Nietzel
earle.nietzel at gmail.com
Thu Sep 8 10:35:01 PDT 2011
I have opened the following JIRA to track this issue:
https://jira.sakaiproject.org/browse/MSGCNTR-575
although I could not assign it to you Bryan.
Earle
On Wed, Sep 7, 2011 at 5:11 PM, Earle Nietzel <earle.nietzel at gmail.com> wrote:
> Completely agree.
> And I do believe that this is a mysql issue but that answer does not help
> the users.
> I have also learned that this is now happening at other institutions.
>
> I hope to have a patch forthcoming for this today.
>
> Earle
>
>
>
>
> On Wed, Sep 7, 2011 at 5:00 PM, Bryan Holladay <holladay at longsight.com>
> wrote:
>>
>> I understand and agree that it would be a safe guard against people
>> who didn't properly run the SQL conversions (assuming they are
>> correct), but the approach Sakai has used (and is a good approach), is
>> to not make specific queries for different types of query languages.
>> If there's a problem with the conversion script or the hibernate ddl,
>> then we should change those and not make an exception for this.
>>
>> Thanks,
>> Bryan
>>
>>
>>
>> On Wed, Sep 7, 2011 at 4:41 PM, Earle Nietzel <earle.nietzel at gmail.com>
>> wrote:
>> > Thank Bryan for checking on that.
>> >
>> > The goal there was to come up with the same query plan that MySQL
>> > selects on
>> > its own when it is working fine (which it looks like we did).
>> >
>> > The problem is that MySQL has been selecting a dodgy query plan which
>> > then
>> > kills the database server. The plans we were just discussing are what we
>> > expect (and want) to run. If you look at one of my previous posts you
>> > will
>> > see the plan that is bad and undesired. This where the straight_join
>> > comes
>> > in and says I will always run it this way no matter what.
>> >
>> > So I am working up a patch to do this.
>> >
>> > Can you look at my psuedo code from my first post and let me know if
>> > that
>> > looks like I am going in the right direction?
>> >
>> > Thanks,
>> > Earle
>> >
>> >
>> > On Wed, Sep 7, 2011 at 4:20 PM, Bryan Holladay <holladay at longsight.com>
>> > wrote:
>> >>
>> >> Yup... it gives the same result as whats in Msgcntr right now. It
>> >> looks like the only difference is you reordered the joins and added
>> >> "straight_join" (which only works for MySQL correct?). Anyways, I
>> >> don't see a need to change the code if the results are identical,
>> >> especially if it only works for MySQL.
>> >>
>> >> Thanks,
>> >> Bryan
>> >>
>> >> On Wed, Sep 7, 2011 at 4:14 PM, Earle Nietzel <earle.nietzel at gmail.com>
>> >> wrote:
>> >> > In one of my previous post I mentioned that one of the straight_join
>> >> > queries
>> >> > chooses the same fast plan when all is working well. I will include
>> >> > it
>> >> > here:
>> >> >
>> >> > 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
>> >> > This query should give the same exact performance as the one without
>> >> > a
>> >> > straight_join that works well.
>> >> >
>> >> > Can you give it a try?
>> >> >
>> >> > On Wed, Sep 7, 2011 at 3:28 PM, Bryan Holladay
>> >> > <holladay at longsight.com>
>> >> > wrote:
>> >> >>
>> >> >> You were right, I grabbed the wrong query... sorry about that... I
>> >> >> just copied your query in the email and ran explain and this is what
>> >> >> I
>> >> >> got:
>> >> >>
>> >> >> with straight_join:
>> >> >>
>> >> >> id select_type table type possible_keys key
>> >> >> key_len
>> >> >> ref rows Extra
>> >> >> 1 SIMPLE topicimpl0_ ALL PRIMARY,FK863DC0BE74C7E92B
>> >> >> (null) (null) (null) 589086 Using
>> >> >> where
>> >> >> 1 SIMPLE openforumi2_ eq_ref PRIMARY,FKC17608478B5E2A2F
>> >> >> PRIMARY 8 sakai28.topicimpl0_.of_surrogateKey 1
>> >> >> 1 SIMPLE areaimpl3_ eq_ref
>> >> >> PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE PRIMARY 8
>> >> >> sakai28.openforumi2_.surrogateKey 1 Using
>> >> >> where
>> >> >> 1 SIMPLE membership4_ ref FKE03761CB7DEF8466
>> >> >> FKE03761CB7DEF8466 9 sakai28.topicimpl0_.ID 6
>> >> >> Using
>> >> >> where
>> >> >> 1 SIMPLE permission1_ ref MFR_MODERATE_POSTINGS_IDX
>> >> >> MFR_MODERATE_POSTINGS_IDX 1 const 15486 Using
>> >> >> where
>> >> >>
>> >> >>
>> >> >> without straight_join:
>> >> >>
>> >> >> 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 FKE03761CB7DEF8466
>> >> >> FKE03761CB7DEF8466 9 sakai28.topicimpl0_.ID 6
>> >> >> Using
>> >> >> where
>> >> >> 1 SIMPLE permission1_ ref MFR_MODERATE_POSTINGS_IDX
>> >> >> MFR_MODERATE_POSTINGS_IDX 1 const 15486 Using
>> >> >> where
>> >> >>
>> >> >>
>> >> >>
>> >> >> So it still looks like it's faster without straight_join. I've
>> >> >> attached the csv file for easier reading.
>> >> >>
>> >> >>
>> >> >> On Wed, Sep 7, 2011 at 3:12 PM, Earle Nietzel
>> >> >> <earle.nietzel at gmail.com>
>> >> >> wrote:
>> >> >> > Thanks for the reply Brian.
>> >> >> >
>> >> >> > Everything in your query plan is the same except for the last
>> >> >> > line:
>> >> >> >
>> >> >> > | id | select_type | table | type | possible_keys
>> >> >> > |
>> >> >> > key | key_len |
>> >> >> > ref | rows | Extra
>> >> >> > | 1 | SIMPLE | permission1_ | ref |
>> >> >> > MFRPERMLVL_COMPOSITE_IDX1
>> >> >> > |
>> >> >> > MFRPERMLVL_COMPOSITE_IDX1 | 152 |
>> >> >> > ilearn.membership4_.PERMISSION_LEVEL_NAME | 10410 | Using where
>> >> >> >
>> >> >> > Are you looking the right query?
>> >> >> > Your query looks like
>> >> >> >
>> >> >> >
>> >> >> > "findNumModeratedTopicsForSiteByUserByMembershipWithPermissionLevelId"
>> >> >> > which
>> >> >> > would make sense since it is using the the PRIMARY key for that
>> >> >> > last
>> >> >> > line.
>> >> >> > Look at query
>> >> >> >
>> >> >> >
>> >> >> > "findNumModeratedTopicsForSiteByUserByMembershipWithPermissionLevelName"
>> >> >> > which uses the permissionLevelName so it can't be the PRIMARY key.
>> >> >> >
>> >> >> > Can you check this?
>> >> >> >
>> >> >> > Thanks
>> >> >> > Earle
>> >> >> >
>> >> >> > 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"
>> >> >> >> >> >> >
>> >> >> >> >> >
>> >> >> >> >
>> >> >> >> >
>> >> >> >
>> >> >> >
>> >> >
>> >> >
>> >> > _______________________________________________
>> >> > 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