From d1daef08102ff1cd299e4b8830145f25fae10e55 Mon Sep 17 00:00:00 2001 From: Fridolin Somers 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: 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 | 20 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 4 +++- 2 files changed, 23 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..d405f969c6 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_32330.pl @@ -0,0 +1,20 @@ +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', '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..ce61e5816e 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1041,7 +1041,9 @@ 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 `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- 2.35.5