New answers tagged delete
create table notification_temp as select * from notification where CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day) ; drop table notification; RENAME notification_temp TO NOTIFICATION;
Make a temp table, switch it in and out, and copy the last 30 days data into it. # # Make empty temp table # CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION; # # Switch in new empty temp table # RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION; # # Retrieve last 30 days data # INSERT INTO NOTIFICATION SELECT * FROM ...
My favorite is pt-archiver from Percona Toolkit. It takes care of MySQL load, replication lag.
You could reindex the table and even shrink the table. However, if you want to delay such disk-based maintenance, you should, at the very least, recompute the index statistics. Without recomputing the index statistics, the MySQL Query Optimizer may make bad choices for query EXPLAIN plans. This could adversely affect SELECTs if the statistics for nonexist ...
Top 50 recent answers are included