Description:
A nested loop join occurs when joining a table on a condition that does not belong to that table
The condition check that does not pertain to the joined table should ideally be evaluated first
Impact:
Queries execute 2.5 times slower than anticipated.
A manual selectivity estimation and the use of a scalar subquery are required as a workaround.
Selectivity estimate: table 2 is expected to have very few rows that match the constant JOIN condition.
How to repeat:
DROP TABLE IF EXISTS optimizer_test_1;
CREATE TABLE IF NOT EXISTS optimizer_test_1 (
id bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY,
val_1 double NOT NULL,
v_type tinyint UNSIGNED DEFAULT 0
);
DROP TABLE IF EXISTS optimizer_test_2;
CREATE TABLE IF NOT EXISTS optimizer_test_2 (
id bigint UNSIGNED PRIMARY KEY,
val_2 double NOT NULL
);
DELIMITER $$
CREATE PROCEDURE optimizer_test_generate_data ()
BEGIN
DECLARE var_id,
var_id_limit bigint UNSIGNED;
SET var_id_limit = 10000000;
SET var_id = 1;
START TRANSACTION;
WHILE var_id < var_id_limit DO
IF (var_id % 1000) = 0 THEN
INSERT INTO optimizer_test_1 (id, val_1, v_type)
VALUES (var_id, RAND(), 2);
IF RAND() > 0.5 THEN
INSERT INTO optimizer_test_2 (id, val_2)
VALUES (var_id, RAND());
END IF;
ELSE
INSERT INTO optimizer_test_1 (id, val_1, v_type)
VALUES (var_id, RAND(), 0);
END IF;
IF (var_id % 10000) = 0 THEN
COMMIT;
START TRANSACTION;
END IF;
SET var_id = var_id + 1;
END WHILE;
COMMIT;
END $$
DELIMITER ;
CALL optimizer_test_generate_data();
SET profiling = 1;
SET profiling = 0;
SHOW PROFILES;
SELECT
t1.id
,t1.val_1
,COALESCE(t2.val_2,0) AS val_2
FROM optimizer_test_1 t1
LEFT JOIN optimizer_test_2 t2 ON t1.v_type = 2 AND t1.id = t2.id # constant where for row !
WHERE t1.v_type IN (0,1,2,3,4) AND t1.val_1>=0.0
;
#12,22708925
#12,23878725
#12,246552
SELECT
t1.id
,t1.val_1
,COALESCE(t2.val_2,0) AS val_2
FROM optimizer_test_1 t1
LEFT JOIN LATERAL (select val_2 from optimizer_test_2 where optimizer_test_2.id = t1.id) AS t2 ON t1.v_type = 2 # constant where for row !
WHERE t1.v_type IN (0,1,2,3,4) AND t1.val_1>=0.0
;
#13,263449
#13,29376725
#13,326489
SELECT
t1.id
,t1.val_1
,COALESCE( IF( t1.v_type = 2, (select val_2 from optimizer_test_2 where optimizer_test_2.id = t1.id) ,0), 0 ) AS val_2 # constant where for row !
FROM optimizer_test_1 t1
WHERE t1.v_type IN (0,1,2,3,4) AND t1.val_1>=0.0
;
#4,9037905
#5,005257
#4,91212775
Suggested fix:
The static condition should be evaluated upfront, before the nested loop begins
Gathering this requires an enhancement to the BNL algorithm
