Assistance with optimizing below MySql procedure

MySql - innodb_version 5.7.33

I working on a stored procedure which will be called periodically (lets say once a month) to populate a table with list of string in one column and with static values in other column. The table also has

• ID column (AUTO_INCREMENT) and
• timestamp column (CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

The string is a concatenation of fixed characters plus integers (lets say 10). This integer has to be non repetitive random within the range.

CREATE DEFINER=`db`@`%` PROCEDURE `InsertRandom`(IN NumRows INT, IN MinVal INT, IN MaxVal INT)
BEGIN
    DECLARE i INT;
    DECLARE UniqueId INT(15);
    SET i = 1;
    START TRANSACTION;
    WHILE i <= NumRows DO
        SET UniqueId = concat('ABC', MinVal + CEIL(RAND() * (MaxVal - MinVal)));
        IF  NOT EXISTS (SELECT UNIQUE_ID FROM MY_TABLE WHERE UNIQUE_ID = UniqueId) THEN
            INSERT INTO MY_TABLE (`UNIQUE_ID`, `STATE`, `RANGE_ID`) VALUES (UniqueId, 'new', '100');
        END IF;
        SET i = i + 1;
    END WHILE;
    COMMIT;
END

The range (minVal & maxVal) will be 1 million for every procedure call.

For example,

CALL InsertRandom(1000000, 10000000,11000000);

The table will be purged once in 5 months retaining 1 month of data, so we can assume there will be at about 5 million records at a point when this procedure will executed and the usage of select inside loop is not optimal so please suggest an alternative approach.

Thank you.