So here is what I tried so far:
DELETE FROM Log WHERE LogTime < DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY ID LIMIT 1000
I am trying to delete 1000 rows from the table Log at a time so that it can be done in a controlled manner and so that it will not take up all the resources of the mysql server.
Now to the problem. When there are thousands of rows to delete it works perfectly. It seem like since I order by ID which is an auto_increment primary key it will start from the beginning and take as much as needed. Also LogTime is increasing so first row has the oldest timestamp.
Problem is now if I only have 999 rows that should be deleted according to the LogTime < DATE_SUB(NOW(), INTERVAL 30 DAY) check. Then it will search the entire table for the last 1 row.
Is there any way to tell mysql to only delete until it does not find any more matches, in the order it is searching?
Put an index on your LogTime column. Then remove ORDER BY ID from your DELETE query. Then deleting your batches of 1000 (or fewer) rows should be efficient.
If you must guarantee that your deletion batches go in strict ID order you still need the index I suggested. Your query will be fine.
But if your scheme for deleting batches doesn’t require maintaining ID order that’s a better choice, especially if the table is active. If you rerun the DELETE query until it deletes 0 rows you need not worry about strict ID order.
Smaller batches (50 or 100) will tie up your table for shorter times, reducing delays for other users of the table. And using a single indexed column in your WHERE clause reduces the chance of a deadlock.