23

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?

Cricket
  • 491
  • 1
  • 7
  • 16
  • You should be able to upload 40k rows per minute. How do you import the records? – Daniel W. Oct 30 '13 at 12:36
  • 1
    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? – David Oct 30 '13 at 12:41
  • You could also use prepared statements - that is if you are inserting through a programming language. – alandarev Oct 30 '13 at 12:42
  • 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) – Cricket Oct 30 '13 at 16:03
  • Possible duplicate of [How to insert 20 million record into MySql database as fast as possible](https://stackoverflow.com/q/8474926/608639). – jww Mar 22 '19 at 17:25

1 Answers1

47

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.

vallentin
  • 23,478
  • 6
  • 59
  • 81
  • 2
    I've tried inserting about 50000 rows with single query and get error that sql query is too large. – Josef Dec 12 '14 at 13:21
  • 1
    @Josef then create a new query each time you exceed x rows. – vallentin Dec 12 '14 at 18:47
  • 13
    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 – Kimutai Sep 19 '15 at 09:32
  • 9
    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. – sobstel Oct 21 '16 at 07:20
  • maybe it looks logical, but what if inserted data are external (user, third party company) and query have to be protected from sql injections? – Marek R Jan 24 '19 at 13:46
  • 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? – jww Mar 22 '19 at 17:21
  • updated link in the answer https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html – Gagan Gupta Mar 06 '20 at 07:45
  • 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 – Atmiya Kolsawala Oct 05 '21 at 04:50
  • How can I use this query with java prepared statement. There is batchUpdate but still it creates the query with single insert. – Raman Joshi Oct 12 '21 at 13:13