[Bug 40936] New: Add index for default patron sort order
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Bug ID: 40936 Summary: Add index for default patron sort order Change sponsored?: --- Product: Koha Version: unspecified Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Patrons Assignee: koha-bugs@lists.koha-community.org Reporter: kyle@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com, kyle@bywatersolutions.com 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 ); -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |kyle@bywatersolutions.com |ity.org | -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 --- Comment #1 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 187258 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=187258&action=edit Bug 40936: Add index for default patron sort order Patch from commit fd513e4 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 --- Comment #2 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 187259 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=187259&action=edit Bug 38330: (follow-up) Add an entry for new column Patch from commit 9928302 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 --- Comment #3 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 187260 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=187260&action=edit Bug 38936: Use https in license Patch from commit 2fb76a3 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 --- Comment #4 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 187261 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=187261&action=edit Bug 38330: (follow-up) Quote tagfield value to force VARCHAR comparisson Patch from commit cfc2cd7 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 --- Comment #5 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 187262 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=187262&action=edit Bug 38330: Commit changes for dbic --force Patch from commit ab0fc75 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 --- Comment #6 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 187263 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=187263&action=edit Bug 38330: (RM follow-up) Add missing comma Patch from commit c144c73 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #187259|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #187260|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #187261|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #187262|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #187263|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 --- Comment #7 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 187264 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=187264&action=edit Bug 40936: Add index for default patron sort order Patch from commit 0509560 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 --- Comment #8 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 187265 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=187265&action=edit Bug 38936: Use https in license Patch from commit 2fb76a3 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 --- Comment #9 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 187266 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=187266&action=edit Bug 38330: (follow-up) Quote tagfield value to force VARCHAR comparisson Patch from commit cfc2cd7 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #187258|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #187265|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #187266|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #187264|0 |1 is obsolete| | --- Comment #10 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 187650 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=187650&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@openfifth.c | |o.uk Status|Needs Signoff |Signed Off Text to go in the| |This change introduces a release notes| |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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |tomascohen@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #187650|0 |1 is obsolete| | --- Comment #11 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- Created attachment 188024 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=188024&action=edit 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> Signed-off-by: Nick Clemens <nick@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |nick@bywatersolutions.com |y.org | Patch complexity|--- |Trivial patch CC| |lucas@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |25.11.00 released in| | Status|Passed QA |Pushed to main -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 --- Comment #12 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Nice work everyone! Pushed to main for 25.11 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Paul Derscheid <paul.derscheid@lmscloud.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|25.11.00 |25.11.00,25.05.05 released in| | Status|Pushed to main |Pushed to stable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 --- Comment #13 from Paul Derscheid <paul.derscheid@lmscloud.de> --- Nice work everyone! Pushed to 25.05.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|25.11.00,25.05.05 |25.11.00,25.05.05,24.11.11 released in| | CC| |fridolin.somers@biblibre.co | |m Status|Pushed to stable |Pushed to oldstable --- Comment #14 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to 24.11.x for 24.11.11 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to oldstable |Needs documenting --- Comment #15 from Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> --- Not backporting to 22.11.x as it wasn't backported to 24.05.x -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org