From f242bee29906d44cc9f529f51441f9bdcf98a633 Mon Sep 17 00:00:00 2001 From: Fridolin Somers Date: Thu, 8 Feb 2024 10:25:12 +0100 Subject: [PATCH] Bug 36033: Add more indexes to table pseudonymized_transactions Content-Type: text/plain; charset=utf-8 Table pseudonymized_transactions contains : KEY `pseudonymized_transactions_ibfk_1` (`categorycode`), KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`), KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`) To improve SQL queries performance, it needs more indexes, specially on itemnumber. Looking at table statistics : KEY `timeidx` (`datetime`), KEY `branch_idx` (`branch`), KEY `type_idx` (`type`), KEY `itemnumber_idx` (`itemnumber`), So index is need on pseudonymized_transactions columns : itemnumber => For join with table items transaction_type => For filter on type issue, return ... datetime => For filter on date, this will help cleanup script Test plan : 1) Run updatedatabase.pl 2) Check indexes are created in table pseudonymized_transactions 3) Run SQL query : describe select * from pseudonymized_transactions join items using(itemnumber) where transaction_type='issue' and datetime < date_sub(curdate(), INTERVAL 30 DAY) => You see the 3 new indexes used in 'possible_keys'. Signed-off-by: David Nind Signed-off-by: Marcel de Rooy --- .../data/mysql/atomicupdate/bug_36033.pl | 27 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 5 +++- 2 files changed, 31 insertions(+), 1 deletion(-) create mode 100755 installer/data/mysql/atomicupdate/bug_36033.pl diff --git a/installer/data/mysql/atomicupdate/bug_36033.pl b/installer/data/mysql/atomicupdate/bug_36033.pl new file mode 100755 index 0000000000..0bc077a0ee --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_36033.pl @@ -0,0 +1,27 @@ +use Modern::Perl; + +return { + bug_number => "36033", + description => "Add more indexes to pseudonymized_transactions", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + unless ( index_exists( 'pseudonymized_transactions', 'pseudonymized_transactions_items_ibfk_4' ) ) { + $dbh->do( + q{ALTER TABLE `pseudonymized_transactions` ADD INDEX `pseudonymized_transactions_items_ibfk_4` (`itemnumber`)} + ); + say $out "Added new index on pseudonymized_transactions.itemnumber"; + } + unless ( index_exists( 'pseudonymized_transactions', 'pseudonymized_transactions_ibfk_5' ) ) { + $dbh->do( + q{ALTER TABLE `pseudonymized_transactions` ADD INDEX `pseudonymized_transactions_ibfk_5` (`transaction_type`)} + ); + say $out "Added new index on pseudonymized_transactions.transaction_type"; + } + unless ( index_exists( 'pseudonymized_transactions', 'pseudonymized_transactions_ibfk_6' ) ) { + $dbh->do( + q{ALTER TABLE `pseudonymized_transactions` ADD INDEX `pseudonymized_transactions_ibfk_6` (`datetime`)}); + say $out "Added new index on pseudonymized_transactions.datetime"; + } + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 16310f0f44..ea540c0819 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -5307,7 +5307,10 @@ CREATE TABLE `pseudonymized_transactions` ( PRIMARY KEY (`id`), KEY `pseudonymized_transactions_ibfk_1` (`categorycode`), KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`), - KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`) + KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`), + KEY `pseudonymized_transactions_items_ibfk_4` (`itemnumber`), + KEY `pseudonymized_transactions_ibfk_5` (`transaction_type`), + KEY `pseudonymized_transactions_ibfk_6` (`datetime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- 2.30.2