Best practice to delete big portion of big mysql table I have a log table in mysql that inserts 1 000 000 rows per day for the past 6 months. (Approximately 180 000 000 rows)

This table requires 54GB of space whereas rest of tables within the same database only require 2GB.

I plan to delete this log table manually using:

DELETE FROM log_table ORDER BY id asc LIMIT 1000000


It is intended to keep approximately 10 000 000 most recent rows.

Should I use some specific procedure such as table optimization, stopping temporarily traffic to the database, use higher / lower limit, make a copy of database to make sure data is not fragmented on the HDD and so on?

Thank you for your help.
While there is more than one way to do that (including the one you mentioned), if you can temporarily stop the traffic to the DB, I suggest this way:


Stop the traffic (At least to that table if possible)
Create a new table with the same fields, but no indexes: CREATE TABLE new_table AS SELECT * FROM myTable WHERE 1=2;
Insert into this new table all the rows that you want to keep: INSERT INTO new_table SELECT * FROM MyTable WHERE id&gt;123456; -- OR, INSERT INTO new_table SELECT * FROM myTable ORDER BY id DESC limit 10000000;
Create indexes on this new table.
Switch the tables by renaming them: RENAME TABLE myTable TO old_table, new_table to myTable;
Drop the old table
Restart the traffic


If the ID of this table is an auto increment one, and you don't care about its value, you will have the chance to reset it to start from 1, so you don't run out of IDs in the near future (This is totally dependent on other factors)

HTH