Why is MySQL InnoDB insert so slow?

The default value for InnoDB is actually pretty bad. InnoDB is very RAM dependent, you might find better result if you tweak the settings. Here's a guide that I used InnoDB optimization basic

InnoDB has transaction support, you're not using explicit transactions so innoDB has to do a commit after each statement ("performs a log flush to disk for every insert").

Execute this command before your loop:

START TRANSACTION

and this after you loop

COMMIT

InnoDB doesn't cope well with 'random' primary keys. Try a sequential key or auto-increment, and I believe you'll see better performance. Your 'real' key field could still be indexed, but for a bulk insert you might be better off dropping and recreating that index in one hit after the insert in complete. Would be interested to see your benchmarks for that!

Some related questions

• Slow INSERT into InnoDB table with random PRIMARY KEY column's value
• Why do MySQL InnoDB inserts / updates on large tables get very slow when there are a few indexes?
• InnoDB inserts very slow and slowing down

I've needed to do testing of an insert-heavy application in both MyISAM and InnoDB simultaneously. There was a single setting that resolved the speed issues I was having. Try setting the following:

innodb_flush_log_at_trx_commit = 2

Make sure you understand the risks by reading about the setting here.

Also see https://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2/12612 and https://dba.stackexchange.com/a/29974/9405