Stored procedure getting delayed more

Question

Stored procedure and it is getting delayed. Taking nearly more than half an hour if I call the stored procedure in MySQL command line client. This procedure is to update values in two tables

drop procedure IF EXISTS ALTERTABLEVALUES;
delimiter $$

CREATE PROCEDURE ALTERTABLEVALUES()
BEGIN
DECLARE finished INT DEFAULT 1;
DECLARE total_count INT DEFAULT 1;
DECLARE val varchar(15);
DECLARE i INT DEFAULT 1;
DECLARE table_num INT;
DECLARE table_tab varchar(5);
DECLARE number_cursor CURSOR FOR select outpassNo,tab from tempnumbers;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 0;

drop temporary table if exists tempnumbers;
CREATE TEMPORARY TABLE tempnumbers as
    select  num,'temp' as tab
        from  table1
        where  month(inTime)=2
          and  year(inTime)=2018
        union 
     select  num,'vary' as tab
        from  table2
        where  month(inTime)=2
          and  year(inTime)=2018
        order by  outpassNo;

SET val = (select num from tempnumbers limit 1);
SET total_count = (select count(*) from tempnumbers);

OPEN number_cursor;
get_values: LOOP
FETCH number_cursor INTO table_num,table_tab;
IF finished = 0 THEN 
 LEAVE get_values;
 END IF;
IF table_tab = 'temp' THEN 
    update table1 set table1.num= val where table1.num= table_num;
ELSE
    update table2 set table2.num= val where table2.num = table_num;
END IF;
SET val = val +1;
END LOOP get_values;
CLOSE number_cursor;
DROP TEMPORARY TABLE tempnumbers;
END;
$$
delimiter ;

table1 has the newly inserted data and table2 has the processing values came from table1. A business need is there, where few data from table2 are moved to some other table on a monthly basis, based on some criteria. The expectation is to reorder the numbers column of table1 and table2 as a sequence with the missing values of table2.

Can anyone help on handling resultset without a cursor in stored procedure?

Answer 1

since you are going back to a temporary table and updating rows one by one, you should put an index on the table so the updates go faster. alter table tempnumbers add index(num);

Answer 2

• Don't put numbers into a VARCHAR. (re: val)
• Use parens on the UNION if you want all of it to be ordered: ( SELECT ... ) UNION ( SELECT ... ) ORDER BY ...
• If you don't need the val values to be interleaved, do two "multi-table" UPDATEs, no cursor.
• If the tables don't currently have an AUTO_INCREMENT column, consider ALTER TABLE ... ADD COLUMN val INT AUTO_INCREMENT as a way of numbering the rows.
• Please explain the need for the numbering.