Bug 6078

Summary: Patron with no checkout Report overload the server
Product: Koha Reporter: Julian Maurice <julian.maurice>
Component: ReportsAssignee: Julian Maurice <julian.maurice>
Status: CLOSED FIXED QA Contact: Bugs List <koha-bugs>
Severity: enhancement    
Priority: PATCH-Sent (DO NOT USE) CC: alex.arnaud, chris, paul.poulain
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Attachments: Patch
Signed off Patch
proposed patch (passed QA)

Description Julian Maurice 2011-04-06 08:24:25 UTC
Following patch will optimize the SQL request using NOT EXISTS instead of NOT IN.
Comment 1 Julian Maurice 2011-04-06 08:26:49 UTC Comment hidden (obsolete)
Comment 2 Alex Arnaud 2011-04-07 14:59:37 UTC
*** Bug 6123 has been marked as a duplicate of this bug. ***
Comment 3 Chris Cormack 2011-06-07 06:41:58 UTC Comment hidden (obsolete)
Comment 4 Paul Poulain 2011-08-10 08:56:08 UTC
Created attachment 4917 [details] [review]
proposed patch (passed QA)

QA comment

the goal of the patch is correct: nested queries are time consumming. The solution provided requires quite a lot of SQL calculation, but there is probably no best solution, and this one is an improvement
Results are the same before and after this patch

Marking passed QA
Comment 5 Chris Cormack 2011-08-10 09:14:38 UTC
Original query results in an execution plan of
+----+--------------------+------------+--------+-------------------------------+-------------------+---------+------+------+--------------------------+
| id | select_type        | table      | type   | possible_keys                 | key               | key_len | ref  | rows | Extra                    |
+----+--------------------+------------+--------+-------------------------------+-------------------+---------+------+------+--------------------------+
|  1 | PRIMARY            | borrowers  | index  | NULL                          | PRIMARY           | 4       | NULL |   58 | Using where              |
|  2 | DEPENDENT SUBQUERY | <derived3> | system | NULL                          | NULL              | NULL    | NULL |    1 |                          |
|  3 | DERIVED            | old_issues | range  | old_issuesborridx,old_bordate | old_issuesborridx | 5       | NULL |    8 | Using where; Using index |
|  4 | UNION              | issues     | index  | NULL                          | issuesborridx     | 5       | NULL |    1 | Using index              |
| NULL | UNION RESULT       | <union3,4> | ALL    | NULL                          | NULL              | NULL    | NULL | NULL |                          |
+----+--------------------+------------+--------+-------------------------------+-------------------+---------+------+------+--------------------------+

New query, is much better

+----+--------------------+------------+-------+-------------------------------+-------------------+---------+-------------------------------+------+--------------------------+
| id | select_type        | table      | type  | possible_keys                 | key               | key_len | ref                           | rows | Extra                    |
+----+--------------------+------------+-------+-------------------------------+-------------------+---------+-------------------------------+------+--------------------------+
|  1 | PRIMARY            | borrowers  | index | NULL                          | PRIMARY           | 4       | NULL                          |   58 | Using where              |
|  3 | DEPENDENT SUBQUERY | old_issues | ref   | old_issuesborridx,old_bordate | old_issuesborridx | 5       | koha.borrowers.borrowernumber |    8 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | issues     | ref   | issuesborridx,bordate         | issuesborridx     | 5       | koha.borrowers.borrowernumber |    1 | Using where; Using index |
+----+--------------------+------------+-------+-------------------------------+-------------------+---------+-------------------------------+------+--------------------------+

Pushing, please test
Comment 6 Julian Maurice 2012-01-02 11:12:26 UTC
Ok in master, marked as resolved