Bug #88663 Performance Regression using subqueries in 5.7
| Submitted: | 27 Nov 2017 10:27 | Modified: | 28 Nov 2017 22:07 |
|---|---|---|---|
| Reporter: | Mark El-Wakil | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S5 (Performance) |
| Version: | 5.7.20 | OS: | Any |
| Assigned to: | CPU Architecture: | Any |
[27 Nov 2017 10:27]
Mark El-Wakil
[28 Nov 2017 11:20]
Øystein Grøvlen
It is not clear to me why you want to avoid temporary tables in your example. The 5.7 plan will read 1 row from table s, do 1 look-up into table p, and store 1 row in a temporary table. The 5.5 plan will read 10 rows from table p and do 10 look-ups into table s. I am not sure the latter will be more efficient. That is said, it is possible to turn off this particular plan by setting optimizer_switch='dupsweedout=off'. However, it is possible that you will then get another semijoin strategy that uses a temporary table. So in order to be sure to get the 5.5 plan, you should set optimizer_switch='semijoin=off,materialization=off' Alternatively, you can use an optimizer hint to force the subquery strategy from 5.5: SELECT p.Id FROM (p) WHERE p.Id IN (select /*+ SUBQUERY(INTOEXISTS) */ s.Id from s) ORDER BY Id desc
[28 Nov 2017 22:07]
Mark El-Wakil
I was trying to track down performance issues down to a behavior difference between 5.5 and 5.7. Looks like the issue I had on my end was unrelated to this. My bad. I noticed that sometimes a temporary table is not created for subqueries like this. Is this related to the relative size of `p` and `s` respectively? Thanks!
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.