Summary: | Add index for default patron sort order | ||
---|---|---|---|
Product: | Koha | Reporter: | Kyle M Hall (khall) <kyle> |
Component: | Patrons | Assignee: | 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
Created attachment 187258 [details] [review] Bug 40936: Add index for default patron sort order Patch from commit fd513e4 Created attachment 187259 [details] [review] Bug 38330: (follow-up) Add an entry for new column Patch from commit 9928302 Created attachment 187260 [details] [review] Bug 38936: Use https in license Patch from commit 2fb76a3 Created attachment 187261 [details] [review] Bug 38330: (follow-up) Quote tagfield value to force VARCHAR comparisson Patch from commit cfc2cd7 Created attachment 187262 [details] [review] Bug 38330: Commit changes for dbic --force Patch from commit ab0fc75 Created attachment 187263 [details] [review] Bug 38330: (RM follow-up) Add missing comma Patch from commit c144c73 Created attachment 187264 [details] [review] Bug 40936: Add index for default patron sort order Patch from commit 0509560 Created attachment 187265 [details] [review] Bug 38936: Use https in license Patch from commit 2fb76a3 Created attachment 187266 [details] [review] Bug 38330: (follow-up) Quote tagfield value to force VARCHAR comparisson Patch from commit cfc2cd7 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> |