|
Just an example a little closer to what I saw. Version 10.0.24..
CREATE TABLE t1 (id INT PRIMARY KEY);
|
INSERT t1 VALUES (1),(2),(3);
|
CREATE TABLE t2 (id INT PRIMARY KEY, acc_id INT, KEY(acc_id));
|
INSERT t2 VALUES (1,1),(2,2),(3,3);
|
EXPLAIN DELETE FROM t1 WHERE id IN (SELECT id FROM t2 WHERE acc_id = 9999); # Table scan first!
|
EXPLAIN DELETE uc FROM t1 AS uc WHERE uc.id IN (SELECT p.id FROM t2 p WHERE p.acc_id = 9999); # the plan we want
|
|
EXPLAIN DELETE FROM t1 WHERE t1.id IN (SELECT t2.id FROM t2 WHERE t2.acc_id = 9999) RETURNING t1.id; # Even aliasing like this produceds the poor plan
|
|