MySQL procedure to drop deleted data in batches.

prune.sql

DELIMITER $$
CREATE PROCEDURE batch_delete(batch INT, sinceDays INT)
BEGIN
    DECLARE remaining INT DEFAULT batch;
    
    CREATE TEMPORARY TABLE del_runs SELECT e.name as e, p.name as p, r.name as r, r.project_id as pid FROM runs as r 
        JOIN projects as p ON p.id = r.project_id 
        JOIN entities as e ON e.id = p.entity_id 
        WHERE r.deleted_at > DATE_ADD(NOW(), INTERVAL -sinceDays DAY)
        ORDER BY r.deleted_at ASC;
    WHILE remaining > 0 DO
        DELETE FROM hs_history WHERE run REGEXP (
            SELECT GROUP_CONCAT(CONCAT(".*:",d.e,":",d.p,":",d.r) SEPARATOR '|') FROM 
                (SELECT * FROM del_runs LIMIT batch) as d);
        DELETE FROM hs_events WHERE run REGEXP (
            SELECT GROUP_CONCAT(CONCAT(".*:",d.e,":",d.p,":",d.r) SEPARATOR '|') FROM 
                (SELECT * FROM del_runs LIMIT batch) as d);
        DELETE FROM hs_logs WHERE run REGEXP (
            SELECT GROUP_CONCAT(CONCAT(".*:",d.e,":",d.p,":",d.r) SEPARATOR '|') FROM 
                (SELECT * FROM del_runs LIMIT batch) as d);
        DELETE FROM del_runs LIMIT batch;
        DELETE FROM runs WHERE deleted_at > DATE_ADD(NOW(), INTERVAL -sinceDays DAY)
            ORDER BY deleted_at ASC LIMIT batch;
        SELECT COUNT(*) FROM del_runs INTO remaining;
    END WHILE;
    DROP TABLE del_runs;
END$$
DELIMITER ;

CALL batch_delete(100, 1);
DROP PROCEDURE batch_delete;