Problem Definition

Lot of Content search queries as below are issued when accessed via REST content api.

select abstractpa0_.CONTENTID as CONTENTI1_12_, .... from CONTENT abstractpa0_ 
... limit 500, 501;
select abstractpa0_.CONTENTID as CONTENTI1_12_, .... from CONTENT abstractpa0_ 
... limit 1000, 501;
select abstractpa0_.CONTENTID as CONTENTI1_12_, .... from CONTENT abstractpa0_ 
... limit 1500, 501;
.....
select abstractpa0_.CONTENTID as CONTENTI1_12_, .... from CONTENT abstractpa0_ 
... limit 10000, 501;

How to replicate this problem

There are at least two types of scenario to cause this problem –
Scenario 1

1. Create a Confluence Instance with many content. e.g. 20,000 content.
2. Create a user who has access permission to the contents created in step 1. e.g. testuser
3. Clear cache
4. Send a GET request with rest/api/content?start=<any number over 500> to the instance. e.g. start from 10,000

   curl -u testuser:testuser http://<confluence-base-url>/rest/api/content?start=10000
   

i.e. Confluence tries to fetch all 10,500 contents to obtain just 500 contents from 10,001st to 10,500th items.

Scenario 2

1. Create a Confluence Instance with many content. e.g. 20,000 content.
2. Create a user who doesn't have access permission to the contents created in step 1. e.g. testuser
3. Clear cache
4. Send a GET request with rest/api/content to the instance.

   curl -u testuser:testuser http://<confluence-base-url>/rest/api/content
   

i.e. Confluence tries to fetch all 20,000 contents to filter the contents on which the testuser has permission.

Suggested Solution

For scenario 1, to obtain data for rest/api/content?start=10000 , only the query

select abstractpa0_.CONTENTID as CONTENTI1_12_, .... from CONTENT abstractpa0_ 
... limit 10000, 501;

should be issued.

For scenario 2, filter the CONTENT first with permission and fetch the items later.

Why this is important

Because this can cause a performance problem on database server processing a lot of queries at the same time.