MYSQL delete from large table increase the delete time for each transaction-CodePudding

I am trying to delete data from table which contains almost 6,000,000,000 records , with where clause.

here is the stored procedure I am using and running from command prompt MySQL in windows.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `clean_table`( )
BEGIN   
    REPEAT
        DO SLEEP(1);
        DELETE FROM tablename
        WHERE exportInfoId<=8479  limit 100000;   
        SELECT ROW_COUNT();
    UNTIL ROW_COUNT() = 0 END REPEAT;
END$$
DELIMITER ;

Its deleting the data but it continue incasing the time for each delete transaction. Why its keep increasing , even when with delete the earlier transaction reduce the data in table ? Is there any way to make it same time for each transaction ? I am already using sleep as suggested in some other answers .

CodePudding user response：

You need to add an index to the column with which you are using to find the record(s) to be deleted.

With an index, MySQL knows exactly where the records are to be found so it can go straight to the record(s).

Without an index, then the table must be searched row by row.

The difference is, without an index the deletes are performed in the order of the primary key however, with an index the records will be searched in the order of that particular column.

To add an index, do what @o-jones points out below in the comments, which is:

// Normal Index
ALTER TABLE ADD INDEX exportInfoId (exportInfoId);

// Reverse Index
ALTER TABLE ADD INDEX exportInfoId (exportInfoId DESC);

Adding an index to the column is the correct answer here, there really is no other answer.