I have to delete old records from a very large table (1.6billion rows) in a stored procedure.
CREATE TABLE mytable( id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, unix_time INT(11) NOT NULL DEFAULT 0, value DOUBLE (20, 4) NOT NULL DEFAULT 0.0000, UNIQUE INDEX history_1 USING BTREE (id, unix_time) ) ENGINE = INNODB; So I can get the unix time string I use a variable: DECLARE UnixTime BIGINT(20); SET UnixTime = UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 30 DAY)); So now I can do my delete query. For now I made a temp table: CREATE TEMPORARY TABLE historyDropper( id BIGINT(20) NOT NULL, UNIQUE INDEX index1 USING BTREE (id) ); And load it with all the unique id I want to delete, then join that to my huge table: SET @sql = CONCAT('DELETE h.* FROM mytable h inner join historyDropper hd on h.id = hd.id WHERE unix_time < ', UnixTime, ' '); PREPARE s1 FROM @sql; EXECUTE s1; DEALLOCATE PREPARE s1; My question is, is this the most efficient way to delete data older than a certain unix_timestamp out of s huge table? Would it be better to loop thru each unique id and delete all the items older? Is there a utility or command line or mysql dump and restore method that is faster? Thanks for the tips, Bryancan