Bug #119030 Significant performance regression of INTERSECT operation in MySQL 9.4.0

• Submitted: 18 Sep 5:07
• Modified: 19 Sep 15:49
• Reporter: jinhui lai
• Status: Analyzing
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 9.4.0
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[18 Sep 5:07] jinhui lai

Description:
Our benchmarking reveals a significant performance regression in the INTERSECT operation for MySQL 9.4.0. When executing the same query, the runtime degraded from 30.345 seconds in version 9.3.0 to 35 minutes and 58.638 seconds in version 9.4.0.

How to repeat:
-- Create table t1 with 0 row and t2 with 10,000,000 rows
CREATE TABLE t1(c0 INT8);
CREATE TABLE t2(c0 INT8);

DELIMITER //
CREATE PROCEDURE batch_insert_numbers()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE batch_size INT DEFAULT 1000;
    
    WHILE i <= 10000000 DO
        START TRANSACTION;
        WHILE i <= 10000000 AND batch_size > 0 DO
            INSERT INTO t2(c0) VALUES (i);
            SET i = i + 1;
            SET batch_size = batch_size - 1;
        END WHILE;
        COMMIT;
        SET batch_size = 1000;
    END WHILE;
END //
DELIMITER ;

CALL batch_insert_numbers();

-- 9.4.0
-- docker run --name mysql-9.4.0 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -p 3306:3306 -d mysql:latest
SELECT * FROM t2 INTERSECT SELECT * FROM t1;
Empty set, 1 warning (35 min 58.638 sec)

-- MySQL 9.3.0
-- docker run --name mysql-9.3.0 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -p 3306:3306 -d mysql:9.3.0
SELECT * FROM t2 INTERSECT SELECT * FROM t1;
Empty set (30.345 sec)