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.