Bug #80470 queries used a derived table on the inner side of an outer join perform slowly
Submitted: 23 Feb 2016 4:56 Modified: 26 Feb 2016 2:19
Reporter: mohamed atef Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.7.11 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[23 Feb 2016 4:56] mohamed atef
Description:
this is bugs fixed in mysql version 5.7.11
Some queries with derived tables perform better with materialization than when merged into the outer query. The optimizer no longer uses merging by default for derived tables containing dependent subqueries in the select list. (Bug #79294, Bug #22223202)

but now this queries perform too much slowly although optimizer switch flaged on derived_merge=on ?

it may take two days to fetch data from server

you fixed incorrect data but it affect performance
now mysql server 5.7.11 perform like mysql server 5.6
in mysql server 5.7.10 when optimizer switch derived_merge=on it merge to outer query 
in mysql server 5.7.10 we have the choice
but in mysql 5.7.11 no choice it ignore optimizer switch flag derived_merge =on or derived_merge=off it all the same

How to repeat:
queries used a derived table on the inner side of an outer join perform slowly
[24 Feb 2016 15:30] Sinisa Milivojevic
Hi,

Thank you for reporting this behavior.

What you claim is that 5.7.11 is performing much worse then 5.7.10. There were some fixes in 5.7.11 that avoided materialization for DEPENDENT nested queries, included those in the FROM clause. Dependent nested queries can NOT be merged in !!!!!

So, if your case entails the independent (very important) nested query in the FROM clause (also important) and not in SELECT list, then send us a test case.

Test case should include both EXPLAIN EXTENDED and optimizer trace for both 5.7.10 and 5.7.11.

Thanks in advance.
[25 Feb 2016 12:34] Miguel Solorzano
Please check http://bugs.mysql.com/bug.php?id=80505.
[25 Feb 2016 14:51] Sinisa Milivojevic
Hi!

If data for this bug are the same as for the bug #80505, then this report is not a bug.

There is a bug fixed in 5.7.11, Bug #79194, which improves performance in 90 % of cases. In 10 % of cases, like this one, it will produce lower performance then with materialization. 90 % is much more then 10 %, hence the fix from # 79194 is accepted. To get better result, use materialization in this bug.

Also, what you used in #80505 are not derived tables, but nested query in the SELECT list !!!!
[25 Feb 2016 14:54] Miguel Solorzano
http://bugs.mysql.com/bug.php?id=80505 marked as duplicate of this one.
[26 Feb 2016 2:19] mohamed atef
Hi 
Thanks for reply 
you said it 10 % only 
10% 
Is not nothing to be ignored
And i seriously thinking about 
Change mysql to other database 
Many thx