I needed to do optimise some queries on a big dataset at work, so figured I’d export recent data to my windows development machine rather than tax the live servers.

Datadump: 1M rows, returned after lunch an hour later and only 70k rows had inserted

How To Optimise
By default a recent MySQL install on windows creates a my.ini file (not my.cnf) in c:/.  This file is practically empty and won’t do you much good.  So I overwrote it with the my-medium file supplied in the distro and altered two key values respective to the INNO DB table type I was using:

• set-variable = innodb_buffer_pool_size=256M (this represent 50% of physical RAM on my machine)
• innodb_flush_log_at_trx_commit=0 (this disables transaction logging that roughly doubles the resources required by MySQL)

Truncated the now 75k rows and reimported the data – whole million rows went in in under a minute!

nb: you may want to return to your default values after big imports, the above setting hoard a lot of RAM.