in Web & Tech

Faster Import of InnoDB Tables in MySQL

After waiting for more than an hour to import a MySQL dump of ~270 MB I started looking for the bottleneck. My database was mixed with InnoDB and MyISAM tables and it turned out the InnoDB tables took ages to import. I fixed it by wrapping my SQL statements for the InnoDB tables with the following statement:

SET autocommit=0;
# table definitions for the InnoDB table 
COMMIT;

If I understood it correctly this reduces the disk input massively. Don’t forget the COMMIT statement at the end, as the MySQL reference says:

After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables (such as those for InnoDB, BDB, or NDBCLUSTER) are not made permanent immediately. You must use COMMIT to store your changes to disk or ROLLBACK to ignore the changes.

https://dev.mysql.com/doc/refman/5.0/en/commit.html

Write a Comment

Comment

  • Related Content by Tag