locked
Stored Procedure INSERT INTO SELECT in MySQL 5.5.62-log version RRS feed

  • 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;
    Thursday, April 23, 2020 2:39 PM

Answers

  • User303363814 posted

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 24, 2020 12:45 AM

All replies

  • User303363814 posted

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 24, 2020 12:45 AM
  • User1140724383 posted

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

    Saturday, April 25, 2020 6:31 AM