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
Created attachment 149457 [details] [review] 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
Created attachment 149463 [details] [review] 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>
Created attachment 149505 [details] [review] 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>
Pushed to master for 23.05. Nice work everyone, thanks!
Many hands makes light work, thankyou everyone! Pushed to 22.11.x for the next release
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
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
(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
Created attachment 151979 [details] [review] Bug 33489: (QA follow-up) Add missing index prefix in DBRev Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>