[Building Sakai] MSGCNTR Forums mysql picks a slow query plan over a faster query plan
Anthony Whyte
arwhyte at umich.edu
Thu Sep 8 10:47:35 PDT 2011
You can assign it now (as well as be assigned tickets).
Cheers,
Anth
On Sep 8, 2011, at 1:35 PM, Earle Nietzel wrote:
> 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"
>>>>>>
>>>>
>>>>
>>
>>
> _______________________________________________
> 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