From 51718f41c53b1b7c7c3b1c66490f949ba2fa86f4 Mon Sep 17 00:00:00 2001 From: Fridolin Somers Date: Tue, 27 Jun 2017 14:54:46 +0200 Subject: [PATCH] Bug 18861 - branch_transfer_limits needs keys The branch transfers limits are edited in : /cgi-bin/koha/admin/branch_transfer_limits.pl This page calls a loop on IsBranchTransferAllowed with all destination branches and codes (ccodes or itemtypes). This can takes a huge time because table branch_transfer_limits has no keys. Since ccode or itemtype may be used, this patch adds a key on both combinaison : ccode/fromBranch/toBranch and itemtype/fromBranch/toBranch. Tested on a table with 66000 entries, time whent from 26 second to less than 1 second. Test plan : - Go to Administration > Library transfer limits - Check page load is faster - Uncheck a checkbox - Save => Verify save is OK - Change branch and same tests Followed test plan which worked as intended Signed-off-by: Alex Buckley --- installer/data/mysql/atomicupdate/bug_18861.perl | 14 ++++++++++++++ installer/data/mysql/atomicupdate/bug_18861.sql | 2 ++ installer/data/mysql/kohastructure.sql | 2 ++ 3 files changed, 18 insertions(+) create mode 100644 installer/data/mysql/atomicupdate/bug_18861.perl create mode 100644 installer/data/mysql/atomicupdate/bug_18861.sql diff --git a/installer/data/mysql/atomicupdate/bug_18861.perl b/installer/data/mysql/atomicupdate/bug_18861.perl new file mode 100644 index 0000000000..5144d41c12 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_18861.perl @@ -0,0 +1,14 @@ +$DBversion = 'XXX'; +if( CheckVersion( $DBversion ) ) { + unless ( index_exists( 'branch_transfer_limits', 'itemtype_idx' ) ) { + $dbh->do(q| + ALTER TABLE branch_transfer_limits ADD KEY `itemtype_idx` (`itemtype`,`fromBranch`,`toBranch`); + |); + } + unless ( index_exists( 'branch_transfer_limits', 'ccode_idx' ) ) { + $dbh->do(q| + ALTER TABLE branch_transfer_limits ADD KEY `ccode_idx` (`ccode`,`fromBranch`, `toBranch`); + |); + } + NewVersion( $DBversion, 18861, "Add indexes for ccode and itemtype to branch_transfer_limits"); +} diff --git a/installer/data/mysql/atomicupdate/bug_18861.sql b/installer/data/mysql/atomicupdate/bug_18861.sql new file mode 100644 index 0000000000..6f17c67fde --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_18861.sql @@ -0,0 +1,2 @@ +ALTER TABLE branch_transfer_limits ADD KEY `itemtype_idx` (`itemtype`,`fromBranch`,`toBranch`); +ALTER TABLE branch_transfer_limits ADD KEY `ccode_idx` (`ccode`,`fromBranch`, `toBranch`); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index ef3bff04b0..07ea308fb9 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2649,6 +2649,8 @@ CREATE TABLE branch_transfer_limits ( itemtype varchar(10) NULL, ccode varchar(80) NULL, PRIMARY KEY (limitId) + KEY `itemtype_idx` (`itemtype`,`fromBranch`,`toBranch`), + KEY `ccode_idx` (`ccode`,`fromBranch`, `toBranch`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- 2.11.0