Following patch will optimize the SQL request using NOT EXISTS instead of NOT IN.
Created attachment 3724 [details] [review] Patch
*** Bug 6123 has been marked as a duplicate of this bug. ***
Created attachment 4390 [details] [review] Signed off Patch
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
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
Ok in master, marked as resolved