Bug 33489 - The borrowers table should have indexes on default patron search fields
Summary: The borrowers table should have indexes on default patron search fields
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Nick Clemens (kidclamp)
QA Contact: Katrin Fischer
URL:
Keywords:
Depends on:
Blocks: 33853
  Show dependency treegraph
 
Reported: 2023-04-11 16:13 UTC by Nick Clemens (kidclamp)
Modified: 2023-12-28 20:47 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
23.05.00,22.11.06


Attachments
Bug 33489: Add indices to default patron search fields (2.62 KB, patch)
2023-04-11 17:35 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 33489: Add indices to default patron search fields (2.67 KB, patch)
2023-04-11 18:30 UTC, David Nind
Details | Diff | Splinter Review
Bug 33489: Add indices to default patron search fields (2.73 KB, patch)
2023-04-11 21:26 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 33489: (QA follow-up) Add missing index prefix in DBRev (1.04 KB, patch)
2023-06-02 09:53 UTC, Mason James
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens (kidclamp) 2023-04-11 16:13:59 UTC
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
Comment 1 Nick Clemens (kidclamp) 2023-04-11 17:35:25 UTC
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
Comment 2 David Nind 2023-04-11 18:30:14 UTC
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>
Comment 3 Katrin Fischer 2023-04-11 21:26:47 UTC
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>
Comment 4 Tomás Cohen Arazi 2023-05-12 20:54:40 UTC
Pushed to master for 23.05.

Nice work everyone, thanks!
Comment 5 Martin Renvoize 2023-05-16 06:45:21 UTC
Many hands makes light work, thankyou everyone!

Pushed to 22.11.x for the next release
Comment 6 SOS 2023-05-29 12:29:45 UTC
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
Comment 7 Katrin Fischer 2023-05-29 12:46:12 UTC
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
Comment 8 Mason James 2023-06-02 09:36:22 UTC
(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
Comment 9 Mason James 2023-06-02 09:53:43 UTC
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>