Truncate all but the most recent XX rows from a MySQL table

delete_most.sql

/*
    Need to delete most, but not all, of the records in a 
    large table? Deleting the normal way can be VERY slow,
    but this will keep the most recent million (or however
    many) rows of your large table
*/

/* --- CONFIGURE ------------------------------------------- */
SET @tbl  := 'TABLE_NAME_HERE';	-- table name
SET @keep := 1000000;	-- how many of the newest rows to keep
/* --------------------------------------------------------- */

/* 1. drop any old backup */
SET @sql := CONCAT('DROP TABLE IF EXISTS ', @tbl, '_backup');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* 2. clone structure into a new table */
SET @sql := CONCAT('CREATE TABLE ', @tbl, '_backup LIKE ', @tbl);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* 3. copy the last @keep rows  */
SET @sql := CONCAT(
  'INSERT INTO ', @tbl, '_backup ',
  'SELECT * FROM ', @tbl,
  ' WHERE id > ((SELECT MAX(id) FROM ', @tbl, ') - ', @keep, ')'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* 4. swap tables to use the new, smaller one */
SET @sql := CONCAT(
  'RENAME TABLE ', @tbl, ' TO ', @tbl, '_todelete, ',
                 @tbl, '_backup TO ', @tbl
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

/* 5. drop old, larger table that we no longer need */
SET @sql := CONCAT('DROP TABLE ', @tbl, '_todelete');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;