[Bug]: Inefficient comments table access

[ChristophWurst]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

I found queries like this in production:

SELECT
  *
FROM
  `oc_comments`
WHERE
  `object_type` = 'files'
ORDER BY
  `creation_timestamp` DESC,
  `id` DESC
LIMIT
  100 OFFSET 200

The query can use the index comments_object_index but only to filter for the object_type. The rest has to be fetched from the table. This is relatively expensive, because the query needs to scan 27x number of actually returned rows:

Moreover, the limit/offset doesn't scale. To retrieve 100 results with an offset of 200, 300 rows have to be built.

This is also a bug because as the data changes, queries might miss entries for new inserts or see overlaps for deleted rows.

Steps to reproduce

1. Install Nextcloud
2. ???

Expected behavior

This can be achieved by two changes

1. Change the query to use a timestamp for relative comparison of where the results should start
2. Add an index on (object_type, creation_timestamp, id)

This will allow the database to go to the start of the result directly without scanning rows that are not returned. Adding the id to the index helps Postgres and any other databases that use a heap table.

Nextcloud Server version

29

Operating system

None

PHP engine version

None

Web server

None

Database engine version

None

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

List of activated Apps

Nextcloud Signing status

Nextcloud Logs

Additional info

https://use-the-index-luke.com/sql/partial-results/fetch-next-page