Bug #91598 Anti-joins running very slowly
Submitted: 11 Jul 2018 11:52 Modified: 12 Jul 2018 12:51
Reporter: Greg Obenshain Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.11 OS:Microsoft Windows (Microsoft Windows 10 Home)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[11 Jul 2018 11:52] Greg Obenshain
Description:
Any query that references another table to identify records in the first table that are not in the second table runs significantly slower that on the previous version of MySQL, even if indexed. 

Examples include 
SELECT b.id from b LEFT JOIN a ON b.id = a.id WHERE a.id IS NULL

SELECT b.id from b WHERE b.id NOT IN (SELECT a.id FROM a)

How to repeat:
Can't repeat because I've upgraded from the old version, but I am running the exact same queries on the same data that I ran before. The problem happens ony  any query using an anti-join

Suggested fix:
I think as similar issue existed between MySQL 3 and MySQL 5 based on my readings of message boards.
[11 Jul 2018 15:08] Sinisa Milivojevic
Performance regressions in the single-threaded read-only, but also in read-write operations, is quite well known phenomena.

This bug is a duplicate of this bug:

https://bugs.mysql.com/bug.php?id=68825
[11 Jul 2018 15:10] Sinisa Milivojevic
This bug could also be a duplicate of this one:

https://bugs.mysql.com/bug.php?id=68979
[12 Jul 2018 12:51] Sinisa Milivojevic
We concluded that we can not claim that this is a duplicate. That is because we do not have performance info from previous versions, nor do we have optimiser plans and traces from both versions. Last , but not least, we can not repeat the behaviour without full dumps and all data mentioned above.