// you’re reading...

MySQL

Row Deletion MySQL Stored Procedure

Scenario: You’ve got a huge history table that’s getting too big to deal with. It’s grown out of your control and the application has an SLA of 100% uptime. What now?

My solution: The answer is to archive rows off the system, then delete rows by primary key.

DROP PROCEDURE IF EXISTS proc_delete_id_range_safe; 
DELIMITER $$
CREATE PROCEDURE proc_delete_id_range_safe (IN i_row_start INT UNSIGNED, IN i_row_end INT UNSIGNED, IN i_tablename VARCHAR(64))
BEGIN
DECLARE itr INT UNSIGNED;
DECLARE delsql VARCHAR(120);
SET @itr = i_row_start;
SET @del_sql = CONCAT('DELETE FROM ', i_tablename, ' WHERE id = ?');
PREPARE del_stmt FROM @del_sql;
WHILE @itr < i_row_end  DO
EXECUTE del_stmt USING @itr;
COMMIT;
SET @itr = @itr + 1;
END WHILE ;
SELECT @itr AS Iterations;
DEALLOCATE PREPARE del_stmt;
END $$
DELIMITER ;
CALL proc_delete_id_range_safe (0, 123450001, 'big_hug_table_full_of_history');

Discussion

No comments for “Row Deletion MySQL Stored Procedure”

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/

%d bloggers like this: