From cbf405828a6a048c17a3501a0e2d69a54386b56c Mon Sep 17 00:00:00 2001 From: Nick Clemens Date: Wed, 29 Sep 2021 12:26:59 +0000 Subject: [PATCH] Bug 29134: Use a subquery to increase performance of patron attributes search MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit This patch generates a subquery and checks if a borrowrnumber is in the results to add patrons to search results To test: 1 - Generate a bunch of patrons: SELECT surname, firstname, branchcode, categorycode FROM ( SELECT surname FROM borrowers ORDER BY rand() ) a,( SELECT firstname FROM borrowers ORDER BY rand() ) b,( SELECT branchcode FROM borrowers ORDER BY rand() ) c,( SELECT categorycode FROM borrowers ORDER BY rand() ) d LIMIT 50000 2 - Add a patron attribute to the system and make it searchable - I used code 'TEST' 3 - Add a value for this attribute to many patrons: INSERT INTO borrower_attributes (borrowernumber,code,attribute) SELECT borrowernumber, 'TEST','alphabet' FROM borrowers LIMIT 10000; 4 - In staff client got 'Patrons' 5 - Open the browser console (F12) and view the netwrok tab 6 - Perform a patron search for 'a' 7 - Note the time it takes for 'search' to complete in console 8 - Apply patch, restart_all 9 - Repeat search 10 - Note it is much faster 11 - prove -v t/db_dependent/Utils/Datatables_Members.t NOTE: I tested with 500k patrons and 100k attributes - search returned in ~2 seconds with patch and did not return before I got impatient without patch Signed-off-by: David Nind Signed-off-by: Joonas Kylmälä Signed-off-by: Jonathan Druart --- C4/Utils/DataTables/Members.pm | 8 ++------ 1 file changed, 2 insertions(+), 6 deletions(-) diff --git a/C4/Utils/DataTables/Members.pm b/C4/Utils/DataTables/Members.pm index 3d677b8e01..97a372bd96 100644 --- a/C4/Utils/DataTables/Members.pm +++ b/C4/Utils/DataTables/Members.pm @@ -160,12 +160,8 @@ sub search { if ( $searchfieldstype eq 'standard' and C4::Context->preference('ExtendedPatronAttributes') and $searchmember ) { - my @matching_borrowernumbers = Koha::Patrons->filter_by_attribute_value($searchmember)->get_column('borrowernumber'); - - for my $borrowernumber ( @matching_borrowernumbers ) { - push @where_strs_or, "borrowers.borrowernumber = ?"; - push @where_args, $borrowernumber; - } + push @where_strs_or, " borrowernumber IN ( SELECT DISTINCT borrowernumber FROM borrower_attributes JOIN borrower_attribute_types USING (code) WHERE staff_searchable = 1 AND attribute LIKE ? ) "; + push @where_args, $term; } push @where_strs, '('. join (' OR ', @where_strs_or) . ')' -- 2.39.5