fix(issue): performance problem in sync issue query

Mysql uses temporary and filesort, with huge performance hit on big DB
pluginsGLPI · Apr 1, 2022 · 74b38ec · 74b38ec
1 parent 154a353
commit 74b38ec
Showing 1 changed file with 14 additions and 8 deletions.
diff --git a/inc/issue.class.php b/inc/issue.class.php
@@ -212,14 +212,20 @@ public static function getSyncIssuesRequest() : AbstractQuery {
          'HAVING' => new QueryExpression("COUNT(`$itemTicketTable`.`items_id`) = 0")
       ];
 
-      // assistance requests having only one generated ticket (use query2)
-      $query3 = $query2;
-      // replace LEFT JOIN with INNER JOIN to exclude tickets not linked to a Formanswer object
-      $query3['INNER JOIN'][$itemTicketTable] = $query3['LEFT JOIN'][$itemTicketTable];
-      unset($query3['LEFT JOIN'][$itemTicketTable]);
-      // Only 1 relation to a Formanswer object
-      $query3['GROUPBY'] = ["$itemTicketTable.items_id"];
-      $query3['HAVING'] = new QueryExpression("COUNT(`$itemTicketTable`.`items_id`) = 1");
+      // assistance requests having only one generated ticket (reuse query2)
+      $query3 = [
+         'SELECT'     => $query2['SELECT'],
+         'FROM'       => $query2['FROM'],
+         'INNER JOIN' => [$itemTicketTable => $query2['LEFT JOIN'][$itemTicketTable]],
+         'LEFT JOIN'  => [
+            $query2['LEFT JOIN'][0], // This is the TABLE => [...] subquery
+            $ticketValidationTable => $query2['LEFT JOIN'][$ticketValidationTable],
+         ],
+         'WHERE'      => $query2['WHERE'],
+         'GROUPBY'    => ["$itemTicketTable.items_id"],
+         // Only 1 relation to a Formanswer object
+         'HAVING'     => new QueryExpression("COUNT(`$itemTicketTable`.`items_id`) = 1"),
+      ];
 
       // Union of the 3 previous queries
       $union = new QueryUnion([