Sign in
Log inSign up

Optimization in mysql

Ranjeet Kasture's photo
Ranjeet Kasture
·Jan 9, 2018

Hi All,

I have table from which have to pick up data perform some condition checks and also retrieve ids from respective tables. This is will performed on each record say millions records and then insert into new table.

My question is how can I achieve above in MySQL with optimized way/solution?

I am trying Stored Procedure but seems like its taking time of 666 seconds to insertion of 10k records.

Also my application uses PHP codeigniter framework.

So need solution for this.

For example I am sharing a simple example stored procedure 

USE `press_test_sp`;
DROP procedure IF EXISTS `package_column_master`;

DELIMITER $$
USE `press_test_sp`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `package_column_master`()
BEGIN
    DECLARE n INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
   /* DECLARE cpp varchar(255);*/

    SELECT COUNT(*) FROM pubgrp1 INTO n;

    SET i=0;

    WHILE i<n DO
        INSERT INTO test_cp(cpp) SELECT CPP from pubgrp1 WHERE id = i;
        SET i = i+1;
    END WHILE;     
END$$

DELIMITER ;
2