Since the Upgrade from 4.1.9 to 4.2.2 during the weekend, we suffer bad performance on the Popular Label Macro.
Having looked at it from the mysql side, the SQL Query which is executed by the Macro is very slow:
mysql> select label5_.LABELID as LABELID, label5_.NAME as NAME, label5_.OWNER as OWNER, label5_.NAMESPACE as NAMESPACE, label5_.CREATIONDATE as CREATION5_, label5_.LASTMODDATE as LASTMODD6_, label5_.LABELID as x0_0_, count(label5_.LABELID) as x1_0_ from CONTENT spaceconte0_, SPACES space1_, CONTENT_LABEL labelling2_ left outer join ATTACHMENTS attachment3_ on labelling2_.ATTACHMENTID=attachment3_.ATTACHMENTID left outer join CONTENT contentent4_ on labelling2_.CONTENTID=contentent4_.CONTENTID, LABEL label5_ where spaceconte0_.CONTENTTYPE in ('com.atlassian.confluence.pages.AbstractPage', 'USERSTATUS', 'PAGE', 'BLOGPOST', 'CUSTOM', 'MAIL', 'SPACEDESCRIPTION', 'com.atlassian.confluence.core.SpaceContentEntityObject') and labelling2_.LABELID=label5_.LABELID and ((label5_.NAMESPACE='global' and labelling2_.LABELID=label5_.LABELID)and((contentent4_.CONTENTID=spaceconte0_.CONTENTID )or(attachment3_.PAGEID=spaceconte0_.CONTENTID ))and(spaceconte0_.SPACEID=space1_.SPACEID )and(lower(space1_.SPACEKEY)='knowledgebase' )and((attachment3_.ATTACHMENTID is not null )or(contentent4_.CONTENT_STATUS!='deleted' ))) group by label5_.LABELID , label5_.NAMESPACE , label5_.OWNER , label5_.NAME , label5_.LASTMODDATE , label5_.CREATIONDATE order by count(label5_.LABELID)DESC limit 5;
+----------+-------+-------+-----------+---------------------+---------------------+----------+-------+
| LABELID | NAME | OWNER | NAMESPACE | CREATION5_ | LASTMODD6_ | x0_0_ | x1_0_ |
+----------+-------+-------+-----------+---------------------+---------------------+----------+-------+
| 3211278 | howto | NULL | global | 2011-11-23 12:54:13 | 2011-11-23 12:54:13 | 3211278 | 14 |
| 6619144 | cucm | NULL | global | 2011-11-30 15:32:19 | 2011-11-30 15:32:19 | 6619144 | 10 |
| 4685827 | cisco | NULL | global | 2011-11-25 19:07:52 | 2011-11-25 19:07:52 | 4685827 | 7 |
| 8290306 | uccx | NULL | global | 2011-12-05 22:43:31 | 2011-12-05 22:43:31 | 8290306 | 7 |
| 17465346 | ios | NULL | global | 2012-02-29 09:24:29 | 2012-02-29 09:24:29 | 17465346 | 6 |
+----------+-------+-------+-----------+---------------------+---------------------+----------+-------+
5 rows in set *(53.69 sec)*
mysql>
As you can see - needing 54 sec to complete the Query.
Other than that we don't really expierience any performance Issues with our Database.
Please advise.
