[Building Sakai] sakai 2.9.3: forums querying over 200 million rows?

[Kenwrick Chan]

Folks,
Our DB expressed concern that this query (forums I think) is consuming
resources querying over 200 million rows?  Any suggestions or does this
look familiar as to something that's addressed as a patch?

# Time: 150107 10:32:36
# User at Host: manof[manof] @ manof.mgt.hawaii.edu [10.2.1.212]
# Query_time: 474.222131  Lock_time: 0.000287 Rows_sent: 0  Rows_examined:
228711446
SET timestamp=1420662756;
select messageimp0_.ID as ID73_0_, topicimpl2_.ID as ID72_1_,
openforumi3_.ID as ID75_2_, messageimp0_.VERSION as VERSION73_0_,
messageimp0_.UUID as UUID73_0_, messageimp0_.CREATED as CREATED73_0_,
messageimp0_.CREATED_BY as CREATED6_73_0_, messageimp0_.MODIFIED as
MODIFIED73_0_, messageimp0_.MODIFIED_BY as MODIFIED8_73_0_,
messageimp0_.TITLE as TITLE73_0_, messageimp0_.BODY as BODY73_0_,
messageimp0_.AUTHOR as AUTHOR73_0_, messageimp0_.HAS_ATTACHMENTS as
HAS12_73_0_, messageimp0_.GRADEASSIGNMENTNAME as GRADEAS13_73_0_,
messageimp0_.NUM_READERS as NUM14_73_0_, messageimp0_.LABEL as LABEL73_0_,
messageimp0_.IN_REPLY_TO as IN16_73_0_, messageimp0_.THREADID as
THREADID73_0_, messageimp0_.LASTTHREADATE as LASTTHR18_73_0_,
messageimp0_.LASTTHREAPOST as LASTTHR19_73_0_, messageimp0_.TYPE_UUID as
TYPE20_73_0_, messageimp0_.APPROVED as APPROVED73_0_, messageimp0_.DRAFT as
DRAFT73_0_, messageimp0_.DELETED as DELETED73_0_, messageimp0_.surrogateKey
as surroga24_73_0_, messageimp0_.EXTERNAL_EMAIL as EXTERNAL25_73_0_,
messageimp0_.EXTERNAL_EMAIL_ADDRESS as EXTERNAL26_73_0_,
messageimp0_.RECIPIENTS_AS_TEXT as RECIPIENTS27_73_0_,
messageimp0_.RECIPIENTS_AS_TEXT_BCC as RECIPIENTS28_73_0_,
messageimp0_.MESSAGE_DTYPE as MESSAGE2_73_0_, topicimpl2_.VERSION as
VERSION72_1_, topicimpl2_.UUID as UUID72_1_, topicimpl2_.CREATED as
CREATED72_1_, topicimpl2_.CREATED_BY as CREATED6_72_1_,
topicimpl2_.MODIFIED as MODIFIED72_1_, topicimpl2_.MODIFIED_BY as
MODIFIED8_72_1_, topicimpl2_.DEFAULTASSIGNNAME as DEFAULTA9_72_1_,
topicimpl2_.TITLE as TITLE72_1_, topicimpl2_.SHORT_DESCRIPTION as
SHORT11_72_1_, topicimpl2_.EXTENDED_DESCRIPTION as EXTENDED12_72_1_,
topicimpl2_.MODERATED as MODERATED72_1_, topicimpl2_.POST_FIRST as
POST14_72_1_, topicimpl2_.AUTO_MARK_THREADS_READ as AUTO15_72_1_,
topicimpl2_.MUTABLE as MUTABLE72_1_, topicimpl2_.SORT_INDEX as
SORT17_72_1_, topicimpl2_.TYPE_UUID as TYPE18_72_1_,
topicimpl2_.AVAILABILITY_RESTRICTED as AVAILAB19_72_1_,
topicimpl2_.AVAILABILITY as AVAILAB20_72_1_, topicimpl2_.OPEN_DATE as
OPEN21_72_1_, topicimpl2_.CLOSE_DATE as CLOSE22_72_1_,
topicimpl2_.of_surrogateKey as of23_72_1_, topicimpl2_.pf_surrogateKey as
pf24_72_1_, topicimpl2_.USER_ID as USER25_72_1_, topicimpl2_.CONTEXT_ID as
CONTEXT26_72_1_, topicimpl2_.pt_surrogateKey as pt27_72_1_,
topicimpl2_.LOCKED as LOCKED72_1_, topicimpl2_.DRAFT as DRAFT72_1_,
topicimpl2_.CONFIDENTIAL_RESPONSES as CONFIDE30_72_1_,
topicimpl2_.MUST_RESPOND_BEFORE_READING as MUST31_72_1_,
topicimpl2_.HOUR_BEFORE_RESPONSES_VISIBLE as HOUR32_72_1_,
topicimpl2_.TOPIC_DTYPE as TOPIC2_72_1_, openforumi3_.VERSION as
VERSION75_2_, openforumi3_.UUID as UUID75_2_, openforumi3_.CREATED as
CREATED75_2_, openforumi3_.CREATED_BY as CREATED6_75_2_,
openforumi3_.MODIFIED as MODIFIED75_2_, openforumi3_.MODIFIED_BY as
MODIFIED8_75_2_, openforumi3_.DEFAULTASSIGNNAME as DEFAULTA9_75_2_,
openforumi3_.TITLE as TITLE75_2_, openforumi3_.SHORT_DESCRIPTION as
SHORT11_75_2_, openforumi3_.EXTENDED_DESCRIPTION as EXTENDED12_75_2_,
openforumi3_.TYPE_UUID as TYPE13_75_2_, openforumi3_.SORT_INDEX as
SORT14_75_2_, openforumi3_.LOCKED as LOCKED75_2_, openforumi3_.DRAFT as
DRAFT75_2_, openforumi3_.AVAILABILITY_RESTRICTED as AVAILAB17_75_2_,
openforumi3_.AVAILABILITY as AVAILAB18_75_2_, openforumi3_.OPEN_DATE as
OPEN19_75_2_, openforumi3_.CLOSE_DATE as CLOSE20_75_2_,
openforumi3_.surrogateKey as surroga21_75_2_, openforumi3_.MODERATED as
MODERATED75_2_, openforumi3_.AUTO_MARK_THREADS_READ as AUTO23_75_2_,
openforumi3_.POST_FIRST as POST24_75_2_, openforumi3_.FORUM_DTYPE as
FORUM2_75_2_ from MFR_MESSAGE_T messageimp0_ inner join MFR_TOPIC_T
topicimpl2_ on messageimp0_.surrogateKey=topicimpl2_.ID inner join
MFR_OPEN_FORUM_T openforumi3_ on
topicimpl2_.of_surrogateKey=openforumi3_.ID inner join MFR_AREA_T
areaimpl4_ on openforumi3_.surrogateKey=areaimpl4_.ID inner join
MFR_MEMBERSHIP_ITEM_T membership5_ on
topicimpl2_.ID=membership5_.t_surrogateKey, MFR_PERMISSION_LEVEL_T
permission1_ where areaimpl4_.CONTEXT_ID='MAN.3274.201533' and
(membership5_.NAME in ('Instructor')) and messageimp0_.DELETED=0 and
(messageimp0_.APPROVED is null) and
permission1_.TYPE_UUID<>'00000000-0000-0000-7777-000000000000' and
permission1_.NAME=membership5_.PERMISSION_LEVEL_NAME and
permission1_.MODERATE_POSTINGS=1
LIMIT 0, 1000;
-- 

Thanks,
kenwrick
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20150107/79a0629c/attachment.html