Mysql Stored Procedure Acceleration

First, let's look at a stored procedure, which is used to construct data. Of course, the stored procedure simplifies some unnecessary table structures.

CREATE PROCEDURE modifyRootEntry()BEGIN  DECLARE done INT DEFAULT FALSE;  DECLARE userId INT;    DECLARE userIdIter CURSOR FOR SELECT DISTINCT user_id from entries;   OPEN userIdIter;  read_loop: LOOP    FETCH userIdIter INTO userId;       IF done THEN      LEAVE read_loop;    END IF;INSERT INTO entries (id, name, user_id, parent_id) VALUES(0, 'root_parent', userId, 0);UPDATE entries SET parent_id=0 where user_id=userId AND name='file_root' AND parent_id is NULL;  END LOOP;  CLOSE userIdIter;END;

CREATE PROCEDURE modifyRootEntry()BEGIN  DECLARE done INT DEFAULT FALSE;  DECLARE userId INT;    DECLARE userIdIter CURSOR FOR SELECT DISTINCT user_id from entries;   START TRANSACTION; // here !  OPEN userIdIter;     read_loop: LOOP    FETCH userIdIter INTO userId;       IF done THEN      LEAVE read_loop;    END IF;INSERT INTO entries (id, name, user_id, parent_id) VALUES(0, 'root_parent', userId, 0);UPDATE entries SET parent_id=0 where user_id=userId AND name='file_root' AND parent_id is NULL;  END LOOP;  CLOSE userIdIter;  COMMIT;// here !END;

UPDATE entries SET parent_id=0 where user_id=userId AND name='file_root' AND parent_id is NULL;

CREATE PROCEDURE modifyRootEntry()BEGIN  DECLARE done INT DEFAULT FALSE;  DECLARE userId INT;    DECLARE userIdIter CURSOR FOR SELECT DISTINCT user_id from entries;   START TRANSACTION; // here !  OPEN userIdIter;     read_loop: LOOP    FETCH userIdIter INTO userId;       IF done THEN      LEAVE read_loop;    END IF;INSERT INTO entries (id, name, user_id, parent_id) VALUES(0, 'root_parent', userId, 0);  END LOOP;  UPDATE entries SET parent_id=0 where user_id=userId AND name='file_root' AND parent_id is NULL; // here!  CLOSE userIdIter;  COMMIT;// here !END;

 CREATE PROCEDURE pFastCreateNums (cnt INT UNSIGNED)BEGINDECLARE s INT UNSIGNED DEFAULT 1;TRUNCATE TABLE Nums;INSERT INTO Nums SELECT s;WHILE s*2 <= cnt DOBEGININSERT INTO Nums SELECT a+s FROM Nums;SET s = s*2;END;END WHILE;END;