We have observed the indexing issue in 5.9.8 as well with mysql 5.5 version 

Dear All,

Just for your information, our developer is working on a solution for this problem across databases at this ticket https://jira.atlassian.com/browse/CONF-41010

I am closing this ticket. Please follow the progress in this ticket https://jira.atlassian.com/browse/CONF-41010

Best Regards,
Minh Tran
Confluence BugMaster

Worked for us too. Just upgraded our test server to MySQL 5.6.34 two days ago and the index rebuild on a 5.9.3 environment went from 3+ hours to jsut over 5 minutes.

In our case, upgrading from mySql 5.5 to mySql 5.6 solved the problem. In our test environment (with low resources), reindexing the same database took 1 hour with mySql 5.6 – as opposed to some 48 hours with mySql 5.5. So it should take around 20-30 min. in our production environment.

Confluence version was 5.10.6 on Linux RedHat 6.8. The database size is 3.6 GB.

Hi Denise!
I forwarded this information last year via support issue, but no one could help me on this
Now, what I can tell you is that the order of the actual execution plan of the mentioned SQL statement has changed. We did it like this:

• I started the search index rebuild.
• Our DB admin did some analysis on long running statements and found exactly the one I mentioned above in my comment.
• With help of the tool Oracle 'SQL Tuning Advisor' he was able to change the execution plan of the statement
• Before it looked like this
• After the tuning was done, it look like this

I hope this will help you on your investigations.

Thanks for letting us know Jens! Would you be able to provide us with the old and new execution plans for that statement? I'd like to see how it was fixed.

Hi Denise!
Dropping the c_status_idx index from the CONTENT table did not work in our case. This is why I created the comment above.

Thanks everyone for your comments! It's possible that this issue is related to CONF-41010. There is a workaround in that ticket to drop a database index, which has resolved the slow indexing for some customers. If you are affected by this issue on MySQL 5.5, you can try dropping the c_status_idx index from the CONTENT table.

The impact that dropping this index may have is that retrieving drafts and trashed pages/attachments may be a little slower.

Please do let us know if this resolves the issue for you.

Same issue on Oracle 11 and Oracle 12c for Confluence 5.8.18 and Confluence 5.9.10.

We had this issue also earlier with a previous release of Confluence. We were only able to resolve this by tuning the order of sql statements. Our DB admin helped us to change the execution plan of this statement:
select confluence1.user_key as user_key0_, confluence1_.user_key as user_key1_, confluence1_.username as username0_, confluence1_.lower_username as lower_us3_0_, confluence1_.username as username1_, confluence1_.lower_username as lower_us3_1_, contentent0_.CONTENTID as x0_0_, contentent0_.VERSION as x1_0_, confluence1_.user_key as x2_0_, contentent0_.LASTMODDATE as x3_0_, contentent0_.VERSIONCOMMENT as x4_0_ from CONTENT contentent0_ left outer join user_mapping confluence1_ on contentent0_.LASTMODIFIER=confluence1_.user_key where (contentent0_.PREVVER=:1 )or(contentent0_.CONTENTID=:2 )and(contentent0_.CONTENT_STATUS='current' ) order by contentent0_.VERSION desc_

@Atlassian: This issue is a blocker for larger instances since you are not able to create a search index if you experience this issue!

same with 5.9.12 running MySQL 5.5.38 with mysql-connector-java-5.1.39

update: as i'm working on dev instance, i stopped confluence, dropped the index referenced in CONF-41010 and restart indexing. Significant improvement show below. Unfortunately no details are provided on the impact to other application functionality of removing the c_status_idx index, so i cant really proceed with upgrade to 5.9 in our production installation.

Original:
(Elapsed Time: 41:16:25) 14%
15045 / 103623

after
mysql> DROP INDEX c_status_idx ON CONTENT;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

restarted indexing
(Elapsed Time: 00:59:30) 24%
25174 / 103640