Details
-
Bug
-
Resolution: Fixed
-
Medium
-
No-Version
-
Severity 3 - Minor
-
Description
NOTE: This bug report is for Confluence Server. Using Confluence Cloud? See the corresponding bug report.
On EAC we see this particular CQ query in the slow query logs very frequently:
2015-09-17 04:57:44 GMT LOG: duration: 77570.006 ms execute <unnamed>: select label1_.LABELID as x0_0_, customcont2_.TITLE as x1_0_ from CONTENT_LABEL labelling0_, LABEL label1_, CONTENT customcont2_ where customcont2_.CONTENTTYPE='CUSTOM' and ((labelling0_.CONTENTID=customcont2_.CONTENTID )AND(customcont2_.PLUGINKEY=$1 )AND(customcont2_.CONTENT_STATUS='current' )AND(label1_.LABELID=labelling0_.LABELID )AND(label1_.LABELID IN($2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 , $10))AND(labelling0_.LASTMODDATE=(select max(labelling3_.LASTMODDATE) from CONTENT_LABEL labelling3_, LABEL label4_, CONTENT customcont5_ where customcont5_.CONTENTTYPE='CUSTOM' and ((labelling3_.CONTENTID=customcont5_.CONTENTID )AND(customcont5_.CONTENT_STATUS='current' )AND(customcont5_.PLUGINKEY=$11 )AND(label4_.LABELID=labelling3_.LABELID )AND(labelling3_.LABELID=labelling0_.LABELID )) group by label4_.LABELID , label4_.NAMESPACE , label4_.OWNER , label4_.NAME , label4_.LASTMODDATE , label4_.CREATIONDATE))) order by label1_.LABELID DESC 2015-09-17 04:57:44 GMT DETAIL: parameters: $1 = 'com.atlassian.confluence.plugins.confluence-questions:question', $2 = '148', $3 = '525041667', $4 = '14', $5 = '1868365862', $6 = '119', $7 = '1971224595', $8 = '139', $9 = '1901166616', $10 = '602013786', $11 = 'com.atlassian.confluence.plugins.confluence-questions:question'
As you can see, this instance took 77 seconds to compute!
The purpose of this query, as far as I can determine, is for each of the given question topics, get the question which last added to that topic.
The subquery (which finds the latest modification date per topic) is extremely slow to compute. A better way would be to do another join using an inequality, like described here: http://stackoverflow.com/a/14841015
Attachments
Issue Links
- is related to
-
CONFSERVER-47432 Improve performance of slow label query
- Closed
- relates to
-
CONFCLOUD-46669 Extremely slow CQ query on EAC
- Closed