Bug #94364 | Optimizer does not use index on multiple join | ||
---|---|---|---|
Submitted: | 16 Feb 2019 9:52 | Modified: | 22 Mar 2019 13:52 |
Reporter: | Masaki Goto | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
Version: | 5.7.25-log | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | INDEX, join, Optimizer |
[16 Feb 2019 9:52]
Masaki Goto
[20 Feb 2019 15:34]
MySQL Verification Team
Hi, Thank you very much for your bug report. You are using 5.7.15, which is very, very old release of 5..7. Could you please try 5.7.25 and let us know whether this problem persists. Thanks in advance.
[21 Feb 2019 3:18]
Masaki Goto
I tried with 5.7.25. But result is same as 5.7.15.
[21 Feb 2019 13:52]
MySQL Verification Team
Hi, I think that what you see is the expected behaviour. Let me try to explain it to you briefly. Your table 'contract' is the left-most table in the two-stop LEFT OUTER JOIN. Being the left-most, implies that no index of that table can be used for the resolution of JOIN. Hence, the only way to use that index is in sorting. However, as we are dealing with two-step JOIN, a temporary table is needed to resolve the query. That means, that only temporary table has to be sorted and read from in order to return sorted results. So, since table 'contract' is not needed any more, there is no way to use that index for the resolution of the ORDER BY. I hope that it is clear. You can try to force this index for sorting, but I think you will get 100 % same execution plan from the optimiser.
[22 Mar 2019 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".