How to speed up MySQL inserts

One would assume that if an application had two threads, and while one thread inserted data into one table and the other thread inserted data into another table, the inserts would be faster than if both threads inserted into the same table? No. It isn't faster.
The various threads that MySQL has, are to manage the other different operations.

There however is a technique that can improve insert speed further.

Smart programmers already know that using a batch insert is more efficient and faster than calling INSERT multiple times from their program.

What you perhaps do not know, is that after you send the batch of insert statements to MySQL, they are executed internally as separate inserts.

INSERT INTO Table1 VALUES (1,2,3) 
INSERT INTO Table1 VALUES (4,5,6)

What if you could combine it into a single insert statement like this:

INSERT INTO X VALUES (1,2,3),(4,5,6)

It'll actually execute faster! Some people report a 2x increase in write speed. Others, report a 40x increase.


How to do it? 

Just set the rewriteBatchedStatements=true value in the connection string.

So if you normally connect to MySQL like this...

Connection conn = DriverManager.getConnection("jdbc:mysql://"+ domainName +"/"+configRef.getBallastDatabaseNameOfSQL()+"?" + "user=" + username + "&password=" + password);

...you just have to change it to:

Connection conn = DriverManager.getConnection("jdbc:mysql://"+ domainName +"/"+configRef.getBallastDatabaseNameOfSQL()+"?" + "user=" + username + "&password=" + password + "&rewriteBatchedStatements=true");


In my opinion, MySQL should have kept this as default behaviour and the user should have had to disable it only if they were doing very large batch inserts.

Do try this out even if you don't execute too many inserts. In applications dealing with a lot of data though, this option can come as a life saver! You save a humongous amount of time with these super fast inserts.