Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my application, my INSERTs seem to be taking a major chunk of the time. I have a large number of objects in memory (~40-50,000) which i want to insert into a table.

Lets take a sample table

amount INT(11) DEFAULT 0,
INDEX (bill_date)

Taking 3 rows as my batch size, the following are the approaches i could think of for inserting

Approach 1 - construct and fire 3 raw inserts

INSERT INTO bill (amount, bill_date) VALUES (10, '2012-01-01 00:00:00');
INSERT INTO bill (amount, bill_date) VALUES (20, '2012-01-02 00:00:00');
INSERT INTO bill (amount, bill_date) VALUES (40, '2013-02-05 00:00:00');

Approach 2 - clubbing the values into 1 query

INSERT INTO bill (amount, bill_date) VALUES 
(10, '2012-01-01 00:00:00'),
(20, '2012-01-02 00:00:00'),
(40, '2013-02-05 00:00:00');

Approach 3 - fire this query 1 time passing 6 parameters

INSERT INTO bill (amount, bill_date) VALUES 
(?, ?), (?, ?), (?, ?);

Approach 4 - Fire this prepared query 3 times changing the 2 parameters each time

INSERT INTO bill (amount, bill_date) VALUES (?, ?);

Any other approaches are welcome.

My question is

What is the quickest way to make multiple inserts in a table?

I have read this link on mysql insert speed and this guide to JDBC programming, but i'm not able to come to a conclusion.

My case -

Currently my table has ~ 20 columns, most of which are numbers, with a couple of varchar(60) and 1 text column. Mysql version 5.5. Running on INNODB and has 1 index on Integer primary keys. All the queries run in transaction.

I construct my queries from Java and use Spring JDBC to run the queries.

I am currently following Approach 3, It is taking around 10 seconds for 20,000 inserts to an empty table, not including the time it takes to construct the query.

To keep things in perspective, its taking 100-200 millis to fetch the data from the table.

Is there something i am missing? How do i make the inserts faster?

share|improve this question
Some observations: 1) Your table is "wide." Perhaps more normalization would allow you to insert only what's necessary? 2) You have an index. I'm not 100% certain how MySQL handles this, and 20k isn't huge, but you may be dealing with index overhead. 3) How is Spring actually inserting the data? RBAR? Batches? Sets? –  swasheck Jul 23 '13 at 19:37
I can't normalize it, its a history table. I need to keep all the information there. Spring logs say it is firing the query in the same structure that i pass it. So i don't think there is any batching etc Spring is doing. –  gap_j Jul 24 '13 at 3:10
Could you make your batch size large, say 1000? –  user1931858 Dec 14 '13 at 19:20

1 Answer 1

Some bulk data loading tips from mysql document is possible useful. https://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-bulk-data-loading.html

You can increase insert speed by some ways:

- turn off autocommit
- turn off unique check
- turn off foreign check

Hope this help !

share|improve this answer
If turning off constraint checks (unique, foreign key, ...) be very very sure that your data does not break them or your database is in an inconsistent state from that point onwards. –  David Spillett Jul 8 at 10:40

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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