0

Good morning, I have a question, what is the best way to delete records per million?

I have a table that contains more than 70 million records, so I need to delete all records prior to 12/31/2020, I do this table cursor, but it gives timeout error and doesn't end up removing any tuple.

DELIMITER //

CREATE PROCEDURE delOs()

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE dbid INT;

DECLARE osCursor CURSOR FOR
    SELECT os.DBID FROM object_store os INNER JOIN sync.`transaction` t ON ( t.DBID = 
os.`TRANSACTION` )
    INNER JOIN sync.transaction_det td ON ( td.`TRANSACTION` = t.DBID )
    wHERE DATE(td.`DATE`) <= '2020-12-31';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN osCursor;

delete_loop: LOOP

FETCH osCursor INTO dbid;
IF done THEN
    LEAVE delete_loop;
END IF;

IF(dbid IS NOT null) THEN
    DELETE FROM sync.object_store WHERE DBID IN(dbid);
COMMIT;
END IF;

END LOOP;

CLOSE osCursor;
END; //

delimiter ;

is there a better way to do this?

Alder Isaac Solis De Leon
  • 1
  • 1
  • Don't use a cursor. Just change the `SELECT` query to a `DELETE` query. See the linked question for details. – Barmar Oct 26 '21 at 21:59
  • Best is to create a new table with the data you will keep drop the old table and rename – nbk Oct 26 '21 at 22:01

0 Answers0