How can mysql insert millions records faster? [closed]

Question

I wanted to insert about millions records into my database, but it went very slow with a speed about 40,000 records/hour, I dont think that my hardware is too slow, because i saw the diskio is under 2 MiB/s. I have many tables seperated in different .sql-files. One single record is also very simple, one record has less than 15 columns and one column has less than 30 characters. I did this job under archlinux with mysql 5.3. Do you guys have any ideas? Or is this speed not slow?

Turnout

You should be able to upload 40k rows per minute. How do you import the records? – Chirk

What's the bottleneck? How are you inserting the records? Can you profile this in some way to determine what part is taking a long time? – Sorcerer

You could also use prepared statements - that is if you are inserting through a programming language. – Maxim

Actually ich dumped all tables from a sqlite database and saved as .sql files. I wanted to import these tables to mysql-server with this command: mysql -u user -p database < table_name.sql. These sql files look like: CREATE TABLE T ( ... ); INSERT INTO table1 (field1, field2) VALUES ("data1", "data2"); INSERT INTO table1 (field1, field2) VALUES ("data1", "data2"); ......(millions rows) – Turnout

Possible duplicate of How to insert 20 million record into MySql database as fast as possible. – Housefather

Answer 1

It's most likely because you're inserting records like this:

INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");

Sending a new query each time you need to INSERT something is bad for performance. Instead combine those queries into a single query, like this.

INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2"),
                                                 ("data1", "data2"),
                                                 ("data1", "data2"),
                                                 ("data1", "data2"),
                                                 ("data1", "data2");

You can also read more about insert speed in the MySQL Docs. It clearly describs the following.

To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.

Of course don't combine ALL of them, if the amount is HUGE. Say you have 1000 rows you need to insert, then don't do it one at a time. But you probably shouldn't equally try to have all 1000 rows in a single query. Instead break it into smaller sizes.

If it's still really slow, then it might just be because your server is slow.

Note that you of course don't need all those spaces in the combined query, that is simply to get a better overview of the answer.

Imbibe

I've tried inserting about 50000 rows with single query and get error that sql query is too large. – Orang

@Orang then create a new query each time you exceed x rows. – Imbibe

This answer is really helpful. The step by step inserts took me close to 6 hours to complete inserting 1.5M records, but using this approach with a group of 10000 per step, took me just ~ 90 seconds :D – Cullum

If you are able to import data from a text file, use LOAD DATA INFILE. MySQL manual says this is usually 20 times faster than using INSERT statements. – Difficile

maybe it looks logical, but what if inserted data are external (user, third party company) and query have to be protected from sql injections? – Frankhouse

I'd like to avoid rewriting some C code. Is there a way to tell MySQL (1) all insert will be the same, and (2) batch them (cache them?) until some number are ready? – Housefather

updated link in the answer dev.mysql.com/doc/refman/5.7/en/insert-optimization.html – Carrew

Is there any way to insert it from a file, as i have exported my data from one server and i have to insert it in another different server – Belsky

How can I use this query with java prepared statement. There is batchUpdate but still it creates the query with single insert. – Corsage