Skip to content

Instantly share code, notes, and snippets.

@urjitbhatia
Created April 20, 2021 00:56
Show Gist options
  • Save urjitbhatia/402017405e60f63c11d725d905ed0a64 to your computer and use it in GitHub Desktop.
Save urjitbhatia/402017405e60f63c11d725d905ed0a64 to your computer and use it in GitHub Desktop.
Aurora mysql delete in chunk
--- Simple table to keep track of delete chunks
create table _delete_log
(
log varchar(200) null,
createdAt timestamp null
);
------------ Stored proc definition ---------------
DROP PROCEDURE IF EXISTS delete_chunks;
DELIMITER $$
CREATE PROCEDURE delete_chunks()
BEGIN
REPEAT
DO SLEEP(1); ## Optional, to minimise contention
DELETE
FROM <TABLE_NAME>
WHERE <WHERE CLAUSE>
ORDER BY <GOOD TO ORDER THE DELETES>
LIMIT 1000; ## Per chunk limit
insert into _delete_log SELECT CONCAT(ROW_COUNT(), ' rows deleted'), now(); ## Optional - easy way to track how many deleted so far
SHOW ERRORS;
UNTIL ROW_COUNT() = 0 END REPEAT;
END$$
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment