We switched to InnoDB tables a while ago. They offer transactions, foreign key constraints and a few other goodies that are missing from MyISAM. We knew writes would be slower due to foreign key checks, etc, but we didn’t imagine that importing a table with about 160,000 records would take almost an hour to import, while it only took about 15 seconds to import using MyISAM. I did some digging and figured out the solution. Add these lines to your dumps.
... Your dump here ...
This sped up InnoDB imports to pretty much the same speed as MyISAM imports for the table in question.
From time to time I get lazy and use
REPLACE INTO instead of checking for records and then only inserting the ones that don’t exist. It works really well for importing records where some records might be already in the database. Today, I ran into a little snag due to my laziness.
MySQL uses the following algorithm for
LOAD DATA ...):
- Try to insert the new row into the table
- While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
- Delete from the table the conflicting row that has the duplicate key value
- Try again to insert the new row into the table
When you combine this with foreign keys that are defined with
ON DELETE CASDCADE you end up nuking child records when you
REPLACE INTO a parent record. It only took me an hour and a half to debug this insanity.