Option 1:
Delete huge rows by single delete command will not lock the table in MySQL Innodb engine but it will chew up a lot of resources, leading to slowness, therefore better to delete huge number of rows by Chunk.
In the following example the procedure will delete record older than current days by chunk (Based on Column ReceivedAt in Table). If you have to delete data from many tables based on same condition, then you can just pass the table name during procedure call.
delimiter //
create procedure daily_delete(IN tab_name VARCHAR(40))
begin
declare var_rows int;
declare rows_deleted int;
set var_rows = 1;
set rows_deleted = 0;
while var_rows > 0
do
SET @d= CONCAT('delete from ', tab_name,' where ReceivedAt < date_add(current_date, interval -0 hour) limit 10000;');
PREPARE stm FROM @d;
EXECUTE stm;
set var_rows = row_count();
set rows_deleted = rows_deleted + row_count();
end while;
select rows_deleted as "Rows Deleted";
end //
delimiter ;
Example of Calling the procedure for Table “SystemEvent”:
call daily_delete('SystemEvents');
Option 2:
This option is faster if application can have downtime. In the following example it will delete records older than current days (Based on Column ReceivedAt in Table).
drop table if exists SystemEvents_Copy;
create table SystemEvents_Copy like SystemEvents;
insert into SystemEvents_Copy select * from SystemEvents where ReceivedAt > date_add(current_date, interval -0 hour);
drop table SystemEvents;
rename table SystemEvents_Copy to SystemEvents;