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.