Stored Procedure To Delete Rows From Extremely Large Tables

Edit to spec as necessary:

DELIMITER $$
CREATE PROCEDURE trimTable()
BEGIN
 DECLARE ROWS INT;
 DECLARE rows_deleted INT;
 SET ROWS = 1;
 SET rows_deleted = 10000;
 WHILE ROWS > 0
 DO
    DELETE FROM db.tabA WHERE predicate_col < CURDATE() - INTERVAL 90 DAY ORDER BY `id` LIMIT 10000;
    SET ROWS = ROW_COUNT();
    SET rows_deleted = rows_deleted + ROW_COUNT();
    SELECT rows_deleted AS "Rows Deleted";
 END WHILE;
END $$
DELIMITER ;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: