Mysql - what is the faster way to delete old records

Question

I have a table with about 35 million rows. each has about 35 integer values and one time value (last updated) The table has two indexes primary - uses two integer values from the table columns Secondary - uses the 1st integer from the primary + another integer value.

I would like to delete old records (about 20 millions of them) according to the date field.

What is the fastest way: 1. Delete as is according the the date field? 2. Create another index by date and then delete by date.

There will be one time deletion of large portion of the data and then incremental weekly deletion of much smaller parts.

Is there another way to do it more efficiently?

Answer 1

it might be quicker to create a new table containing the rows you want to keep, drop the old table and then rename the new table

Answer 2

For weekly deletions an index on date field would speed things up.