MySQL Bugs: #80470: queries used a derived table on the inner side of an outer join perform slowly

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:	Windows
Assigned to:		CPU Architecture:	Any

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

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.

Please check http://bugs.mysql.com/bug.php?id=80505.

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 !!!!

http://bugs.mysql.com/bug.php?id=80505 marked as duplicate of this one.

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