From 020f7777246db96521030dc109c5fc480e33d4ab Mon Sep 17 00:00:00 2001
From: Fridolin Somers <fridolin.somers@biblibre.com>
Date: Tue, 22 Nov 2022 20:49:02 -1000
Subject: [PATCH] Bug 32330: Add indexes to table background_jobs

Table background_jobs should have indexes to optimize queries.

Query on borrowernumber :
mainpage.pl:    my $already_ran_jobs = Koha::BackgroundJobs->search(
mainpage.pl-        { borrowernumber => $logged_in_user->borrowernumber } )->count ? 1 : 0;

Query on status and queue :
misc/background_jobs_worker.pl:        my $jobs = Koha::BackgroundJobs->search({ status => 'new', queue => \@queues });

Test plan :
Run updatedatabase and play with background jobs

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
(cherry picked from commit 8755f775990bba513480a3b54372d76395ab2fbe)
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
(cherry picked from commit f2ec246fd6f16224a556e073e28479ca5104949e)

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
(cherry picked from commit a579ff4fe46d1c6a74942087a8aebdcb303829e6)
Signed-off-by: Arthur Suzuki <arthur.suzuki@biblibre.com>
---
 .../data/mysql/atomicupdate/bug_32330.pl      | 25 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  5 +++-
 2 files changed, 29 insertions(+), 1 deletion(-)
 create mode 100755 installer/data/mysql/atomicupdate/bug_32330.pl

diff --git a/installer/data/mysql/atomicupdate/bug_32330.pl b/installer/data/mysql/atomicupdate/bug_32330.pl
new file mode 100755
index 0000000000..90d3c9a960
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_32330.pl
@@ -0,0 +1,25 @@
+use Modern::Perl;
+
+return {
+    bug_number => "32330",
+    description => "Add indexes to background_jobs table",
+    up => sub {
+        my ($args) = @_;
+        my ($dbh, $out) = @$args{qw(dbh out)};
+        unless ( index_exists('background_jobs', 'borrowernumber') ) {
+            $dbh->do(q{
+                ALTER TABLE `background_jobs` ADD INDEX `borrowernumber` (`borrowernumber`)
+            });
+        }
+        unless ( index_exists('background_jobs', 'queue') ) {
+            $dbh->do(q{
+                ALTER TABLE `background_jobs` ADD INDEX `queue` (`queue`)
+            });
+        }
+        unless ( index_exists('background_jobs', 'status') ) {
+            $dbh->do(q{
+                ALTER TABLE `background_jobs` ADD INDEX `status` (`status`)
+            });
+        }
+    },
+};
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 1fd1be4220..d6f2a7c3c6 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -984,7 +984,10 @@ CREATE TABLE `background_jobs` (
   `enqueued_on` datetime DEFAULT NULL,
   `started_on` datetime DEFAULT NULL,
   `ended_on` datetime DEFAULT NULL,
-  PRIMARY KEY (`id`)
+  PRIMARY KEY (`id`),
+  KEY `borrowernumber` (`borrowernumber`),
+  KEY `queue` (`queue`),
+  KEY `status` (`status`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
-- 
2.39.5