perf(core): Add index for jobs last_checked, reserved_at, time_sensitiveperf/core/jobs-index

Signed-off-by: provokateurin <kate@provokateurin.de>

2 files changed, 47 insertions, 2 deletions

diff --git a/core/Application.php b/core/Application.php
index d2bcb18bafb..770fdfb532d 100644
--- a/core/Application.php
+++ b/core/Application.php
@@ -191,8 +191,8 @@ class Application extends App {
 
 			$event->addMissingIndex(
 				'jobs',
-				'job_lastcheck_reserved',
-				['last_checked', 'reserved_at']
+				'job_last_reserved_sensitive',
+				['last_checked', 'reserved_at', 'time_sensitive']
 			);
 
 			$event->addMissingIndex(
diff --git a/core/Migrations/Version31000Date20240819122840.php b/core/Migrations/Version31000Date20240819122840.php
new file mode 100644
index 00000000000..7664c2a35ac
--- /dev/null
+++ b/core/Migrations/Version31000Date20240819122840.php
@@ -0,0 +1,45 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+
+namespace OC\Core\Migrations;
+
+use Closure;
+use OCP\DB\ISchemaWrapper;
+use OCP\Migration\IOutput;
+use OCP\Migration\SimpleMigrationStep;
+
+class Version31000Date20240819122840 extends SimpleMigrationStep {
+	/**
+	 * @param IOutput $output
+	 * @param Closure(): ISchemaWrapper $schemaClosure
+	 * @param array $options
+	 * @return null|ISchemaWrapper
+	 */
+	public function changeSchema(IOutput $output, Closure $schemaClosure, array $options): ?ISchemaWrapper {
+		/** @var ISchemaWrapper $schema */
+		$schema = $schemaClosure();
+
+		$table = $schema->getTable('jobs');
+
+		# Remove previous indices
+		if ($table->hasIndex('job_lastcheck_reserved')) {
+			$table->dropIndex('job_lastcheck_reserved');
+		}
+		if ($table->hasIndex('jobs_time_sensitive')) {
+			$table->dropIndex('jobs_time_sensitive');
+		}
+
+		# Add updated index
+		if (!$table->hasIndex('job_last_reserved_sensitive')) {
+			$table->addIndex(['last_checked', 'reserved_at', 'time_sensitive'], 'job_last_reserved_sensitive');
+		}
+
+		return $schema;
+	}
+}