0

I want to insert 10 million rows to MySQL table. But it completed in 10 h 12 m 13 s 29 ms. How to speed it up?

Here is my SQL

USE foo;
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  school_id  CHAR(4) NOT NULL,
  student_id CHAR(6) NOT NULL,
  INDEX school_id(school_id),
  INDEX student_id(student_id),
  INDEX school_id_and_student_id(school_id, student_id)
);
DROP PROCEDURE IF EXISTS tmpproc;
CREATE PROCEDURE tmpproc() BEGIN
  DECLARE i INT UNSIGNED DEFAULT 0;
  WHILE i < 10000000 DO
    INSERT INTO tmp (school_id, student_id)
    VALUES (SUBSTR(MD5(RAND()) FROM 1 FOR 4), SUBSTR(MD5(RAND()) FROM 1 FOR 6));
    SET i = i + 1;
  END WHILE;
END;
CALL tmpproc();
BaiJiFeiLong
  • 3,716
  • 1
  • 30
  • 28
  • I don't know MySQL very well, but I'd see if smaller transactions help. Commit once in a while, for each 10000 rows perhaps? – jarlh Nov 23 '18 at 08:13
  • For my experience, insert multiple rows within one statement is very fast, and MySQL's dump are also in this way. But I don't know why. – Geno Chen Nov 23 '18 at 08:20
  • Also you can look at https://stackoverflow.com/questions/6889065. – Geno Chen Nov 23 '18 at 08:21
  • Possible duplicate of https://stackoverflow.com/questions/1793169. – Geno Chen Nov 23 '18 at 08:25
  • Remove all the indexes at first. Once you have completed the inserts then create index. Also you could just create one INDEX on school_id and student_id Having index while inserting introduces overhead. – AmeyaN99 Nov 23 '18 at 10:11

0 Answers0