Improving the performance of MySql bulk inserts

When I was trying to improve the performance of my old server, I came across this page. Unfortunately it didn’t help with the old server and just made me realise I needed to upgrade my server hardware.

But once I got my new hardware, I wanted some tweaks to improve the performance of importing postcode and property sales data. Both were taking days to complete. One suggestion from that page was to use SET autocommit=0;. I was a little sceptical, but was willing to try anything to speed up the import.

So all I did was add the statement on the start of every insert and add a counter to my code. After 1000 inserts, I committed the changes, using the following

              if (count % 1000 == 0)
              {
                command.CommandText = "COMMIT";
                command.ExecuteNonQuery();
              }

And this made a huge difference to my imports. Whereas I was importing for days before, now the time taken was down to hours. Maybe I could improve it further by committing the changes even less frequently, but I’m pretty happy with the current situation.

So in conclusion, if you need your data imports to run quicker, start using SET autocommit=0; now!