mysql-archive-single-tbl.sql

use sqladmin;

DROP PROCEDURE
IF EXISTS archive;
delimiter //
  CREATE PROCEDURE
    archive()
  begin
    DECLARE rows INT;
    DECLARE rows_deleted INT;
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET rows = 1;
    SET rows_deleted = 10000;
    WHILE rows > 0
    do
    SET autocommit=1;
    DELETE
    FROM   test
    WHERE  dop < DATE(Date_sub(Now(), INTERVAL 10 day))
    LIMIT  10000;
    SET rows = row_count();
    select sleep(1); 
    commit;
    END WHILE;
    END //
delimiter ;