Deleting Billion records in a range vs exact ID lookup MYSQL I have a database table which is around 700GBs with 1 Billion rows, the data is approx 500 GB and index is 200GB,
I am trying to delete all the data that's before 2021,
Roughly around 298,970,576 rows in 2021 and remaining before this are 708,337,583 rows.
To delete this I am running a non-stop query in my python shell
DELETE from table_name where id &lt; 1762163840 limit 1000000;
id -&gt; 1762163840 represent data from 2021. Deleting 1Mil row taking almost 1200-1800sec.
Is there any way I can speed up this because the current way is running for more than 15 days and there is not much data delete so far and it's going to do more days.
I thought that if I make a table with just ids of all the records that I want to delete and then do an exact map like
DELETE from table_name where id in (select id from _tmp_table_name);
will that be fast? and will it going to be faster than first making a new table with all the records and then deleting it?
The database is setup on RDS and instance class is db.r3.large 2 vCPU and 15.25 GB RAM, only 4-5 connections running
I would use much smaller batches, and delete in specific ranges:
DELETE from table_name where id BETWEEN ? AND ?

Start with the first number equal to the minimum id of the table, and the second number 1000 higher than that. Doing batches of 1000000 is too large, it'll take too long and make the rollback segment grow too large.
Then as you loop, increase the start and end of the range by 1000, terminating when it gets to 1762163840.
It will take a long time to do this no matter what.
One alternative strategy some people use instead of deleting a great majority of data is to make a new table, copy only the small number of rows you want to keep, and then swap the tables. That will take less time, but it's complicated to manage concurrent updates. It's easier if your table is insert-only.