Bug #89739 | MySQL performs slowly due to missing basic optimisations | ||
---|---|---|---|
Submitted: | 21 Feb 2018 2:36 | Modified: | 15 Jan 2020 0:49 |
Reporter: | Yoseph Phillips | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
Version: | 5.7.21 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[21 Feb 2018 2:36]
Yoseph Phillips
[21 Feb 2018 4:38]
MySQL Verification Team
Hi Yoseph Phillips, This is not a bug. The MySQL optimizer is not ideal but we are working on making it better all the time. Your "how to repeat" is rather broad and with so few columns optimizer will certainly not (nor it can) calculate the same path as with large tables. To discuss how to best optimize your particular query you have issues with please contact our MySQL Support team as they can give you consulting on your particular query, how to tweak it (and/or the server settings) to get best out of your system. Since you say you are using EE binaries this means you already have a subscription so this type of consulting is included in your contract. best regards Bogdan
[21 Feb 2018 11:39]
Yoseph Phillips
I am not saying it is a bug as it is producing the correct results. It is more of an RFE and what should be simple to do with greater benefits compared to some of the other ones the Optimization team has been working on. We are using InnoDB and this small test case produces exactly the same execution plan as when we have huge tables with many columns which would be much harder to see what it is doing. We have already put in a workaround where we check if those ON conditions etc. would be impossible and remove them. Basically our code ends up like: if such and such is false then do simplified query else do complex query however we should not need to do things like that as MySQL could do that for us. MySQL already often checks for Impossible ON conditions and optimises them away. This is not the forum for discussing our client's experiences with using the MySQL EE Support Teams. What I am suggesting is that MySQL executes the query in the description the same way it would execute: SELECT t1.id FROM table_1 t1 INNER JOIN temporary_t4 t4 ON t4.id = t1.t4ID ORDER BY t1.id; They both give exactly the same results, however this simplified takes milliseconds for us, whereas the complex version takes minutes. (MySQL should know that FALSE AND anything is FALSE, and TRUE OR anything is TRUE, and then when it sees that the LEFT JOINs are not used anywhere it can safely ignore them). Number 3 in the description ('Using index; Using temporary; Using filesort') is not as important to us as these temporary tables tend to be small enough, it just seems strange that MySQL would be using filesort on a table that just has a single PRIMARY KEY column and nothing else. The table is already sorted. Hopefully that clarifies things further. Thanks, Yoseph
[22 Feb 2018 3:13]
MySQL Verification Team
Hi, It is clear what you wish but it is how optimizer works for now. I changed the bug to "feature request" and verified it but that's about it. There are only certain places where the impossible conditions are optimized out, it's not done "everywhere" and, as I mention, we do constantly improve the optimizer so it will happen in future. all best Bogdan