Attachment 144186 Details for Bug 32330 – Bug 32330: Add indexes to table background_jobs

[patch] Bug 32330: Add indexes to table background_jobs

Bug-32330-Add-indexes-to-table-backgroundjobs.patch (text/plain), 2.62 KB, created by Fridolin Somers on 2022-11-23 07:08:07 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Fridolin Somers

Created: 2022-11-23 07:08:07 UTC

Size: 2.62 KB

patch

obsolete

>From 6350af1ca663e8292d96d992682ce7c3ab1104df 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
>---
> .../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 6d343fd56f..d04f0f598c 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -1041,7 +1041,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.35.5

Actions: View | Diff | Splinter Review

Attachments on bug 32330: 144184 | 144185 | 144186 | 144390 | 144493 | 144638