Re: How to delete millions of record in a loop

[pandu hondu]

So dear folks, I am giving back what I got back from you !

Please find below working code to delete millions of rows. Its not yet iterative and I am looking at iterative method but that is part 2.

Part 1 is it does the job, you can extend the limit to say 50K in one hit.

use mydb;
set autocommit=0;
drop procedure delete_table_incrementally;
delimiter //
create procedure delete_table_incrementally()
modifies sql data
begin
SET optimizer_trace="enabled=on";
select count(*) FROM mytable where my_condition='ERROR-5000';
DELETE FROM dq_issues where my_condition='ERROR-5000'; order by id limit 3;
commit;
select count(*) FROM mytable where my_condition='ERROR-5000';;
end;
//
delimiter ;
call delete_table_incrementally();