https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40936
Nick Clemens (kidclamp) <[email protected]> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #187650|0 |1 is obsolete| | --- Comment #11 from Nick Clemens (kidclamp) <[email protected]> --- 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 <[email protected]> Signed-off-by: Nick Clemens <[email protected]> -- You are receiving this mail because: 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/
