MySQL Stored Procedure execution takes a long time

Question

I am trying to insert rows into a db table through a stored procedure. My table contains more than 500000 rows so when I execute the procedure, it takes more than 4h executing. Knowing that I am using Xampp server, MySQL as database management system and HeidiSQL to manipulate the db. Below you see the procedure script. Is there any issue with the code or should I add other things. Thanks for your help in advance.

BEGIN
DECLARE finished  INTEGER DEFAULT 0;
DECLARE post_id BIGINT;    
DECLARE v_id  BIGINT;
DECLARE done INT DEFAULT 0;
DECLARE msg varchar(10000) DEFAULT "";
DEClARE post_cursor CURSOR FOR
SELECT distinct po.post_id FROM wp_postmeta po;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SET finished = 2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Open post_cursor;
get_posts: LOOP
FETCH post_cursor INTO post_id;
select CONCAT('postid', post_id) ;
IF done = 1 THEN
LEAVE get_posts;
END IF;
SET v_id = (select MAX (meta_id) from wp_postmeta) +1 ;
INSERT INTO   wp_postmeta (meta_id, post_id, meta_key, meta_value) VALUES (v_id, post_id ,"_company_name", "blabla");
SET done = 0;
END LOOP;

CLOSE post_cursor;
END

this looks so wrong, wp_postmeta should have an autoincrmenet, not that Max +1 as you have to run ot all the time, anwhat fopr the loop. that makes even less sense when you don have to get the data from somewhere else. so where come "_company_name", "blabla" from. — nbk, Jul 08 '21 at 15:23
thanks for your reply. Actually I am trying to add the same value ("_company_name", "blabla" ) which is static for every post_id I have in the table wp_postmeta. — mypeeka, Jul 08 '21 at 15:49

score 0 · Answer 1 · answered Jul 08 '21 at 15:53

I would do it this way:

INSERT INTO wp_postmeta (post_id, meta_key, meta_value) 
 SELECT id, '_company_name', 'blabla'
 FROM wp_posts;

No cursor needed, just rely on INSERT...SELECT.

No DISTINCT needed, since you know there's exactly one row for each post in the wp_posts table.

No max(meta_id)+1 needed, just rely on the auto-increment to do its job.

score 0 · Accepted Answer · answered Jul 08 '21 at 16:04

wp_postmeta has an auto_incrememnt on meta_id, so use

The main point is the GROUP BY that garantees that only 1 post _id is inserted

CREATE TABLE wp_postmeta (meta_id int AUTO_INCREMENT PRIMARY KEY, post_id int, meta_key VARCHAR(20), meta_value VARCHAR(20))

INSERt into wp_postmeta ( post_id, meta_key, meta_value)  VALUEs (1,'teata','testb'),(1,'teata','testb'),(2,'teata','testb'),(2,'teata','testb')

INSERT INTO   wp_postmeta ( post_id, meta_key, meta_value) SELECT    po.post_id ,"_company_name", "blabla" FROM wp_postmeta po GROUP BY  po.post_id

SELECT * FROM wp_postmeta

meta_id | post_id | meta_key      | meta_value
------: | ------: | :------------ | :---------
      1 |       1 | teata         | testb     
      2 |       1 | teata         | testb     
      3 |       2 | teata         | testb     
      4 |       2 | teata         | testb     
      5 |       1 | _company_name | blabla    
      6 |       2 | _company_name | blabla

db<>fiddle here

MySQL Stored Procedure execution takes a long time

2 Answers2