[Bug 33489] New: The borrowers table should have indexes on default patron search fields
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Bug ID: 33489 Summary: The borrowers table should have indexes on default patron search fields Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: koha-bugs@lists.koha-community.org Reporter: nick@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org While a library can specify the default patron search fields in the similarly named preference, DefaultPatronSearchFields, Koha should ship with indices on the default fields: firstname,middle_name,surname,othernames,cardnumber,userid These exist for firstname, surname, and othernames We have seen the basic patron search take between 2-4 seconds without these indexes on a database of 700k patrons. When using only the indexed fields, the search takes 6 milliseconds In testing with 100k patrons I saw a reduction after adding the indices from half a second to ~1 centisecond -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- 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=33489 --- Comment #1 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 149457 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=149457&action=edit Bug 33489: Add indices to default patron search fields This patch adds indices to the borrowers table to match the default search fields for patrons. To test: 1 - Apply patch 2 - Update database 3 - Ensure patron searching works as before -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |nick@bywatersolutions.com |ity.org | CC| |jonathan.druart+koha@gmail. | |com, | |martin.renvoize@ptfs-europe | |.com, tomascohen@gmail.com -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=33488 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #149457|0 |1 is obsolete| | --- Comment #2 from David Nind <david@davidnind.com> --- Created attachment 149463 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=149463&action=edit Bug 33489: Add indices to default patron search fields This patch adds indices to the borrowers table to match the default search fields for patrons. To test: 1 - Apply patch 2 - Update database 3 - Ensure patron searching works as before Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |katrin.fischer@bsz-bw.de |y.org | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Small patch 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=33489 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #149463|0 |1 is obsolete| | --- Comment #3 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Created attachment 149505 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=149505&action=edit Bug 33489: Add indices to default patron search fields This patch adds indices to the borrowers table to match the default search fields for patrons. To test: 1 - Apply patch 2 - Update database 3 - Ensure patron searching works as before Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=33554 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to master Version(s)| |23.05.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 --- Comment #4 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Pushed to master for 23.05. Nice work everyone, thanks! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|23.05.00 |23.05.00,22.11.06 released in| | Status|Pushed to master |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=33489 --- Comment #5 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Many hands makes light work, thankyou everyone! Pushed to 22.11.x for the next release -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 SOS <aliakbar.petiwala88@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |aliakbar.petiwala88@gmail.c | |om --- Comment #6 from SOS <aliakbar.petiwala88@gmail.com> --- I tried to switched to new stable version but unfortunately received error on this number, please guide how to ignore this error and update the table using script file... step by step guide using koha on ubuntu -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 --- Comment #7 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- This will be fixed with a new package very soon. Upgrade to 22.11.05.010 [10:48:17]: Bug 33489 - Add indices to default patron search fields ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: BLOB/TEXT column 'middle_name' used in key specification without a key length at /usr/share/koha/lib/C4/Installer.pm line 743 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |33853 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33853 [Bug 33853] Error upgrading database schema -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Mason James <mtj@kohaaloha.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mtj@kohaaloha.com --- Comment #8 from Mason James <mtj@kohaaloha.com> --- (In reply to Katrin Fischer from comment #7)
This will be fixed with a new package very soon.
Upgrade to 22.11.05.010 [10:48:17]: Bug 33489 - Add indices to default patron search fields ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: BLOB/TEXT column 'middle_name' used in key specification without a key length at /usr/share/koha/lib/C4/Installer.pm line 743
a fix for this has been included in 22.11.06-3 package -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Mason James <mtj@kohaaloha.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to stable |ASSIGNED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Mason James <mtj@kohaaloha.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 --- Comment #9 from Mason James <mtj@kohaaloha.com> --- Created attachment 151979 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=151979&action=edit Bug 33489: (QA follow-up) Add missing index prefix in DBRev Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33489 Martin Renvoize <martin.renvoize@ptfs-europe.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=33489 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to stable -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org