phpmyadmin Question posted in
The official documentation can be found here.

MySQL Stored Procedure execution takes a long time – Phpmyadmin

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
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search