How to Speed Up a Slow MySQL Import (10x Faster)

Importing a large dump shouldn't take all afternoon. Most slow imports are slow for one of a handful of reasons — and the fixes are mechanical. Here they are, ordered by how much time they save.

1. Disable key checks while importing

Rebuilding indexes and validating foreign keys on every single row is the biggest tax on an import. Turn them off for the duration, then back on:

SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;

SOURCE dump.sql;

SET foreign_key_checks = 1;
SET unique_checks = 1;
COMMIT;

Wrapping the whole import in a single transaction (autocommit = 0 ... COMMIT) means MySQL flushes to disk once instead of after every statement. On a spinning disk this alone can be a 5–10x win.

2. Use extended inserts in the dump

One INSERT per row is brutally slow. mysqldump batches rows by default, but if your dump has one row per statement, re-export with:

mysqldump --extended-insert --quick --single-transaction \
  database_name > dump.sql

• --extended-insert packs many rows into each INSERT (far fewer statements)
• --quick streams rows instead of buffering the whole table in RAM
• --single-transaction gives a consistent snapshot without locking InnoDB tables

3. Raise the InnoDB log buffer and packet size

If you control the server config, these two settings let MySQL chew through big batches without stalling:

[mysqld]
innodb_buffer_pool_size = 1G        # as much RAM as you can spare
innodb_log_file_size = 256M         # bigger = fewer checkpoint flushes
max_allowed_packet = 1G
innodb_flush_log_at_trx_commit = 2  # temporary, for import speed

Setting it to 2 trades a little crash safety for speed. Great during a one-off import, but set it back to 1 for production durability.

4. Use the CLI, not phpMyAdmin

phpMyAdmin adds PHP memory limits, execution timeouts, and HTTP overhead on top of the actual import. The command line skips all of that:

mysql -u user -p database_name < dump.sql

5. Watch progress with pv

A silent terminal makes a slow import feel infinite. Pipe through pv to get a live progress bar and ETA:

pv dump.sql | mysql -u user -p database_name

Quick reference