I had a use case of deleting 1M+ rows in the 25M+ rows Table in the MySQL.
Tried different approaches like batch deletes (described above).
I've found out that the fasterfastest way (copy of required records to new table):
- Create Temporary Table that holds just ids.
CREATE TABLE id_temp_table ( temp_id int);
- Insert ids that should be removed:
insert into id_temp_table (temp_id) select.....
Create New table table_new
Insert all records from table to table_new without unnecessary rows that are in id_temp_table
insert into table_new .... where table_id NOT IN (select distinct(temp_id) from id_temp_table);
- Rename tables
The whole process took ~1hr. In my use case simple delete of batch on 100 records took 10 mins.