[Bug]: Query on calendarobjects by classification takes too long as it is not indexed

[akhil1508]

⚠️ 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 (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• Nextcloud Server is running on 64bit capable CPU, PHP and OS.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

• Queries on the calendarobjects table getting rows by the classification column are taking too long
• One example is the query in this repair step
  • This query took up to a minute on a database with 1 million entries in the table and is done twice for each classification type in this repair step
  • After adding an index, it took only a few seconds to return rows
• Queries by this column are also done in multiple places in CalDavBackend.php
• As seen here and also confirmed by running a SELECT DISTINCT(classification) FROM calendarobjects;, I confirm that this column only stores the values 0, 1 and 2
  • Indexing should not very expensive even for huge tables - for 1 million rows, I saw an index size of 23MB

Steps to reproduce

1. On an instance with heavy usage of calendar/dav, run the occ maintenance:repair step
2. It should take quite long to run the repair step Remove activity entries of private events

Alternatively, to reproduce:

1. On an instance with heavy usage of calendar/dav
2. Login to your database console
3. Run the following queries:

• SELECT c.principaluri, o.calendarid, o.uid FROM calendarobjects o LEFT JOIN calendars c ON c.id = o.calendarid WHERE o.classification = 1;
• SELECT c.principaluri, o.calendarid, o.uid FROM calendarobjects o LEFT JOIN calendars c ON c.id = o.calendarid WHERE o.classification = 2;

5. Notice that it takes quite long for this query to run

Expected behavior

• Queries on the classification parameter should be quick

Installation method

Community Docker image

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.0

Web server

Nginx

Database engine version

MariaDB

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

No response

Are you using the Nextcloud Server Encryption module?

No response

What user-backends are you using?

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

Configuration report

No response

List of activated Apps

Enabled:
  - accessibility: 1.9.0
  - activity: 2.15.0
  - admin_audit: 1.13.0
  - bookmarks: 11.0.4
  - bruteforcesettings: 2.4.0
  - calendar: 3.5.2
  - carnet: 0.24.5
  - cloud_federation_api: 1.6.0
  - comments: 1.13.0
  - contacts: 4.2.1
  - cookbook: 0.9.17
  - dav: 1.21.0
  - deck: 1.6.3
  - drop_account: 2.0.0
  - encryption: 2.11.0
  - external: 3.10.2
  - federatedfilesharing: 1.13.0
  - files: 1.18.0
  - files_pdfviewer: 2.4.0
  - files_rightclick: 1.2.0
  - files_sharing: 1.15.0
  - files_trashbin: 1.13.0
  - files_videoplayer: 1.12.0
  - integration_google: 1.0.8
  - ldap_write_support: 1.4.0
  - logreader: 2.8.0
  - lookup_server_connector: 1.11.0
  - news: 19.0.0
  - notes: 4.5.1
  - notifications: 2.11.2
  - oauth2: 1.11.0
  - onlyoffice: 7.4.4
  - password_policy: 1.13.0
  - passwords: 2022.10.20
  - photos: 1.5.0
  - privacy: 1.7.0
  - provisioning_api: 1.13.0
  - quota_warning: 1.15.0
  - rainloop: 7.2.5
  - recommendations: 1.2.0
  - serverinfo: 1.13.0
  - settings: 1.5.0
  - sharebymail: 1.13.0
  - tasks: 0.14.4
  - text: 3.4.1
  - twofactor_admin: 3.2.0
  - twofactor_backupcodes: 1.12.0
  - twofactor_totp: 6.4.0
  - updatenotification: 1.13.0
  - user_ldap: 1.13.1
  - viewer: 1.7.0
  - workflowengine: 2.5.0
Disabled:
  - circles: 0.20.11
  - contactsinteraction: 1.3.0
  - dashboard: 7.3.0
  - federation: 1.9.0
  - files_external: 1.12.1
  - files_versions: 1.14.0
  - firstrunwizard: 2.7.0
  - nextcloud_announcements: 1.10.0
  - support: 1.4.0
  - survey_client: 1.6.0
  - systemtags: 1.9.0
  - theming: 1.12.0
  - user_status: 1.2.0
  - weather_status: 1.2.0

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

No response

[szaimen]

Which nc version?

[akhil1508]

Which nc version?

@szaimen I have tested on an NC 23 instance but I can confirm by inspecting the code that there is no index for this column in 24 and 25 either.

[kesselb]

cc @miaulalala @tcitworld

[tcitworld]

Sorry for the delay. To be fair I think we could just remove this repair job as it was added to fix an issue with activities almost 4 years ago in #13430. Is there a policy to remove such stuff @nickvergessen ?

Apart from the repair step, the classification criteria is only used for search methods in the backend, so it's possibly not the biggest performance issue in these cases.

Otherwise adding the index is fine to me.

[akhil1508]

@tcitworld In our database I still see ~40k entries that are pending repair with this repair job.