From 933fc8ba12bdb1baac812832933d8b1f9a7df694 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 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 --- .../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 d831006cd5..59607073ca 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -987,7 +987,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.20.1