### ### Create 2 tables with index on 1st column and insert (1,1),(2,2),... (2M, 2M) . ### drop table if exists td1big, td2big; create table td1big ( a int not null, b int not null) ; insert into td1big values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8); set @d = 8; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; set @d = @d*2; insert into td1big select a+@d,b+@d from td1big; alter table td1big add index i1(a); delete from td1big where a > 2*1000*1000; create table td2big like td1big; insert into td2big select * from td1big; select count(*) from td1big; select count(*) from td2big; ### ### Start test ### ## Try multi-delete for one row ## (this will properly delete one row from td1big and one row from td2big) select 'td2big rows before small delete', count(*) from td2big; delete td1big,td2big from td1big,td2big where td1big.b=td2big.a and td1big.a < 2; select 'td2big rows after small delete', count(*) from td2big; select 'td1big rows after small delete', count(*) from td1big; ## Try deleting many rows ## (this will delete all necessary rows from 1st table but not from the second) delete td1big,td2big from td1big,td2big where td1big.b=td2big.a and td1big.a < 100*1000; select 'td2big rows after big delete', count(*) from td2big; select 'td1big rows after big delete', count(*) from td1big;