[Koha-patches] [PATCH] Bug #6123 - Optimization of the SQL request using NOT EXISTS instead of NOT IN

[Alex Arnaud]

---
 reports/borrowers_out.pl |   19 ++++++-------------
 1 files changed, 6 insertions(+), 13 deletions(-)

diff --git a/reports/borrowers_out.pl b/reports/borrowers_out.pl
index 0a135e1..ed0fc0b 100755
--- a/reports/borrowers_out.pl
+++ b/reports/borrowers_out.pl
@@ -229,19 +229,12 @@ sub calculate {
     $strcalc .= "WHERE 1 ";
     @$filters[0]=~ s/\*/%/g if (@$filters[0]);
     $strcalc .= " AND borrowers.categorycode like '" . @$filters[0] ."'" if ( @$filters[0] );
-
-    my $strqueryfilter = "SELECT DISTINCT borrowernumber FROM  ";
-    $strqueryfilter .= "(SELECT borrowernumber from old_issues WHERE borrowernumber IS NOT NULL ";
-    if ($filters->[1]){
-        $strqueryfilter .= "AND old_issues.timestamp> '$filters->[1]' ";
-    }
-    $strqueryfilter .= "UNION SELECT borrowernumber FROM issues WHERE 1 ";
-    if ($filters->[1]){
-        $strqueryfilter .= "AND issues.timestamp> '$filters->[1]' ";
-    }
-    $strqueryfilter .= ") active_borrowers";
-
-    $strcalc .= " AND borrowers.borrowernumber not in ($strqueryfilter)";
+    $strcalc .= " AND NOT EXISTS (SELECT * FROM issues WHERE issues.borrowernumber=borrowers.borrowernumber ";
+    $strcalc .= " AND issues.timestamp> '" . @$filters[1] . "'" if (@$filters[1]);
+    $strcalc .= ") ";
+    $strcalc .= " AND NOT EXISTS (SELECT * FROM old_issues WHERE old_issues.borrowernumber=borrowers.borrowernumber ";
+    $strcalc .= " AND old_issues.timestamp> '" . @$filters[1] . "'" if (@$filters[1]);
+    $strcalc .= ") ";
     $strcalc .= " group by borrowers.borrowernumber";
     $strcalc .= ", $colfield" if ($column);
     $strcalc .= " order by $colfield " if ($colfield);
-- 
1.6.3.3