Re: How to delete millions of record in a loop

[pandu hondu]

So I have good and bad news !
Good news is I am not getting any more error messages !

Bad news is the script is not doing what it is supposed to do.

sql script:

use mydb;
set autocommit=0;
drop procedure delete_table_incrementally;
delimiter //
create procedure delete_table_incrementally()
modifies sql data
begin
repeat
DELETE FROM mytable where my_condition='ERROR-5000' order by id limit 3;
commit;
select count(*) FROM mytable where my_condition='ERROR-5000';
until row_count()=0
end repeat;
end;
//
delimiter ;


Query output:

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)


I personally don't think, its working. As It should produce output as decreasing number of rows affected but that is not the case so what is going wrong ?