From fd513e4df12b3487cd4ba0b94053c5ab1e23a144 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Thu, 2 Oct 2025 08:03:48 -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! --- Koha/Schema.pm | 10 ++++++ .../data/mysql/atomicupdate/bug_40936.pl | 33 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 1 + 3 files changed, 44 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bug_40936.pl diff --git a/Koha/Schema.pm b/Koha/Schema.pm index cffc631d5e1..a87a1022659 100644 --- a/Koha/Schema.pm +++ b/Koha/Schema.pm @@ -12,6 +12,16 @@ use base 'DBIx::Class::Schema'; __PACKAGE__->load_namespaces; +sub connect { + my ($class, @args) = @_; + my $schema = $class->next::method(@args); + + # enable debug + stack trace + $schema->storage->debug(1); + + return $schema; +} + # Created by DBIx::Class::Schema::Loader v0.07025 @ 2013-10-14 20:56:21 # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:oDUxXckmfk6H9YCjW8PZTw 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.39.5 (Apple Git-154)