Tips for speeding up dump and import data in MySQL

Importing a large volume of SQL data into MySQL usually takes quite a while. The following small tips can help speed up large SQL imports. Temporarily disable indexes (Disable index) Disabling indexes during import greatly improves import speed and reduces MySQL server workload, helping avoid disruption when importing into an online system. Before importing, disable indexes with:

ALTER TABLE `table_name` DISABLE KEYS;

After importing, enable indexes with:

ALTER TABLE `table_name` ENABLE KEYS;

* Replace table_name with the name of the table containing indexes. Tip for MyISAM tables Use INSERT DELAYED instead of ordinary INSERT. This avoids waiting for the system’s response when the insert finishes and can improve import speed to some extent. For InnoDB tables, additional commands are required Disable autocommit mode to prevent logs from being flushed to disk for every insert. If there is a UNIQUE constraint, temporarily disable checks to speed up the import so the system does not check the data during import. If the table has FOREIGN KEY constraints, temporarily disable checks to speed up the import so the system does not check foreign keys during import. Run these commands before importing:

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

After importing, run:

SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;