Bug #118198 Different intersection orders lead to more than 20,000 times performance gap

• Submitted: 14 May 14:16
• Modified: 15 May 20:16
• Reporter: jinhui lai
• Status: Analyzing
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.3.0
• OS: Ubuntu (22.04)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[14 May 14:16] jinhui lai

Description:
Hi, MySQL Developers,

Please consider such two queries with different intersection orders: 

(1) SELECT * FROM empty_table INTERSECT SELECT * FROM not_empty_table;
(2) SELECT * FROM not_empty_table INTERSECT SELECT * FROM empty_table;

The first query returns an empty set quickly in MySQL. However, the second query takes over 20,000 times longer to execute. Moreover, the execution time is proportional to the amount of table data.

Obviously, the above tow queries always returns an empty set. I think it should return an empty set quickly. However, it waste much time when executing the second query. MySQL fails to optimize queries containing INTERSECT operations when the right branch includes an empty table. This leads to unnecessary memory consumption and query cancellation, even though the result should be deterministically empty.

I think this is a common case in actual production scenarios. It's important to clarify that users might not intentionally perform INTERSECT operations on empty tables. Rather, they may be unaware that a table is empty . For example, when data has been deleted by another user or process. If MySQL can address this performance bug, it would significantly improve query efficiency and save users valuable time in such cases.

Thank you for your valuable time, looking forward to your reply!

Best regard,
Jinhui Lai

How to repeat:
mysql> use mysql;
mysql> CREATE TABLE empty_table AS SELECT * FROM time_zone_transition CROSS JOIN user LIMIT 0;
Query OK, 0 rows affected (0.073 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE not_empty_table AS SELECT * FROM time_zone_transition CROSS JOIN user;
Query OK, 590075 rows affected (57.880 sec)
Records: 590075  Duplicates: 0  Warnings: 0

-- empty_table INTERSECT not_empty_table
mysql> SELECT * FROM empty_table INTERSECT SELECT * FROM not_empty_table;
Empty set (0.001 sec)

-- not_empty_table INTERSECT empty_table
mysql> SELECT * FROM not_empty_table INTERSECT SELECT * FROM empty_table;
Empty set (23.485 sec) --expect: Empty set (0.xxx sec)