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: [email protected]
Reporter: [email protected]
QA Contact: [email protected]
CC: [email protected], [email protected]
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.
_______________________________________________
Koha-bugs mailing list
[email protected]
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
