[Bug]: Missing column in database index on query for time sensitive background jobs

[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

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

1. Install Nextcloud
2. Configure a maintenance window

Expected behavior

An index on (last_checked, reserved_at, time_sensitive)

Installation method

None

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

No response

List of activated Apps

No response

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

cc @nickvergessen

[nickvergessen]

Sounds good

[ChristophWurst]

An index on (last_checked, reserved_at, time_sensitive)

Due to data locality in the concatenated index it will be better to index for equality before the ranges:

create index job_sensitive
 on nextclouddev.oc_jobs (time_sensitive, last_checked, reserved_at);

https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/greater-less-between-tuning-sql-access-filter-predicates

ANALYZE SELECT
 *
 FROM
 `oc_jobs`
 WHERE
 (`reserved_at` <= UNIX_TIMESTAMP()-12*3600)
 AND (`last_checked` <= UNIX_TIMESTAMP())
 AND time_sensitive = 0
 ORDER BY
 `last_checked` ASC
 LIMIT
 1;

Before:

{
 "query_optimization": {
 "r_total_time_ms": 0.198072572
 },
 "query_block": {
 "select_id": 1,
 "cost": 0.0909634,
 "r_loops": 1,
 "r_total_time_ms": 0.102750711,
 "nested_loop": [
 {
 "table": {
 "table_name": "oc_jobs",
 "access_type": "range",
 "possible_keys": ["job_lastcheck_reserved", "jobs_time_sensitive"],
 "key": "job_lastcheck_reserved",
 "key_length": "10",
 "used_key_parts": ["last_checked", "reserved_at"],
 "loops": 1,
 "r_loops": 1,
 "rows": 473,
 "r_rows": 15,
 "cost": 0.0909634,
 "r_table_time_ms": 0.078428999,
 "r_other_time_ms": 0.012882687,
 "r_engine_stats": {
 "pages_accessed": 45
 },
 "filtered": 35.72938538,
 "r_total_filtered": 6.666666667,
 "attached_condition": "oc_jobs.time_sensitive = 0 and oc_jobs.reserved_at <= 1734331248 and oc_jobs.last_checked <= 1734374448",
 "r_filtered": 6.666666667
 }
 }
 ]
 }
}

After:

{
 "query_optimization": {
 "r_total_time_ms": 0.131674098
 },
 "query_block": {
 "select_id": 1,
 "cost": 0.0909634,
 "r_loops": 1,
 "r_total_time_ms": 0.025374383,
 "nested_loop": [
 {
 "table": {
 "table_name": "oc_jobs",
 "access_type": "range",
 "possible_keys": [
 "job_lastcheck_reserved",
 "jobs_time_sensitive",
 "job_sensitive"
 ],
 "key": "job_sensitive",
 "key_length": "12",
 "used_key_parts": ["time_sensitive", "last_checked", "reserved_at"],
 "loops": 1,
 "r_loops": 1,
 "rows": 169,
 "r_rows": 1,
 "cost": 0.0909634,
 "r_table_time_ms": 0.011920245,
 "r_other_time_ms": 0.005975161,
 "r_engine_stats": {
 "pages_accessed": 3
 },
 "filtered": 100,
 "r_total_filtered": 100,
 "attached_condition": "oc_jobs.time_sensitive = 0 and oc_jobs.reserved_at <= 1734331270 and oc_jobs.last_checked <= 1734374470",
 "r_filtered": 100
 }
 }
 ]
 }
}

-> only 38% of the previous rows predicted to be scanned by the plan. On my dev instance the scan went down from 15 to 1 row (r_rows). A comparison on production would be interesting.