set @save_default_engine=@@default_storage_engine; ####################################### # # # Engine InnoDB # # # ####################################### set global innodb_stats_persistent=1; set default_storage_engine=InnoDB; create table t1 (c1 integer, c2 integer, c3 integer); insert into t1(c1,c2,c3) values (1,1,1),(1,2,2),(1,3,3), (2,1,4),(2,2,5),(2,3,6), (2,4,7),(2,5,8); insert into t1 select c1+10,c2,c3+10 from t1; insert into t1 select c1+20,c2+1,c3+20 from t1; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create view v1 as select * from t1 where c2=2; ####################################### # Test without any index # ####################################### # # Delete with value from subquery on the same table # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); affected rows: 4 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + possibly sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 9.38 9.38 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 12.00 3.12 5.56 Using where; FirstMatch(t1) select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with IN predicand over the updated table in WHERE # create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); affected rows: 8 select * from t1; c1 c2 c3 1 3 3 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit - can be deleted # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 12.50 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 24.50 3.12 1.02 Using where; FirstMatch(t1) select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit and an order by # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete: 2 execution of PS # prepare create_tmp_stmt from "create table tmp as select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; prepare delete_t1_stmt from "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=5; execute create_tmp_stmt using @a; execute delete_t1_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 2 1 4 2 2 5 2 3 6 2 4 7 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 6 38 prepare insert_tmp_stmt from "insert into tmp(c1,c2,c3) select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=2; execute insert_tmp_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 2 1 4 2 3 6 2 4 7 21 3 22 21 4 23 22 3 25 22 4 26 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 drop table tmp; # # Delete in stored procedure # create procedure sp() begin delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; end // create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop procedure sp; drop table tmp; # # Delete in stored function # create function f1(IN a INT) returns int begin delete from t1 where c3 < a order by c3 limit 1; return 1; end;// set @a:=7; create table tmp as select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop function f1; drop table tmp; # # Delete in trigger # create table t2 (c1 integer); insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); CREATE TABLE cnt(del integer); INSERT INTO cnt VALUES(0); CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE cnt SET del=del+1; CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; DELETE FROM t1 WHERE c2>=3; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 SELECT * FROM t2; c1 SELECT * FROM cnt; del 20 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; DROP TRIGGER tr1; DROP TRIGGER tr2; drop table t2, cnt, tmp; # # Delete with a reference to view in subquery # create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 32.00 25.00 3.12 Using where select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; ####################################### # Test with an index # ####################################### create index t1_c2 on t1 (c2,c1); # # Delete with value from subquery on the same table # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); affected rows: 4 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + possibly sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1) analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 32.00 9.38 9.38 Using where 1 PRIMARY a index NULL t1_c2 10 NULL 32 11.67 3.12 5.71 Using where; Using index; FirstMatch(t1) select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with IN predicand over the updated table in WHERE # create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 1 Using index; FirstMatch(t1) explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 1 Using index; FirstMatch(t1) delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); affected rows: 8 select * from t1; c1 c2 c3 1 3 3 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit - can be deleted # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using index; FirstMatch(t1) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using index; FirstMatch(t1) analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 12.50 Using where 1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 0.25 18.75 100.00 Using index; FirstMatch(t1) select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit and an order by # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort 1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort 2 DEPENDENT SUBQUERY a index_subquery t1_c2 t1_c2 5 func 5 Using where delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete: 2 execution of PS # prepare create_tmp_stmt from "create table tmp as select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; prepare delete_t1_stmt from "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=5; execute create_tmp_stmt using @a; execute delete_t1_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 2 1 4 2 2 5 2 3 6 2 4 7 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 6 38 prepare insert_tmp_stmt from "insert into tmp(c1,c2,c3) select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=2; execute insert_tmp_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 2 1 4 2 3 6 2 4 7 21 3 22 21 4 23 22 3 25 22 4 26 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 drop table tmp; # # Delete in stored procedure # create procedure sp() begin delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; end // create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop procedure sp; drop table tmp; # # Delete in stored function # create function f1(IN a INT) returns int begin delete from t1 where c3 < a order by c3 limit 1; return 1; end;// set @a:=7; create table tmp as select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop function f1; drop table tmp; # # Delete in trigger # create table t2 (c1 integer); insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); CREATE TABLE cnt(del integer); INSERT INTO cnt VALUES(0); CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE cnt SET del=del+1; CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; DELETE FROM t1 WHERE c2>=3; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 SELECT * FROM t2; c1 SELECT * FROM cnt; del 20 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; DROP TRIGGER tr1; DROP TRIGGER tr2; drop table t2, cnt, tmp; # # Delete with a reference to view in subquery # create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where 2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 1.00 100.00 100.00 Using index select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; ####################################### # Test with a primary key # ####################################### drop index t1_c2 on t1; alter table t1 add primary key (c3); # # Delete with value from subquery on the same table # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); affected rows: 4 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + possibly sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 9.38 9.38 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 12.33 3.12 5.41 Using where; FirstMatch(t1) select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with IN predicand over the updated table in WHERE # create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); affected rows: 8 select * from t1; c1 c2 c3 1 3 3 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit - can be deleted # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 1.00 100.00 100.00 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 1.00 3.12 100.00 Using where; End temporary select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit and an order by # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 1 1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 1 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete: 2 execution of PS # prepare create_tmp_stmt from "create table tmp as select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; prepare delete_t1_stmt from "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=5; execute create_tmp_stmt using @a; execute delete_t1_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 2 1 4 2 2 5 2 3 6 2 4 7 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 6 38 prepare insert_tmp_stmt from "insert into tmp(c1,c2,c3) select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=2; execute insert_tmp_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 2 1 4 2 3 6 2 4 7 21 3 22 21 4 23 22 3 25 22 4 26 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 drop table tmp; # # Delete in stored procedure # create procedure sp() begin delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; end // create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop procedure sp; drop table tmp; # # Delete in stored function # create function f1(IN a INT) returns int begin delete from t1 where c3 < a order by c3 limit 1; return 1; end;// set @a:=7; create table tmp as select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop function f1; drop table tmp; # # Delete in trigger # create table t2 (c1 integer); insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); CREATE TABLE cnt(del integer); INSERT INTO cnt VALUES(0); CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE cnt SET del=del+1; CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; DELETE FROM t1 WHERE c2>=3; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 SELECT * FROM t2; c1 SELECT * FROM cnt; del 20 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; DROP TRIGGER tr1; DROP TRIGGER tr2; drop table t2, cnt, tmp; # # Delete with a reference to view in subquery # create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 32.00 25.00 3.12 Using where select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; drop view v1; drop table t1; # # Test on dynamic columns (blob) # create table assets ( item_name varchar(32) primary key, -- A common attribute for all items dynamic_cols blob -- Dynamic columns will be stored here ); INSERT INTO assets VALUES ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL')); INSERT INTO assets VALUES ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500)); INSERT INTO assets VALUES ('Fridge', COLUMN_CREATE('color', 'white', 'warranty', '5 years')); INSERT INTO assets VALUES ('Microwave', COLUMN_CREATE('warranty', '3 years')); SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets ORDER BY item_name; item_name color Fridge white MariaDB T-shirt blue Microwave NULL Thinkpad Laptop black UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, 'color') WHERE item_name='Fridge'; SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets ORDER BY item_name; item_name color Fridge NULL MariaDB T-shirt blue Microwave NULL Thinkpad Laptop black DELETE FROM assets WHERE item_name in (select b.item_name from assets b where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black'); SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets ORDER BY item_name; item_name color Fridge NULL MariaDB T-shirt blue Microwave NULL DELETE FROM assets WHERE item_name='Microwave'; SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets ORDER BY item_name; item_name color Fridge NULL MariaDB T-shirt blue drop table assets ; # # Test on fulltext columns # CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)); INSERT INTO ft2(copy) VALUES ('MySQL vs MariaDB database'), ('Oracle vs MariaDB database'), ('PostgreSQL vs MariaDB database'), ('MariaDB overview'), ('Foreign keys'), ('Primary keys'), ('Indexes'), ('Transactions'), ('Triggers'); SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); copy MySQL vs MariaDB database Oracle vs MariaDB database PostgreSQL vs MariaDB database DELETE FROM ft2 WHERE MATCH(copy) AGAINST('database'); SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); copy drop table ft2; ####################################### # # # Engine Aria # # # ####################################### set default_storage_engine=Aria; create table t1 (c1 integer, c2 integer, c3 integer); insert into t1(c1,c2,c3) values (1,1,1),(1,2,2),(1,3,3), (2,1,4),(2,2,5),(2,3,6), (2,4,7),(2,5,8); insert into t1 select c1+10,c2,c3+10 from t1; insert into t1 select c1+20,c2+1,c3+20 from t1; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create view v1 as select * from t1 where c2=2; ####################################### # Test without any index # ####################################### # # Delete with value from subquery on the same table # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); affected rows: 4 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + possibly sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 9.38 9.38 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 12.67 3.12 5.26 Using where; FirstMatch(t1) select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with IN predicand over the updated table in WHERE # create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); affected rows: 8 select * from t1; c1 c2 c3 1 3 3 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit - can be deleted # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 25.00 100.00 12.00 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 22.67 3.12 1.47 Using where; FirstMatch(t1) select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit and an order by # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete: 2 execution of PS # prepare create_tmp_stmt from "create table tmp as select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; prepare delete_t1_stmt from "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=5; execute create_tmp_stmt using @a; execute delete_t1_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 2 1 4 2 2 5 2 3 6 2 4 7 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 6 38 prepare insert_tmp_stmt from "insert into tmp(c1,c2,c3) select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=2; execute insert_tmp_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 2 1 4 2 3 6 2 4 7 21 3 22 21 4 23 22 3 25 22 4 26 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 drop table tmp; # # Delete in stored procedure # create procedure sp() begin delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; end // create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop procedure sp; drop table tmp; # # Delete in stored function # create function f1(IN a INT) returns int begin delete from t1 where c3 < a order by c3 limit 1; return 1; end;// set @a:=7; create table tmp as select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop function f1; drop table tmp; # # Delete in trigger # create table t2 (c1 integer); insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); CREATE TABLE cnt(del integer); INSERT INTO cnt VALUES(0); CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE cnt SET del=del+1; CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; DELETE FROM t1 WHERE c2>=3; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 SELECT * FROM t2; c1 SELECT * FROM cnt; del 20 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; DROP TRIGGER tr1; DROP TRIGGER tr2; drop table t2, cnt, tmp; # # Delete with a reference to view in subquery # create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 32.00 25.00 3.12 Using where select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + non-sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; affected rows: 8 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 12 4 17 12 5 18 2 1 4 2 2 5 2 4 7 2 5 8 21 2 21 21 4 23 22 2 24 22 4 26 22 5 27 22 6 28 31 2 31 31 4 33 32 2 34 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with order by # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; Using filesort 1 PRIMARY eq_ref distinct_key distinct_key 4 func # 2 MATERIALIZED a ALL NULL NULL NULL NULL # explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func # 2 MATERIALIZED a ALL NULL NULL NULL NULL # analyze delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 62.50 62.50 Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func # 1.00 100.00 100.00 2 MATERIALIZED a ALL NULL NULL NULL NULL # 32.00 62.50 100.00 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; explain select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; affected rows: 0 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view using reference # to the same view in subquery # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 3.91 3.12 Using where 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 5.00 25.00 20.00 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # 32.00 100.00 3.12 Using where select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; ####################################### # Test with an index # ####################################### create index t1_c2 on t1 (c2,c1); # # Delete with value from subquery on the same table # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); affected rows: 4 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + possibly sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1) analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 32.00 9.38 9.38 Using where 1 PRIMARY a index NULL t1_c2 10 NULL 32 11.67 3.12 5.71 Using where; Using index; FirstMatch(t1) select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with IN predicand over the updated table in WHERE # create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 1 Using index; FirstMatch(t1) explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 1 Using index; FirstMatch(t1) delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); affected rows: 8 select * from t1; c1 c2 c3 1 3 3 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit - can be deleted # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using index; FirstMatch(t1) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using index; FirstMatch(t1) analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 27.00 100.00 14.81 Using where 1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 0.25 18.75 100.00 Using index; FirstMatch(t1) select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit and an order by # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort 1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using where; FirstMatch(t1) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort 2 DEPENDENT SUBQUERY a index_subquery t1_c2 t1_c2 5 func 5 Using where delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete: 2 execution of PS # prepare create_tmp_stmt from "create table tmp as select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; prepare delete_t1_stmt from "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=5; execute create_tmp_stmt using @a; execute delete_t1_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 2 1 4 2 2 5 2 3 6 2 4 7 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 6 38 prepare insert_tmp_stmt from "insert into tmp(c1,c2,c3) select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=2; execute insert_tmp_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 2 1 4 2 3 6 2 4 7 21 3 22 21 4 23 22 3 25 22 4 26 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 drop table tmp; # # Delete in stored procedure # create procedure sp() begin delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; end // create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop procedure sp; drop table tmp; # # Delete in stored function # create function f1(IN a INT) returns int begin delete from t1 where c3 < a order by c3 limit 1; return 1; end;// set @a:=7; create table tmp as select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop function f1; drop table tmp; # # Delete in trigger # create table t2 (c1 integer); insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); CREATE TABLE cnt(del integer); INSERT INTO cnt VALUES(0); CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE cnt SET del=del+1; CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; DELETE FROM t1 WHERE c2>=3; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 SELECT * FROM t2; c1 SELECT * FROM cnt; del 20 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; DROP TRIGGER tr1; DROP TRIGGER tr2; drop table t2, cnt, tmp; # # Delete with a reference to view in subquery # create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where 2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 1.00 100.00 100.00 Using index select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + non-sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 1 PRIMARY a ref t1_c2 t1_c2 5 const 8 Using index; FirstMatch(t1) explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 1 PRIMARY a ref t1_c2 t1_c2 5 const 8 Using index; FirstMatch(t1) delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; affected rows: 8 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 12 4 17 12 5 18 2 1 4 2 2 5 2 4 7 2 5 8 21 2 21 21 4 23 22 2 24 22 4 26 22 5 27 22 6 28 31 2 31 31 4 33 32 2 34 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with order by # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 range t1_c2 t1_c2 5 NULL # Using index condition 1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 # Using index; FirstMatch(t1) explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a index t1_c2 t1_c2 10 NULL # Using where; Using index; LooseScan 1 PRIMARY t1 ref t1_c2 t1_c2 5 test.a.c2 # analyze delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY a index t1_c2 t1_c2 10 NULL # 16.00 20.00 25.00 Using where; Using index; LooseScan 1 PRIMARY t1 ref t1_c2 t1_c2 5 test.a.c2 # 5.00 4.76 100.00 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; explain select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using where 2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using where 2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; affected rows: 0 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view using reference # to the same view in subquery # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # Using where 1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # Using index; FirstMatch(t1) 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # Using where 3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func # Using where; Using index 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # 1.00 100.00 100.00 Using where 3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func # 1.00 100.00 100.00 Using where; Using index 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # 32.00 100.00 3.12 Using where select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; ####################################### # Test with a primary key # ####################################### drop index t1_c2 on t1; alter table t1 add primary key (c3); # # Delete with value from subquery on the same table # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); affected rows: 4 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + possibly sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 9.38 9.38 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 12.00 3.12 5.56 Using where; FirstMatch(t1) select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with IN predicand over the updated table in WHERE # create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); affected rows: 8 select * from t1; c1 c2 c3 1 3 3 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit - can be deleted # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 4.00 100.00 100.00 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 1.00 3.12 25.00 Using where; End temporary select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit and an order by # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 1 1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 1 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete: 2 execution of PS # prepare create_tmp_stmt from "create table tmp as select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; prepare delete_t1_stmt from "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=5; execute create_tmp_stmt using @a; execute delete_t1_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 2 1 4 2 2 5 2 3 6 2 4 7 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 6 38 prepare insert_tmp_stmt from "insert into tmp(c1,c2,c3) select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=2; execute insert_tmp_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 2 1 4 2 3 6 2 4 7 21 3 22 21 4 23 22 3 25 22 4 26 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 drop table tmp; # # Delete in stored procedure # create procedure sp() begin delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; end // create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop procedure sp; drop table tmp; # # Delete in stored function # create function f1(IN a INT) returns int begin delete from t1 where c3 < a order by c3 limit 1; return 1; end;// set @a:=7; create table tmp as select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop function f1; drop table tmp; # # Delete in trigger # create table t2 (c1 integer); insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); CREATE TABLE cnt(del integer); INSERT INTO cnt VALUES(0); CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE cnt SET del=del+1; CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; DELETE FROM t1 WHERE c2>=3; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 SELECT * FROM t2; c1 SELECT * FROM cnt; del 20 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; DROP TRIGGER tr1; DROP TRIGGER tr2; drop table t2, cnt, tmp; # # Delete with a reference to view in subquery # create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 32.00 25.00 3.12 Using where select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + non-sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; affected rows: 8 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 12 4 17 12 5 18 2 1 4 2 2 5 2 4 7 2 5 8 21 2 21 21 4 23 22 2 24 22 4 26 22 5 27 22 6 28 31 2 31 31 4 33 32 2 34 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with order by # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; Using filesort 1 PRIMARY eq_ref distinct_key distinct_key 4 func # 2 MATERIALIZED a ALL NULL NULL NULL NULL # explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func # 2 MATERIALIZED a ALL NULL NULL NULL NULL # analyze delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 62.50 62.50 Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func # 1.00 100.00 100.00 2 MATERIALIZED a ALL NULL NULL NULL NULL # 32.00 62.50 100.00 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; explain select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; affected rows: 0 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view using reference # to the same view in subquery # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 # explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 # analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 3.91 3.12 Using where 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 6.00 25.00 16.67 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 # 1.00 100.00 100.00 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; drop view v1; drop table t1; # # Test on dynamic columns (blob) # create table assets ( item_name varchar(32) primary key, -- A common attribute for all items dynamic_cols blob -- Dynamic columns will be stored here ); INSERT INTO assets VALUES ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL')); INSERT INTO assets VALUES ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500)); INSERT INTO assets VALUES ('Fridge', COLUMN_CREATE('color', 'white', 'warranty', '5 years')); INSERT INTO assets VALUES ('Microwave', COLUMN_CREATE('warranty', '3 years')); SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets ORDER BY item_name; item_name color Fridge white MariaDB T-shirt blue Microwave NULL Thinkpad Laptop black UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, 'color') WHERE item_name='Fridge'; SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets ORDER BY item_name; item_name color Fridge NULL MariaDB T-shirt blue Microwave NULL Thinkpad Laptop black DELETE FROM assets WHERE item_name in (select b.item_name from assets b where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black'); SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets ORDER BY item_name; item_name color Fridge NULL MariaDB T-shirt blue Microwave NULL DELETE FROM assets WHERE item_name='Microwave'; SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets ORDER BY item_name; item_name color Fridge NULL MariaDB T-shirt blue drop table assets ; # # Test on fulltext columns # CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)); INSERT INTO ft2(copy) VALUES ('MySQL vs MariaDB database'), ('Oracle vs MariaDB database'), ('PostgreSQL vs MariaDB database'), ('MariaDB overview'), ('Foreign keys'), ('Primary keys'), ('Indexes'), ('Transactions'), ('Triggers'); SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); copy MySQL vs MariaDB database Oracle vs MariaDB database PostgreSQL vs MariaDB database DELETE FROM ft2 WHERE MATCH(copy) AGAINST('database'); SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); copy drop table ft2; ####################################### # # # Engine MyISAM # # # ####################################### set default_storage_engine=MyISAM; create table t1 (c1 integer, c2 integer, c3 integer); insert into t1(c1,c2,c3) values (1,1,1),(1,2,2),(1,3,3), (2,1,4),(2,2,5),(2,3,6), (2,4,7),(2,5,8); insert into t1 select c1+10,c2,c3+10 from t1; insert into t1 select c1+20,c2+1,c3+20 from t1; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create view v1 as select * from t1 where c2=2; ####################################### # Test without any index # ####################################### # # Delete with value from subquery on the same table # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); affected rows: 4 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + possibly sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 9.38 9.38 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 12.67 3.12 5.26 Using where; FirstMatch(t1) select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with IN predicand over the updated table in WHERE # create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); affected rows: 8 select * from t1; c1 c2 c3 1 3 3 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit - can be deleted # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 25.00 100.00 12.00 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 22.67 3.12 1.47 Using where; FirstMatch(t1) select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit and an order by # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete: 2 execution of PS # prepare create_tmp_stmt from "create table tmp as select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; prepare delete_t1_stmt from "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=5; execute create_tmp_stmt using @a; execute delete_t1_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 2 1 4 2 2 5 2 3 6 2 4 7 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 6 38 prepare insert_tmp_stmt from "insert into tmp(c1,c2,c3) select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=2; execute insert_tmp_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 2 1 4 2 3 6 2 4 7 21 3 22 21 4 23 22 3 25 22 4 26 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 drop table tmp; # # Delete in stored procedure # create procedure sp() begin delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; end // create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop procedure sp; drop table tmp; # # Delete in stored function # create function f1(IN a INT) returns int begin delete from t1 where c3 < a order by c3 limit 1; return 1; end;// set @a:=7; create table tmp as select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop function f1; drop table tmp; # # Delete in trigger # create table t2 (c1 integer); insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); CREATE TABLE cnt(del integer); INSERT INTO cnt VALUES(0); CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE cnt SET del=del+1; CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; DELETE FROM t1 WHERE c2>=3; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 SELECT * FROM t2; c1 SELECT * FROM cnt; del 20 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; DROP TRIGGER tr1; DROP TRIGGER tr2; drop table t2, cnt, tmp; # # Delete with a reference to view in subquery # create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 32.00 25.00 3.12 Using where select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + non-sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; affected rows: 8 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 12 4 17 12 5 18 2 1 4 2 2 5 2 4 7 2 5 8 21 2 21 21 4 23 22 2 24 22 4 26 22 5 27 22 6 28 31 2 31 31 4 33 32 2 34 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with order by # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; Using filesort 1 PRIMARY eq_ref distinct_key distinct_key 4 func # 2 MATERIALIZED a ALL NULL NULL NULL NULL # explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func # 2 MATERIALIZED a ALL NULL NULL NULL NULL # analyze delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 62.50 62.50 Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func # 1.00 100.00 100.00 2 MATERIALIZED a ALL NULL NULL NULL NULL # 32.00 62.50 100.00 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; explain select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; affected rows: 0 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view using reference # to the same view in subquery # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 3.91 3.12 Using where 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 5.00 25.00 20.00 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # 32.00 100.00 3.12 Using where select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; ####################################### # Test with an index # ####################################### create index t1_c2 on t1 (c2,c1); # # Delete with value from subquery on the same table # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); affected rows: 4 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + possibly sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a index NULL t1_c2 10 NULL 32 Using where; Using index; FirstMatch(t1) analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 32.00 9.38 9.38 Using where 1 PRIMARY a index NULL t1_c2 10 NULL 32 11.67 3.12 5.71 Using where; Using index; FirstMatch(t1) select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with IN predicand over the updated table in WHERE # create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 1 Using index; FirstMatch(t1) explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 1 Using index; FirstMatch(t1) delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); affected rows: 8 select * from t1; c1 c2 c3 1 3 3 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit - can be deleted # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using index; FirstMatch(t1) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 Using index; FirstMatch(t1) analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 29.00 100.00 13.79 Using where 1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c1 5 0.25 18.75 100.00 Using index; FirstMatch(t1) select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit and an order by # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort 1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort 2 DEPENDENT SUBQUERY a index_subquery t1_c2 t1_c2 5 func 5 Using where delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete: 2 execution of PS # prepare create_tmp_stmt from "create table tmp as select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; prepare delete_t1_stmt from "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=5; execute create_tmp_stmt using @a; execute delete_t1_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 2 1 4 2 2 5 2 3 6 2 4 7 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 6 38 prepare insert_tmp_stmt from "insert into tmp(c1,c2,c3) select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=2; execute insert_tmp_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 2 1 4 2 3 6 2 4 7 21 3 22 21 4 23 22 3 25 22 4 26 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 drop table tmp; # # Delete in stored procedure # create procedure sp() begin delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; end // create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop procedure sp; drop table tmp; # # Delete in stored function # create function f1(IN a INT) returns int begin delete from t1 where c3 < a order by c3 limit 1; return 1; end;// set @a:=7; create table tmp as select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop function f1; drop table tmp; # # Delete in trigger # create table t2 (c1 integer); insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); CREATE TABLE cnt(del integer); INSERT INTO cnt VALUES(0); CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE cnt SET del=del+1; CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; DELETE FROM t1 WHERE c2>=3; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 SELECT * FROM t2; c1 SELECT * FROM cnt; del 20 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; DROP TRIGGER tr1; DROP TRIGGER tr2; drop table t2, cnt, tmp; # # Delete with a reference to view in subquery # create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 Using index analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where 2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 1 1.00 100.00 100.00 Using index select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + non-sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 1 PRIMARY a ref t1_c2 t1_c2 5 const 8 Using index; FirstMatch(t1) explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 1 PRIMARY a ref t1_c2 t1_c2 5 const 8 Using index; FirstMatch(t1) delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; affected rows: 8 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 12 4 17 12 5 18 2 1 4 2 2 5 2 4 7 2 5 8 21 2 21 21 4 23 22 2 24 22 4 26 22 5 27 22 6 28 31 2 31 31 4 33 32 2 34 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with order by # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL # Using where; Using filesort 1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 # Using index; FirstMatch(t1) explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a index t1_c2 t1_c2 10 NULL # Using where; Using index; LooseScan 1 PRIMARY t1 ref t1_c2 t1_c2 5 test.a.c2 # analyze delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY a index t1_c2 t1_c2 10 NULL # 16.00 20.00 25.00 Using where; Using index; LooseScan 1 PRIMARY t1 ref t1_c2 t1_c2 5 test.a.c2 # 5.00 5.00 100.00 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; explain select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using where 2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 5 const 8 Using where 2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 5 Using index delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; affected rows: 0 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view using reference # to the same view in subquery # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # Using where 1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # Using index; FirstMatch(t1) 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # Using where 3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func # Using where; Using index 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # Using where analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const # 1.00 100.00 100.00 Using where 3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func # 1.00 100.00 100.00 Using where; Using index 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL # 32.00 100.00 3.12 Using where select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; ####################################### # Test with a primary key # ####################################### drop index t1_c2 on t1; alter table t1 add primary key (c3); # # Delete with value from subquery on the same table # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using index delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); affected rows: 4 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + possibly sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) analyze delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 9.38 9.38 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 13.33 3.12 5.00 Using where; FirstMatch(t1) select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with IN predicand over the updated table in WHERE # create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); affected rows: 8 select * from t1; c1 c2 c3 1 3 3 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit - can be deleted # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary analyze delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 4.00 100.00 100.00 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 1.00 3.12 25.00 Using where; End temporary select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit and an order by # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 1 1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL PRIMARY 4 NULL 1 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete: 2 execution of PS # prepare create_tmp_stmt from "create table tmp as select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; prepare delete_t1_stmt from "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=5; execute create_tmp_stmt using @a; execute delete_t1_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 2 1 4 2 2 5 2 3 6 2 4 7 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 6 38 prepare insert_tmp_stmt from "insert into tmp(c1,c2,c3) select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=2; execute insert_tmp_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 2 1 4 2 3 6 2 4 7 21 3 22 21 4 23 22 3 25 22 4 26 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 drop table tmp; # # Delete in stored procedure # create procedure sp() begin delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; end // create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop procedure sp; drop table tmp; # # Delete in stored function # create function f1(IN a INT) returns int begin delete from t1 where c3 < a order by c3 limit 1; return 1; end;// set @a:=7; create table tmp as select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop function f1; drop table tmp; # # Delete in trigger # create table t2 (c1 integer); insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); CREATE TABLE cnt(del integer); INSERT INTO cnt VALUES(0); CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE cnt SET del=del+1; CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; DELETE FROM t1 WHERE c2>=3; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 SELECT * FROM t2; c1 SELECT * FROM cnt; del 20 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; DROP TRIGGER tr1; DROP TRIGGER tr2; drop table t2, cnt, tmp; # # Delete with a reference to view in subquery # create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where analyze delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 32.00 100.00 25.00 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 32.00 25.00 3.12 Using where select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + non-sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; affected rows: 8 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 12 4 17 12 5 18 2 1 4 2 2 5 2 4 7 2 5 8 21 2 21 21 4 23 22 2 24 22 4 26 22 5 27 22 6 28 31 2 31 31 4 33 32 2 34 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with order by # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; Using filesort 1 PRIMARY eq_ref distinct_key distinct_key 4 func # 2 MATERIALIZED a ALL NULL NULL NULL NULL # explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func # 2 MATERIALIZED a ALL NULL NULL NULL NULL # analyze delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 62.50 62.50 Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func # 1.00 100.00 100.00 2 MATERIALIZED a ALL NULL NULL NULL NULL # 32.00 62.50 100.00 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create table tmp as select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; explain select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; affected rows: 0 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view using reference # to the same view in subquery # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 # explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 # analyze delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # 32.00 3.91 3.12 Using where 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # 3.00 25.00 33.33 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 # 1.00 100.00 100.00 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; drop view v1; drop table t1; # # Test on dynamic columns (blob) # create table assets ( item_name varchar(32) primary key, -- A common attribute for all items dynamic_cols blob -- Dynamic columns will be stored here ); INSERT INTO assets VALUES ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL')); INSERT INTO assets VALUES ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500)); INSERT INTO assets VALUES ('Fridge', COLUMN_CREATE('color', 'white', 'warranty', '5 years')); INSERT INTO assets VALUES ('Microwave', COLUMN_CREATE('warranty', '3 years')); SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets ORDER BY item_name; item_name color Fridge white MariaDB T-shirt blue Microwave NULL Thinkpad Laptop black UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, 'color') WHERE item_name='Fridge'; SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets ORDER BY item_name; item_name color Fridge NULL MariaDB T-shirt blue Microwave NULL Thinkpad Laptop black DELETE FROM assets WHERE item_name in (select b.item_name from assets b where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black'); SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets ORDER BY item_name; item_name color Fridge NULL MariaDB T-shirt blue Microwave NULL DELETE FROM assets WHERE item_name='Microwave'; SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets ORDER BY item_name; item_name color Fridge NULL MariaDB T-shirt blue drop table assets ; # # Test on fulltext columns # CREATE TABLE ft2(copy TEXT,FULLTEXT(copy)); INSERT INTO ft2(copy) VALUES ('MySQL vs MariaDB database'), ('Oracle vs MariaDB database'), ('PostgreSQL vs MariaDB database'), ('MariaDB overview'), ('Foreign keys'), ('Primary keys'), ('Indexes'), ('Transactions'), ('Triggers'); SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); copy MySQL vs MariaDB database Oracle vs MariaDB database PostgreSQL vs MariaDB database DELETE FROM ft2 WHERE MATCH(copy) AGAINST('database'); SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database'); copy drop table ft2; ####################################### # # # Engine MEMORY # # # ####################################### set default_storage_engine=MEMORY; create table t1 (c1 integer, c2 integer, c3 integer); insert into t1(c1,c2,c3) values (1,1,1),(1,2,2),(1,3,3), (2,1,4),(2,2,5),(2,3,6), (2,4,7),(2,5,8); insert into t1 select c1+10,c2,c3+10 from t1; insert into t1 select c1+20,c2+1,c3+20 from t1; analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create view v1 as select * from t1 where c2=2; ####################################### # Test without any index # ####################################### # # Delete with value from subquery on the same table # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); affected rows: 4 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + possibly sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); affected rows: 2 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + non-sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; affected rows: 8 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 12 4 17 12 5 18 2 1 4 2 2 5 2 4 7 2 5 8 21 2 21 21 4 23 22 2 24 22 4 26 22 5 27 22 6 28 31 2 31 31 4 33 32 2 34 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with order by # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED a ALL NULL NULL NULL NULL 32 explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED a ALL NULL NULL NULL NULL 32 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with IN predicand over the updated table in WHERE # create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); affected rows: 8 select * from t1; c1 c2 c3 1 3 3 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit - can be deleted # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit and an order by # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete: 2 execution of PS # prepare create_tmp_stmt from "create table tmp as select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; prepare delete_t1_stmt from "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=5; execute create_tmp_stmt using @a; execute delete_t1_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 2 1 4 2 2 5 2 3 6 2 4 7 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 6 38 prepare insert_tmp_stmt from "insert into tmp(c1,c2,c3) select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=2; execute insert_tmp_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 2 1 4 2 3 6 2 4 7 21 3 22 21 4 23 22 3 25 22 4 26 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 drop table tmp; # # Delete in stored procedure # create procedure sp() begin delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; end // create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop procedure sp; drop table tmp; # # Delete in stored function # create function f1(IN a INT) returns int begin delete from t1 where c3 < a order by c3 limit 1; return 1; end;// set @a:=7; create table tmp as select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop function f1; drop table tmp; # # Delete in trigger # create table t2 (c1 integer); insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); CREATE TABLE cnt(del integer); INSERT INTO cnt VALUES(0); CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE cnt SET del=del+1; CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; DELETE FROM t1 WHERE c2>=3; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 SELECT * FROM t2; c1 SELECT * FROM cnt; del 20 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; DROP TRIGGER tr1; DROP TRIGGER tr2; drop table t2, cnt, tmp; # Delete with a reference to view in subquery # create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); affected rows: 8 select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create table tmp as select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; explain select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; affected rows: 0 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view using reference # to the same view in subquery # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; ####################################### # Test with an index # ####################################### create index t1_c2 on t1 (c2,c1); # # Delete with value from subquery on the same table # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Operation failed create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); affected rows: 4 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + possibly sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Operation failed create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); affected rows: 2 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + non-sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Operation failed create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; affected rows: 8 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 12 4 17 12 5 18 2 1 4 2 2 5 2 4 7 2 5 8 21 2 21 21 4 23 22 2 24 22 4 26 22 5 27 22 6 28 31 2 31 31 4 33 32 2 34 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with order by # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Operation failed create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where; Using filesort 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED a ALL t1_c2 NULL NULL NULL 32 explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED a ALL t1_c2 NULL NULL NULL 32 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with IN predicand over the updated table in WHERE # create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 2 FirstMatch(t1) explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 1 PRIMARY a ref t1_c2 t1_c2 10 test.t1.c2,test.t1.c3 2 FirstMatch(t1) delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); affected rows: 8 select * from t1; c1 c2 c3 1 3 3 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit - can be deleted # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit and an order by # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort 1 PRIMARY a ALL t1_c2 NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort 2 DEPENDENT SUBQUERY a ALL t1_c2 NULL NULL NULL 32 Using where delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete: 2 execution of PS # prepare create_tmp_stmt from "create table tmp as select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; prepare delete_t1_stmt from "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=5; execute create_tmp_stmt using @a; execute delete_t1_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 2 1 4 2 2 5 2 3 6 2 4 7 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 6 38 prepare insert_tmp_stmt from "insert into tmp(c1,c2,c3) select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=2; execute insert_tmp_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 2 1 4 2 3 6 2 4 7 21 3 22 21 4 23 22 3 25 22 4 26 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 drop table tmp; # # Delete in stored procedure # create procedure sp() begin delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; end // create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop procedure sp; drop table tmp; # # Delete in stored function # create function f1(IN a INT) returns int begin delete from t1 where c3 < a order by c3 limit 1; return 1; end;// set @a:=7; create table tmp as select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop function f1; drop table tmp; # # Delete in trigger # create table t2 (c1 integer); insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); CREATE TABLE cnt(del integer); INSERT INTO cnt VALUES(0); CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE cnt SET del=del+1; CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; DELETE FROM t1 WHERE c2>=3; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 SELECT * FROM t2; c1 SELECT * FROM cnt; del 20 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; DROP TRIGGER tr1; DROP TRIGGER tr2; drop table t2, cnt, tmp; # Delete with a reference to view in subquery # create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 2 explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,test.t1.c1 2 delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); affected rows: 8 select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Operation failed create table tmp as select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; explain select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL t1_c2 NULL NULL NULL 32 Using where explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_c2 NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a ALL t1_c2 NULL NULL NULL 32 Using where delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; affected rows: 0 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view using reference # to the same view in subquery # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Operation failed create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const 2 Using where 1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const 2 FirstMatch(t1) 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref t1_c2 t1_c2 10 const,const 2 Using where 3 DEPENDENT SUBQUERY t1 ref t1_c2 t1_c2 10 const,func 2 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; ####################################### # Test with a primary key # ####################################### drop index t1_c2 on t1; alter table t1 add primary key (c3); # # Delete with value from subquery on the same table # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create table tmp as select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); explain select * from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 explain delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 delete from t1 where c1=(select a.c3 from t1 a where a.c3 = t1.c3); affected rows: 4 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + possibly sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create table tmp as select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); explain select * from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2); affected rows: 2 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with EXISTS subquery over the updated table # in WHERE + non-sargable condition # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1) delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3; affected rows: 8 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 12 4 17 12 5 18 2 1 4 2 2 5 2 4 7 2 5 8 21 2 21 21 4 23 22 2 24 22 4 26 22 5 27 22 6 28 31 2 31 31 4 33 32 2 34 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with order by # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create table tmp as select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; explain select * from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED a ALL NULL NULL NULL NULL 32 explain delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED a ALL NULL NULL NULL NULL 32 delete from t1 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with IN predicand over the updated table in WHERE # create table tmp as select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); explain select * from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary explain delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c1 1 Using where; End temporary delete from t1 where c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2); affected rows: 8 select * from t1; c1 c2 c3 1 3 3 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit - can be deleted # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary explain delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 32 Using where; Start temporary 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.a.c2 1 Using where; End temporary delete from t1 where c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete with a limit and an order by # create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; explain select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 32 Using filesort 1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where explain delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; Using filesort 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 Using where delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete: 2 execution of PS # prepare create_tmp_stmt from "create table tmp as select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; prepare delete_t1_stmt from "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=5; execute create_tmp_stmt using @a; execute delete_t1_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 2 1 4 2 2 5 2 3 6 2 4 7 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 6 38 prepare insert_tmp_stmt from "insert into tmp(c1,c2,c3) select * from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)"; set @a:=2; execute insert_tmp_stmt using @a; execute delete_t1_stmt using @a; select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 2 1 4 2 3 6 2 4 7 21 3 22 21 4 23 22 3 25 22 4 26 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 drop table tmp; # # Delete in stored procedure # create procedure sp() begin delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; end // create table tmp as select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; insert into tmp(c1,c2,c3) select * from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3) order by c3 desc limit 1; CALL sp; select * from t1; c1 c2 c3 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop procedure sp; drop table tmp; # # Delete in stored function # create function f1(IN a INT) returns int begin delete from t1 where c3 < a order by c3 limit 1; return 1; end;// set @a:=7; create table tmp as select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 insert into tmp(c1,c2,c3) select * from t1 where c3 < @a order by c3 limit 1; select f1(@a); f1(@a) 1 select * from t1; c1 c2 c3 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop function f1; drop table tmp; # # Delete in trigger # create table t2 (c1 integer); insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8); CREATE TABLE cnt(del integer); INSERT INTO cnt VALUES(0); CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE cnt SET del=del+1; CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2); CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3; DELETE FROM t1 WHERE c2>=3; affected rows: 20 select * from t1; c1 c2 c3 1 1 1 1 2 2 11 1 11 11 2 12 12 1 14 12 2 15 2 1 4 2 2 5 21 2 21 22 2 24 31 2 31 32 2 34 SELECT * FROM t2; c1 SELECT * FROM cnt; del 20 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; DROP TRIGGER tr1; DROP TRIGGER tr2; drop table t2, cnt, tmp; # Delete with a reference to view in subquery # create table tmp as select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); explain select * from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where explain delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where delete from t1 where t1.c2 in ( select max(a.c2) from v1 a where a.c1 = t1.c1); affected rows: 8 select * from t1; c1 c2 c3 1 1 1 1 3 3 11 1 11 11 3 13 12 1 14 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 3 22 21 4 23 22 3 25 22 4 26 22 5 27 22 6 28 31 3 32 31 4 33 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create table tmp as select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; explain select * from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where explain delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 32 Using where delete from v1 where v1.c1 in (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5; affected rows: 0 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 2 5 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; # # Delete from view using reference # to the same view in subquery # analyze table t1 persistent for all; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze note The storage engine for the table doesn't support analyze create table tmp as select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); explain select * from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 explain delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 32 Using where 2 DEPENDENT SUBQUERY a eq_ref PRIMARY PRIMARY 4 test.t1.c3 1 delete from v1 where v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3) and c1 = 2 and exists (select 'X' from v1 a where a.c1 = v1.c1); affected rows: 1 select * from t1; c1 c2 c3 1 1 1 1 2 2 1 3 3 11 1 11 11 2 12 11 3 13 12 1 14 12 2 15 12 3 16 12 4 17 12 5 18 2 1 4 2 3 6 2 4 7 2 5 8 21 2 21 21 3 22 21 4 23 22 2 24 22 3 25 22 4 26 22 5 27 22 6 28 31 2 31 31 3 32 31 4 33 32 2 34 32 3 35 32 4 36 32 5 37 32 6 38 insert into t1(c1,c2,c3) select c1,c2,c3 from tmp; drop table tmp; drop view v1; drop table t1; set @@default_storage_engine=@save_default_engine; # # End of 11.0 tests #