From 8d4e834290dc076d7bdc105fc38c5dea257df0a4 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 27 Feb 2024 09:58:03 -0500 Subject: [PATCH] Bug 36178: Add index for status on message queue table For large libraries, the query to gather pending notices from the message queue can take a very long time. ByWater's Rackpace DBA Mike was kind enough to do some work on this for us and found the issue. He recommends adding a simple index on the status column to fix this issue. Test Plan: 1) Apply this patch 2) Run updatedatabase.pl 3) SHOW CREATE TABLE message_queue should show the new index via koha-mysql --- installer/data/mysql/atomicupdate/bug_36178.pl | 17 +++++++++++++++++ installer/data/mysql/kohastructure.sql | 1 + 2 files changed, 18 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bug_36178.pl diff --git a/installer/data/mysql/atomicupdate/bug_36178.pl b/installer/data/mysql/atomicupdate/bug_36178.pl new file mode 100755 index 00000000000..1af16943d08 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_36178.pl @@ -0,0 +1,17 @@ +use Modern::Perl; + +return { + bug_number => "36178", + description => "Add index for status on message queue table", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + unless ( index_exists( 'message_queue', 'language_subtag' ) ) { + $dbh->do(q{ + ALTER TABLE message_queue ADD INDEX idx_mq_status (`status`) + }); + say $out "Added new index 'idx_mq_status' on table 'message_queue'"; + } + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 16310f0f449..a739f37f65b 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4647,6 +4647,7 @@ CREATE TABLE `message_queue` ( KEY `borrowernumber` (`borrowernumber`), KEY `message_transport_type` (`message_transport_type`), KEY `letter_fk` (`letter_id`), + INDEX idx_mq_status (`status`), CONSTRAINT `letter_fk` FOREIGN KEY (`letter_id`) REFERENCES `letter` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON UPDATE CASCADE -- 2.30.2