Attachment 36096 Details for Bug 13740 – Bug 13740: Remove the NOT NULL clause in GetBorrowersToExpunge

[patch] Bug 13740: Remove the NOT NULL clause in GetBorrowersToExpunge

Bug-13740-Remove-the-NOT-NULL-clause-in-GetBorrowe.patch (text/plain), 2.04 KB, created by Jonathan Druart on 2015-02-20 15:34:26 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jonathan Druart

Created: 2015-02-20 15:34:26 UTC

Size: 2.04 KB

patch

obsolete

>From 322a94152e42fe9629ec4160d5e9856d4dbb0801 Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@biblibre.com>
>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.
>---
> 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 = ? ";
>-- 
>2.1.0

Actions: View | Diff | Splinter Review

Attachments on bug 13740: 36096 | 36150 | 36157 | 36410 | 36411 | 36873 | 36874