To delete a big/huge table in chunks, you could create this store procedure, but the performance at the end depends of how good/normalized is your database (indexes):

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. Such procedures are stored in the database data dictionary.

Connect to the database and select the database:

mysql> \u my_database
Database changed

To list stored procedures


Create the delete procedure:

mysql> DROP PROCEDURE IF EXISTS archive_table;

This will delete any existing procedures named archive_table

Change the delimiter:

mysql> DELIMITER $$

When you write SQL statements, you use the semicolon (;) to separate two statements. A stored procedure, however, consists of multiple statements separated by a semicolon (;).If you use a MySQL client program to define a stored procedure that contains semicolon characters, the MySQL client program will not treat the whole stored procedure as a single statement, but many statements. Therefore, you must redefine the delimiter temporarily so that you can pass the whole stored procedure to the server as a single statement.

Create the procedure:

CREATE PROCEDURE archive_table()
        DELETE FROM table_name
        WHERE time_stamp < unix_timestamp(NOW() - INTERVAL 2 MONTH)
        ORDER BY time_stamp -- time_stamp needs an INDEX
        LIMIT 1000; -- can be tweaked
        SELECT SLEEP(1);

For MyISAM you could try DELETE LOW_PRIORITY QUICK FROM table_name

LIMIT leads to a warning on replication about it being non-deterministic, see more here:

Set back the delimiter to ;:

mysql> DELIMITER ;

And that’s it, to call the store procedure and start deleting records use:

mysql> CALL archive_table();