Bug #100046 Very slow plan with combination of OR+noncorrelated subqueries+ORDER BY+LIMIT

• Submitted: 30 Jun 2020 9:43
• Modified: 30 Jun 2020 13:00
• Reporter: Saverio Miroddi
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.20
• OS: Any
• CPU Architecture: Any

[30 Jun 2020 9:43] Saverio Miroddi

Description:
On a query in the form:

    SELECT *
    FROM (table)
    WHERE
      id IN (noncorrelated subquery)
      OR id IN (noncorrelated subquery)
    ORDER BY (fields)
    LIMIT (num);

The optimizer rewrites the noncorrelated subqueries as correlated, causing a significantly suboptimal plan (>30" vs <0.10").

Several considerations can be made about the plan. One of the most interesting is that `LIMIT` is a necessary condition for the conversion of the subqueries to correlated: if the clause is removed, the subqueries are materialzed, leading to a still suboptimal, but now acceptable plan.

Rewriting the query as UNION leads to the optimal plan.

How to repeat:
Since the query reveals many details about our data model, I'm sending everything as private.

[30 Jun 2020 9:51] Saverio Miroddi

Uploaded dataset as `mysql-bug-data-100046.zip`.

[30 Jun 2020 10:19] Saverio Miroddi

Corrected the title.

[30 Jun 2020 13:00] MySQL Verification Team

Hi Mr. Miroddi,

Thank you for your bug report.

However, this is not a bug.

These nested queries are not correlated, but they are definitely dependent ones. That makes the execution much, much slower on any RDBMS. You can simplify your query and eliminate the nested queries. 

Not a bug.