MySQL Workbench's Table Data Import Wizard takes hours on a big CSV
The Table Data Import Wizard sits on the import step for hoursShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
LOAD 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
LOAD 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
The target table is large and the CSV rows are not in primary key orderThe rows arrive in a different order than the primary keyFix
- 1
What does the error look like?
The Table Data Import Wizard sits on the import step for hoursA 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
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
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 DATA3Verify
Time the load again and compare it with the run on the unsorted file.
SELECT COUNT(*) FROM mydb.mytable;Confirmed by dev.mysql.com
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
LOAD DATA LOCAL INFILE is refused: loading local data is disabled
ERROR 3950 (42000): Loading local data is disabled; this must be enabled on both the client and server side
LOAD DATA LOCAL works only when both the server and the client allow it.
3 causes, each with a fixDatabases
LOAD DATA INFILE or INTO OUTFILE blocked by --secure-file-priv
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
The server only reads and writes files inside the folder set in secure_file_priv.
3 causes, each with a fixDatabases
LOAD DATA INFILE fails with 'Access denied' for a normal MySQL user
LOAD DATA INFILE works as root but not as the application user
A server-side LOAD DATA INFILE needs the global FILE privilege on top of the usual table rights.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fix