Writing solutions to MySQL

I was looking at some code from a client where solution records are stored in a MySQL database. They use standard SQL INSERT statements to do this. Even for small number of solution records this is quite slow. I suggest a few simple improvements. E.g. MySQL has an interesting SQL INSERT extension where you can add multiple VALUEs to a single INSERT statement, like:

INSERT INTO mytable (a,b,c) VALUES (1,2,3),(4,5,6),(7,8,9)

In addition I surrounded the INSERTs by a transaction, which will increase the performance even more (there will be only one commit). For the fastest performance a bulk insert can be done using the LOAD DATA INFILE statement which can load a text file directly into a table.

For n=1,000 solution records I saw:

Method	Time for 1,000 solution points	Note
(1) INSERT	41 seconds	this was the original setup: 12,000 inserts
(2) INSERT multiple values	7 seconds	2,000 inserts
(2)+transactions	1.2 seconds	2,000 inserts
LOAD DATA INFILE	0.3 seconds	2 statements to load 2 csv files

Some optimization models generate a ton of solution results, and in those cases it may be useful to spend a little bit of time on deciding how to store solutions in a database.