From 0a5283cce7d226e8c95d8969fd7da79c6b7f61b4 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 --- C4/Members.pm | 13 ++++++++++--- 1 file changed, 10 insertions(+), 3 deletions(-) diff --git a/C4/Members.pm b/C4/Members.pm index 4fb845d..c1b4b1e 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -2111,17 +2111,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 = ? "; -- 1.7.10.4