Bugzilla – Attachment 187258 Details for
Bug 40936
Add index for default patron sort order
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 40936: Add index for default patron sort order
fd513e4.patch (text/plain), 4.68 KB, created by
Kyle M Hall (khall)
on 2025-10-02 12:07:15 UTC
(
hide
)
Description:
Bug 40936: Add index for default patron sort order
Filename:
MIME Type:
Creator:
Kyle M Hall (khall)
Created:
2025-10-02 12:07:15 UTC
Size:
4.68 KB
patch
obsolete
>From fd513e4df12b3487cd4ba0b94053c5ab1e23a144 Mon Sep 17 00:00:00 2001 >From: Kyle M Hall <kyle@bywatersolutions.com> >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)
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 40936
:
187258
|
187259
|
187260
|
187261
|
187262
|
187263
|
187264
|
187265
|
187266
|
187650