Showing posts from May, 2013

MySQL Query Times with Varying Batch Insert Sizes

I'm always curious how different things affect performance, so when I had to import 775 company records with around 30 fields, 26,681 base unit records with almost 90 fields and 30,197 transmitter records with over 100 fields into a MySQL database, it became time to do some testing.

Initially, the process was taking a few seconds, but that was only a subset of the data. Once I got this larger set of data, it was taking 18:49 to import it, which wasn't acceptable. So, time to leave the straight "insert into ___ set ___=___" method that is so much more readable and move to "insert into ___ values ( __ , __ )" style.

The first thing I did in testing was to disable indexes, insert, then enable indexes again. This brought the time down to 9:27, which is a big improvement, but still not where I wanted to be.

Changing from set _=_ to values ( _ , _ ) made no real difference.

Then, I changed to doing multiple records in a single pass, and what a difference that mad…