[Bug]: Missing column in database index on query for time sensitive background jobs · nextcloud/server#46126

(2 comments) (0 reactions) (0 assignees)PHP (34,953 stars) (4,865 forks)batch import

1. to develop29-feedbackbuggood first issueperformance 🚀

Description

⚠️ 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

The query 4f0fc707a2e82ec699067a969cd17b05

SELECT
  *
FROM
  `oc_jobs`
WHERE
  (`reserved_at` <= ?)
  AND (`last_checked` <= ?)
  AND (`time_sensitive` = ?)
ORDER BY
  `last_checked` ASC
LIMIT
  ?

does not use a covering index. The index job_lastcheck_reserved only covers last_checked and reserved_at, not time_sensitive. The database has to scan a bunch of rows every time cron picks the next job outside the maintenance window:

Steps to reproduce

Install Nextcloud
Configure a maintenance window

Expected behavior

An index on (last_checked, reserved_at, time_sensitive)

Installation method

None

Nextcloud Server version

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

No response

List of activated Apps

No response

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

cc @nickvergessen

Contributor guide

Tech stack: phpsql
Domain: backenddatabaseperformance
Issue type: bug
Difficulty: 3
Estimated time: 1-3 hours
Activity status: fresh
Clarity: clear
Prerequisites: basic SQLdatabase indexing concepts
Newbie friendliness: 60
Research direction: Investigate the database schema to locate the existing index 'job lastcheck reserved' on the 'oc jobs' table. Then create a database migration to add the 'time sensitive' column to the index. Refer to the migration files in 'core/Migrations/'. Also check the query in 'lib/private/BackgroundJob/JobList.php' to confirm the index usage.