From b721b6f9f3b8eb65a929ca0605216b58f8f87996 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 20 Feb 2015 16:23:50 +0100 Subject: [PATCH] Bug 13740: Remove the NOT NULL clause in GetBorrowersToExpunge C4::Borrowers::GetBorrowersToExpunge should not use a "NOT IN", it is not efficient at all. With only 1 guarantor and more than 136k patrons, the not in clause in this subroutine takes ages: mysql> select count(*) FROM borrowers where borrowernumber NOT IN (SELECT guarantorid FROM borrowers WHERE guarantorid IS NOT NULL AND guarantorid <> 0) ; [...] not ended after 5min With the query modified by this patch, the results come after 1 sec :) Test plan: Verify the delete_patrons.pl cronjob or the cleanborrowers tools work as before. Especially with guarantors. Signed-off-by: Brendan Gallagher Signed-off-by: Koha Team AMU Signed-off-by: Kyle M Hall Signed-off-by: Tomas Cohen Arazi --- C4/Members.pm | 13 ++++++++++--- 1 file changed, 10 insertions(+), 3 deletions(-) diff --git a/C4/Members.pm b/C4/Members.pm index a98d16fcf9..7c3f9f6772 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -2109,17 +2109,24 @@ sub GetBorrowersToExpunge { : ""); my $dbh = C4::Context->dbh; - my $query = " + my $query = q| SELECT borrowers.borrowernumber, MAX(old_issues.timestamp) AS latestissue, MAX(issues.timestamp) AS currentissue FROM borrowers JOIN categories USING (categorycode) + LEFT JOIN ( + SELECT guarantorid + FROM borrowers + WHERE guarantorid IS NOT NULL + AND guarantorid <> 0 + ) as tmp ON borrowers.borrowernumber=tmp.guarantorid LEFT JOIN old_issues USING (borrowernumber) LEFT JOIN issues USING (borrowernumber) WHERE category_type <> 'S' - AND borrowernumber NOT IN (SELECT guarantorid FROM borrowers WHERE guarantorid IS NOT NULL AND guarantorid <> 0) - "; + AND tmp.guarantorid IS NOT NULL + |; + my @query_params; if ( $filterbranch && $filterbranch ne "" ) { $query.= " AND borrowers.branchcode = ? "; -- 2.39.2