Bug 40936

Summary: Add index for default patron sort order
Product: Koha Reporter: Kyle M Hall (khall) <kyle>
Component: PatronsAssignee: Kyle M Hall (khall) <kyle>
Status: Signed Off --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: gmcharlt, kyle, martin.renvoize, tomascohen
Version: unspecified   
Hardware: All   
OS: All   
GIT URL: Change sponsored?: ---
Patch complexity: --- Documentation contact:
Documentation submission: Text to go in the release notes:
This change introduces a new database index to improve the performance of patron searches, especially in large databases. This will prevent slow searches and potential database server issues related to sorting patrons by name and address. **System Administrator Note:** Applying this update will add a new index to the `borrowers` table. On systems with a large number of patrons, this operation can take a significant amount of time and consume considerable server resources (CPU and I/O). While modern database systems can often perform this operation without locking the table for the entire duration, a general slowdown of the system is expected. It is **strongly recommended** to run the upgrade (`updatedatabase.pl`) during a planned maintenance window to avoid impacting users.
Version(s) released in:
Circulation function:
Attachments: Bug 40936: Add index for default patron sort order
Bug 38330: (follow-up) Add an entry for new column
Bug 38936: Use https in license
Bug 38330: (follow-up) Quote tagfield value to force VARCHAR comparisson
Bug 38330: Commit changes for dbic --force
Bug 38330: (RM follow-up) Add missing comma
Bug 40936: Add index for default patron sort order
Bug 38936: Use https in license
Bug 38330: (follow-up) Quote tagfield value to force VARCHAR comparisson
Bug 40936: Add index for default patron sort order

Description Kyle M Hall (khall) 2025-10-02 12:02:02 UTC
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
);
Comment 1 Kyle M Hall (khall) 2025-10-02 12:07:15 UTC
Created attachment 187258 [details] [review]
Bug 40936: Add index for default patron sort order

Patch from commit fd513e4
Comment 2 Kyle M Hall (khall) 2025-10-02 12:07:16 UTC
Created attachment 187259 [details] [review]
Bug 38330: (follow-up) Add an entry for new column

Patch from commit 9928302
Comment 3 Kyle M Hall (khall) 2025-10-02 12:07:17 UTC
Created attachment 187260 [details] [review]
Bug 38936: Use https in license

Patch from commit 2fb76a3
Comment 4 Kyle M Hall (khall) 2025-10-02 12:07:19 UTC
Created attachment 187261 [details] [review]
Bug 38330: (follow-up) Quote tagfield value to force VARCHAR comparisson

Patch from commit cfc2cd7
Comment 5 Kyle M Hall (khall) 2025-10-02 12:07:20 UTC
Created attachment 187262 [details] [review]
Bug 38330: Commit changes for dbic --force

Patch from commit ab0fc75
Comment 6 Kyle M Hall (khall) 2025-10-02 12:07:22 UTC
Created attachment 187263 [details] [review]
Bug 38330: (RM follow-up) Add missing comma

Patch from commit c144c73
Comment 7 Kyle M Hall (khall) 2025-10-02 12:12:50 UTC
Created attachment 187264 [details] [review]
Bug 40936: Add index for default patron sort order

Patch from commit 0509560
Comment 8 Kyle M Hall (khall) 2025-10-02 12:12:52 UTC
Created attachment 187265 [details] [review]
Bug 38936: Use https in license

Patch from commit 2fb76a3
Comment 9 Kyle M Hall (khall) 2025-10-02 12:12:53 UTC
Created attachment 187266 [details] [review]
Bug 38330: (follow-up) Quote tagfield value to force VARCHAR comparisson

Patch from commit cfc2cd7
Comment 10 Martin Renvoize (ashimema) 2025-10-09 11:00:44 UTC
Created attachment 187650 [details] [review]
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 <martin.renvoize@openfifth.co.uk>