How to improve performance merging multiple table via cursor

Question

I have the following stored procedure to merged multiple tables into 1 table. The records that im tryin to merge is about 150M. The procedure is quite slow. Here is the query

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE Category CHAR(200);
DECLARE cur1 CURSOR FOR SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='technology2' AND TABLE_NAME <>'technologyRepo';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur1;
  read_loop: LOOP
  FETCH cur1 INTO Category;
     IF done THEN
       LEAVE read_loop;
     END IF;

  SET @table_name:=Category;
  SET @QRY=CONCAT('INSERT INTO 
  technologyRepo (
                  documentId,
                  url,
                  version,
                  first_detected,
                  last_detected,
                  technology_name)
                  SELECT
                  id,
                  url,
                  version,
                  first_detected,
                  last_detected,', QUOTE(Category));
SET @QRYFRM=concat(@QRY, ' FROM technology2.');
SET @sql:=CONCAT(@QRYFRM,@table_name);
PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
END LOOP;
CLOSE cur1;
END

Here is the total number of tables that i need to merge:

+------------+
| TABLE_NAME |
+------------+
|       1178 |
+------------+
1 row in set (0.16 sec)

mysql> SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA                                                                                                                                                           
= 'technology2' AND TABLE_NAME <>'technologyRepo';
+-----------------+
| SUM(TABLE_ROWS) |
+-----------------+
|       150116963 |
+-----------------+
1 row in set (0.02 sec)

Any performance optimization for this? I'm not that familiar with cursor.

Answer 1

Don't use a cursor, use a JOIN to do the entire set of statements all at once. Here are two examples:

http://mysql.rjweb.org/doc.php/myisam2innodb#generating_alters http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

Both of them depend on doing lots of statements, then you manually copy the commands into MySQL commandline tool (or some other tool). They could be adapted to do prepare/execute.

See also the following for construction a single query to pivot a table:

http://mysql.rjweb.org/doc.php/pivot