From 555731da97f4e26da237e3c741ccf590c0131248 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Thu, 2 Oct 2025 08:11:00 -0400 Subject: [PATCH] Bug 40936: Add index for default patron sort order The default sort order for Koha's patron search includes many columns, some of which are not indexed: [% CASE 'name-address' %] { "data":"me.surname:me.preferred_name:me.firstname:me.middle_name:me.othernames:me.street_number:me.address:me.address2:me.city:me.state:me.postal_code:me.country", Depending on the number of patrons in the database, and the specific configuration of the server, this can end up generating a query that creates a temp table in the hundreds of gigabytes of data or more. Not only does this risk crashing a database server with insufficient disk space, it's also incredibly slow. We've found a compound index prevents the issue and also makes the search much faster. InndoDB indexes have a fixed maximum length which is too small ( 3072 bytes ) to encompass the full data for all the fields we need to sort by. The following works well as a comprise to keep the index length within the maximum: CREATE INDEX idx_borrowers_sort_order ON borrowers ( surname(100), -- 400 preferred_name(80), -- 320 firstname(80), -- 320 middle_name(50), -- 200 othernames(50), -- 200 streetnumber(20), -- 80 address(100), -- 400 address2(75), -- 300 city(75), -- 300 state(40), -- 160 zipcode(20), -- 80 country(40) -- 160 ); Test Plan: 1) Apply this patch 2) Run updatedatabase.pl 3) Perform a patron search 4) No change in behvior should be noted! Signed-off-by: Martin Renvoize --- .../data/mysql/atomicupdate/bug_40936.pl | 33 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 1 + 2 files changed, 34 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bug_40936.pl diff --git a/installer/data/mysql/atomicupdate/bug_40936.pl b/installer/data/mysql/atomicupdate/bug_40936.pl new file mode 100755 index 00000000000..7ed2a1088cb --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_40936.pl @@ -0,0 +1,33 @@ +use Modern::Perl; + +return { + bug_number => "40936", + description => "Add index for default patron sort order", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + # Do you stuffs here + unless ( index_exists( 'borrowers', 'idx_borrowers_sort_order' ) ) { + $dbh->do( + q{ + CREATE INDEX idx_borrowers_sort_order ON borrowers ( + surname(100), + preferred_name(80), + firstname(80), + middle_name(50), + othernames(50), + streetnumber(20), + address(100), + address2(75), + city(75), + state(40), + zipcode(20), + country(40) + ); + } + ); + say $out "Added new index on borrowers table"; + } + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e391dc68f5d..8ba31a93c33 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1623,6 +1623,7 @@ CREATE TABLE `borrowers` ( KEY `userid_idx` (`userid`), KEY `middle_name_idx` (`middle_name`(768)), KEY `preferred_name_idx` (`preferred_name`(768)), + KEY `idx_borrowers_sort_order` (`surname`(100),`preferred_name`(80),`firstname`(80),`middle_name`(50),`othernames`(50),`streetnumber`(20),`address`(100),`address2`(75),`city`(75),`state`(40),`zipcode`(20),`country`(40)), CONSTRAINT `borrowers_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`), CONSTRAINT `borrowers_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`), CONSTRAINT `borrowers_ibfk_3` FOREIGN KEY (`sms_provider_id`) REFERENCES `sms_providers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE -- 2.51.0