we have a project with a huge MySQL table (MySQL version 5.7.35-0 on Ubuntu 18.04.1 using InnoDB) table. By Huge I mean approximately 100M of rows 300GB.
I want to do some pruning there. This can be achieved by this SQL query:
DELETE FROM Commit WHERE current=0;
But this will lock up the database and I need to terminate the process by force. I have an index on column current. I can split up the query into chunks:
DELETE FROM Commit WHERE current=0 LIMIT 100;
If I am deleting 1 row it is done immediately if 100 it takes few milliseconds if 1000 it takes a second, if 10000 approximately a minute, and unproportionally more and more (not linearly).
Is there some smart way how to iterate this query again and again until there is no row with this condition left? And without locking up the server?
Or can I somehow modify the strategy of deleting to do it in one query?