|First, some basic benchmarks. On a modern machine properly configured, you might insert 200-byte InnoDB rows at a rate of 2k rows/sec with single committed Inserts, six times that rate with multi-row Insert syntax and one Commit, three times that rate again with Load Data Infile. Multi-row Inserts are much quicker than single-row Insert syntax; Load Data Infile is much quicker than either.|
MyISAM has no transaction logic so it's 20-25 times faster Loading huge input files into a table.
If bulk inserts into InnoDB tables can't be avoided, there's wide agreement that performance is improved by disabling
For huge Inserts on a system with lots of RAM and multiple cores, in my.cnf try ...
See here for Bill Karwin's excellent slideshow on optimising InnoDB bulk inserts.
For my.cnf settings that improve Insert performance, see ... https://nbsoftsolutions.com/blog/optimizing-innodb-bulk-insert
It's usually best to leave
On partitioning and InniDB and Load Data, see http://www.ajaydivakaran.com/mysql-innodb-are-inserts-slowing-down
... and on the benefits of chunking, see Baron Schwarz's pt-fifo_split.