Bug 6078 - Patron with no checkout Report overload the server
Summary: Patron with no checkout Report overload the server
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: Main
Hardware: All All
: PATCH-Sent (DO NOT USE) enhancement (vote)
Assignee: Julian Maurice
QA Contact: Bugs List
URL:
Keywords:
: 6123 (view as bug list)
Depends on:
Blocks:
 
Reported: 2011-04-06 08:24 UTC by Julian Maurice
Modified: 2012-10-25 23:10 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Patch (1.93 KB, patch)
2011-04-06 08:26 UTC, Julian Maurice
Details | Diff | Splinter Review
Signed off Patch (2.00 KB, patch)
2011-06-07 06:41 UTC, Chris Cormack
Details | Diff | Splinter Review
proposed patch (passed QA) (2.04 KB, patch)
2011-08-10 08:56 UTC, Paul Poulain
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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