How to optimize MySQL to insert millions row?

I need to insert millions rows into the MySQL database (InnoDB engine). I have a problem with time when the tables have big sizes. Almost all time is spent on insert queries. Maybe somebody know how to optimize it?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

To import large bulk of data into InnoDB:

1. set in MySQL configuration
   • innodb_doublewrite = 0
   • innodb_buffer_pool_size = 50%+ system memory
   • innodb_log_file_size = 512M
   • log-bin = 0
   • innodb_support_xa = 0
   • innodb_flush_log_at_trx_commit = 0
2. Add right after transaction start:

   SET FOREIGN_KEY_CHECKS = 0;

   SET UNIQUE_CHECKS = 0;

   SET AUTOCOMMIT = 0;

3. Set right before transaction end:

   SET UNIQUE_CHECKS = 1;

   SET FOREIGN_KEY_CHECKS = 1;

Method 2

If your talking about a large number of INSERT statements, look into something called transactions. I’m quite certain that most (if not all) languages that do SQL support transactions. They will speed up anything involving writing to the DB. An added bonus is that if something goes wrong you can rollback the changes.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0