I need to insert 10M+ records into a MySQL memory table.

A naive approach was to pipe records using shell, e.g.

$ tail -10 ~/Desktop/test.sql
INSERT INTO `test` (`a`, `b`) VALUES (4, 5);
INSERT INTO `test` (`a`, `b`) VALUES (5, 6);
INSERT INTO `test` (`a`, `b`) VALUES (6, 7);
INSERT INTO `test` (`a`, `b`) VALUES (6, 7);
INSERT INTO `test` (`a`, `b`) VALUES (1, 2);
INSERT INTO `test` (`a`, `b`) VALUES (2, 3);
INSERT INTO `test` (`a`, `b`) VALUES (3, 4);
INSERT INTO `test` (`a`, `b`) VALUES (4, 5);
INSERT INTO `test` (`a`, `b`) VALUES (5, 6);
INSERT INTO `test` (`a`, `b`) VALUES (6, 7);
$ mysql -u root -proot test < ~/Desktop/test.sql

The table itself is simple:

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)

In essence, the question is what are some lower level ways of inserting data to MySQL?

  • 2
    If you have your data in some other format than insert statements, you might use LOAD DATA INFILE which is supposed to be optimized for batch loads.
    – jkavalik
    Commented May 17, 2016 at 5:26
  • Don't forget to set max_heap_table_size before creating the table.
    – Rick James
    Commented May 27, 2016 at 0:10

1 Answer 1


You need to batch the inserts. This is done by means of extended inserts.

In your question, you can take the row and make them a single insert as follows:

INSERT INTO `test` (`a`, `b`) VALUES
(4, 5),(5, 6),(6, 7),(6, 7),(1, 2),(2, 3),(3, 4),(4, 5),(5, 6),(6, 7);

Why do it this way ?

When you insert into a MEMORY table, there is still some mild disk I/O to check the table's existence by hitting the table .frm file. I wrote about this 5 years ago (I am using the MEMORY storage engine but MySQL still writes to my disk...Why?)

Your question's example does 10 inserts. That's 10 disk reads to the .frm file and 10 full table locks (the lock granularity for the MEMORY storage engine is a full table lock).

Changing it into a single extended insert, the .frm is read only once and the table locked once.


In your case, if test.sql has 10 million rows, there will 10 million tables locks and 10 million access of the .frm file.

You should setup test.sql to batch like 10,000 rows in a single insert. Don't worry, mysqldumps create extended insert like this all the time.

That way, you will have 1000 inserts instead of 10,000,000.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.