Attachment 163659 Details for Bug 36033 – Bug 36033: Add more indexes to table pseudonymized_transactions

[patch] Bug 36033: Add more indexes to table pseudonymized_transactions

Bug-36033-Add-more-indexes-to-table-pseudonymizedt.patch (text/plain), 4.09 KB, created by Marcel de Rooy on 2024-03-22 07:34:19 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Marcel de Rooy

Created: 2024-03-22 07:34:19 UTC

Size: 4.09 KB

patch

obsolete

>From f242bee29906d44cc9f529f51441f9bdcf98a633 Mon Sep 17 00:00:00 2001
>From: Fridolin Somers <fridolin.somers@biblibre.com>
>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 <david@davidnind.com>
>
>Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
>---
> .../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

Actions: View | Diff | Splinter Review

Attachments on bug 36033: 161879 | 161880 | 162012 | 163659