Stored Procedure INSERT INTO SELECT in MySQL 5.5.62-log version

Question

User1140724383 posted

Hi guys,

I have to register in tbl2 all the rows from tbl1 with the last access date for the same nickname.

My database is MySQL 5.5.62-log version.

I tried this Stored Procedure.

No error, but the inserting on tbl2 is very long.

Any suggestion for optimize this SP ?

BEGIN

DECLARE var INT;

SET var = 0;

WHILE var < 1 DO
    INSERT IGNORE INTO `tbl2` (
                `NickName`,
                `Continent`,
                `Region`,
                `AccessLastDate`
    ) SELECT
        `NickName`,
        `Continent`,
        `Region`,
        `AccessLastDate`
    FROM
        `tbl1` t1
    WHERE
        Continent IN ('Asia','Oceania','Africa')
        AND 
             t1.`AccessLastDate` = (
        SELECT
            MAX(t2.`AccessLastDate`)
        FROM
            `tbl1` t2
        WHERE
            t2.`NickName` = t1.`NickName`
    )
ORDER BY
    t1.`AccessLastDate`;

SET var = var + 1;

END
WHILE;

Answer 1

User303363814 posted

An Index on AccessLastDate may help.  Note that adding an index increases insert time and uses more disk space.

Answer 2

User1140724383 posted

better situation, but not excellent
but it is already a step forward
thanks