Delete from table in chunks

Question

I want to cleanup one of my tables which has 4 million. So I prepare this query to clean it. But it's throwing an error, can anyone please help me to fix this?

DELIMITER $$
DROP PROCEDURE IF EXISTS archive_table $$
create procedure archive_table () 
set @min=(select min(dob) from test
              where dob < DATE_SUB(CURDATE(), INTERVAL 30 day));
while @min is not null
begin   
    delete from test where dob = @min;
    commit ;
    select min(dob) from test
            where dob > @min
              and dob < DATE_SUB(CURDATE(), INTERVAL 30 day) );
END WHILE;
END$$

ERROR:

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
 near 'while @min is not null
begin
    delete from test where dob = @min;
    commi' at line 1

score 5 · Answer 1 · answered Mar 04 '20 at 06:08

This might do what you want. It's a way to delete old rows from a very large table, without placing as much load on the database as much as a regular DELETE

DROP PROCEDURE IF EXISTS archive_table;

DELIMITER $$

CREATE PROCEDURE archive_table()

BEGIN   
    REPEAT
        DO SLEEP(1); ## Optional, to minimise contention
        DELETE FROM test 
        WHERE dob < NOW() - INTERVAL 1 MONTH 
        ORDER BY primary_key 
        LIMIT 1000; ## 10000 also works, this is more conservative      
    UNTIL ROW_COUNT() = 0 END REPEAT;
END$$

DELIMITER ;

CALL archive_table();

score 2 · Answer 2 · answered Oct 28 '17 at 16:12

The openark kit by Shlomi Noach could be right up your alley!

From the introduction here:

oak-chunk-update: perform long, non-blocking UPDATE/DELETE operation in auto managed small chunks.

From the documentation page here:

This utility allows for splitting long running or non-indexed UPDATE/DELETE oprations, optionally multi-table ones. Long running updating queries are often used. Some examples:
Purging old table records (e.g. purging old logs).
Updating a column on a table scale.
Deleting or updating a small number of rows, but with a non-indexed search condition.
Inserting into one table aggregated values from other tables.

The tool has a load of options with examples - I was very impressed when I used a couple of his tools for various bits and pieces.

So, kick it off with cron at 03:00 in the morning and relax!

score 2 · Accepted Answer · answered Oct 29 '17 at 20:58

The procedure is missing its BEGIN.

For deleting in chunks, see http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

With 4 million rows, you have perhaps 133K rows per delete. This will choke InnoDB, even if you have an index starting with dob. See the link, above, for how to trim the deletes back to no more than 1000 at a time. (1K is a more civilized chunk.)

score 1 · Answer 4 · answered Oct 30 '22 at 20:11

1

The command line tool pt-archiver found in the Percona Toolkit can move or just delete rows in automatically managed chunks given a where clause.

answered Oct 30 '22 at 20:11

ColinM

373
2
8

score 0 · Answer 5 · answered Oct 27 '17 at 17:16

I'm confused at why you're looping. It looks like you want to delete a batch and return the results. What you want is something like this.

CREATE TEMPORARY TABLE foo
AS
  SELECT *
  FROM test
  WHERE dob < DATE_SUB(CURDATE(), INTERVAL 30 day);

DELETE
FROM test
INNER JOIN foo
  ON (foo.dob = test.dob);

SELECT *
FROM foo;

Or use a real database that supports DELETE .. RETURNING

If you don't care about the results just do

DELETE
FROM test
WHERE dob < DATE_SUB(CURDATE(), INTERVAL 30 day);

Delete from table in chunks

5 Answers5