Bug #96378 | Subquery with parameter is exponentially slower than hard-coded value | ||
---|---|---|---|
Submitted: | 31 Jul 2019 3:00 | Modified: | 2 Jan 2020 9:47 |
Reporter: | Jeff Johnson | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
Version: | 8.0.17 | OS: | Windows (Windows 10 x64) |
Assigned to: | CPU Architecture: | x86 (AMD Threadripper 1950X) | |
Tags: | performance |
[31 Jul 2019 3:00]
Jeff Johnson
[31 Jul 2019 12:35]
MySQL Verification Team
Hi Mr. Johnson, Thank you for your bug report. What you describe is a known behaviour of our optimiser. Simply, in order for index to be used, you can not use user variables. You can circumvent this in your application. However, I find that this is a very realistic feature request, so I am suggesting to you to convert this report into a feature request. Your feedback will be appreciated.
[31 Jul 2019 14:56]
Jeff Johnson
Converted to feature request. Just FYI, MS SQL Server has no problem with the parameters and runs the query quickly either way in just under 2 seconds, same data set.
[31 Jul 2019 15:01]
Jeff Johnson
When parameters are used not in a sub-query they do not suffer the performance issue as well, very odd.
[31 Jul 2019 15:10]
MySQL Verification Team
Hello Mr. Johnson, We do know that a problem is restricted to the nested queries. Simply, our optimiser currently concludes that if user variable is present that the nested query is a dependent one, which can not be converted to sem-join. Henceforth, this is very welcome feature request. Verified.
[2 Dec 2019 9:47]
Roy Lyseng
Hi Jeff, if you still have problems with this case, we'd like to reopen it. In version 8.0.18, we got a new feature EXPLAIN ANALYZE that will better provide information about what parts of a query contribute to the total cost. Please, can you run EXPLAIN ANALYZE on two queries: 1. The original query 2. The original query with user variables substituted with constant values. In version 8.0.17, we started optimizing NOT IN and NOT EXISTS in a hopefully better way, like a semi-join strategy. The new optimizer strategy can be de-selected with this setting: set optimizer_switch='semijoin=off'; If possible, can you try to run the query with both setting (semijoin on and off)? It is a bit tedious for us to try to recreate your data, and when you run the queries, we know it is the explained plan that causes you problems.
[3 Jan 2020 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".
[7 Jan 2020 14:48]
MySQL Verification Team
Hi Mr. Johnson, Is there a chance that we get a feedback from you on our questions ???