Quicker Ways to Import Data into InnoDb
For those of you who ever have to put data into MySQL database, here’s a quick little tip. If you’re importing into a table that is using the InnoDb engine (versus the MyISAM), wrap your import file in the following:
SET AUTOCOMMIT=0;
SET UNIQUE_CHECKS=0;
… SQL import statements …
SET UNIQUE_CHECKS=1;
COMMIT;
This will significantly improve the import time (by orders of magnitude, for imports beyond tens of thousands of records).
Edit: Quick point of reference. Importing 60,000 records (simple inserts) took about 10 minutes or about 10,000 per minute. After switching this fix in, I inserted 10,000 more records in under 10 seconds (so fast, I forgot to time it).
