Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL Workbench's Table Data Import Wizard takes hours on a big CSV

ErrorThe Table Data Import Wizard sits on the import step for hours

Short answer

Time the same file with LOAD DATA in an SQL editor. The manual says LOAD DATA is usually about 20 times faster than INSERT statements. If it is fast, use it instead of the wizard. If it is still slow, the InnoDB table pays a per-row cost: switch off autocommit and the key checks for that session, and load in primary key order.

  • Applies to MySQL 8.4, MySQL Workbench
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Load the same CSV with LOAD DATA from the SQL editor, then compare the time. The wizard does the same job through a form, so a big difference tells you the loading path is the problem and not the table. Check first where the server may read files from.

    SELECT @@secure_file_priv;
    LOAD DATA INFILE 'data.csv' INTO TABLE mydb.mytable
      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
      LINES TERMINATED BY '\n'
      IGNORE 1 LINES;
  2. 2Match what you see, then jump to its fix

    1. 1LOAD DATA finishes the same file many times faster than the wizardThe file is loaded as a stream of INSERT statements instead of one LOAD DATAFix
    2. 2LOAD DATA is faster than the wizard but still slow, and the table has unique secondary keys or foreign keysThe InnoDB table flushes and checks on every single rowFix
    3. 3The target table is large and the CSV rows are not in primary key orderThe rows arrive in a different order than the primary keyFix

What does the error look like?

  • The Table Data Import Wizard sits on the import step for hours
  • A CSV with hundreds of thousands of rows imports at only a few rows per second

Cause 1 of 3

The file is loaded as a stream of INSERT statements instead of one LOAD DATA

1Check

The Workbench manual says only that the wizard imports CSV and JSON files and does not describe how it writes the rows, so measure it. MySQL's manual says LOAD DATA reads rows from a text file at very high speed and is usually 20 times faster than INSERT statements, and that a multiple-row INSERT is far faster than single-row ones.

SHOW VARIABLES LIKE 'local_infile';

2Fix

Use LOAD DATA for the big file. Without LOCAL the file must sit on the server host, inside the directory that secure_file_priv names, and you need the FILE privilege. With LOCAL the file stays on your machine, but both the server and the client must allow it. For very large files the manual also points to MySQL Shell's parallel import utility util.importTable().

LOAD DATA LOCAL INFILE '/path/to/data.csv' INTO TABLE mydb.mytable
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES;

3Verify

The statement reports the number of rows read and finishes in a fraction of the wizard's time.

SELECT COUNT(*) FROM mydb.mytable;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The InnoDB table flushes and checks on every single row

1Check

The manual says InnoDB flushes the log to disk for every insert while autocommit is on. It also says that, for tables with UNIQUE secondary keys or FOREIGN KEY constraints, switching off uniqueness and foreign key checks for the import session saves a lot of disk I/O on big tables.

SELECT @@autocommit, @@unique_checks, @@foreign_key_checks;

2Fix

Change the three settings only for the session that does the load, run the load, commit, and set them back. Do this only if you are certain the file has no duplicate keys and no broken foreign keys, because the checks are not made while they are off.

SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;
LOAD DATA LOCAL INFILE '/path/to/data.csv' INTO TABLE mydb.mytable
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES;
COMMIT;
SET unique_checks = 1;
SET foreign_key_checks = 1;
SET autocommit = 1;

3Verify

The load finishes faster than before and the row count equals the number of data lines in the file.

SELECT COUNT(*) FROM mydb.mytable;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

The rows arrive in a different order than the primary key

1Check

The manual says bulk inserts are faster in PRIMARY KEY order, because InnoDB stores the table in a clustered index. It calls this particularly important for tables that do not fit in the buffer pool. Find the primary key, then see how the file is ordered.

SHOW CREATE TABLE mydb.mytable;

2Fix

Sort the CSV by the primary key column before you load it, and keep the header line out of the sorted part.

# sort the data rows by the key column in a spreadsheet or script,
# keep the header line first, then load the sorted file with LOAD DATA

3Verify

Time the load again and compare it with the run on the unsorted file.

SELECT COUNT(*) FROM mydb.mytable;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Use the wizard for small files and LOAD DATA for anything big.
  • Set autocommit, unique_checks and foreign_key_checks back to their normal values right after a bulk load, in the same script.
  • Export the CSV already sorted by the primary key of the target table.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.