Loading a CSV Into MySQL Workbench Versus the Command Line

There are two honest ways to put a CSV into MySQL: a graphical wizard that you click through, and a LOAD DATA statement that you type. People argue about which is "better" without noticing that they answer different problems. The wizard is for looking at a file and getting it into a table with a few clicks, and the statement is for loading a lot of data repeatably. This article puts numbers on the difference, and is clear about what I measured and what I did not, since I did not drive the Workbench interface for it. The command line path is documented in the MySQL import guide, and the statement is explained clause by clause in using LOAD DATA INFILE. The server's own account of the general log is in the MySQL manual.

What the wizard is documented to do

MySQL's Workbench manual page for the import wizard describes it as importing from a JSON or CSV file. You right click a table, choose the Table Data Import Wizard, select the file, select the destination table, adjust the CSV settings, and review. The import includes table, column and type mapping, and the configuration includes separators, column selection and encoding, with the encoding field expected to match the file's. The page does not say how the rows reach the server, and it does not give any speed figure or advice about large files.

That is all I can say as a fact. The wizard is built for a person looking at a file with a preview, deciding which columns map where, and clicking Next. For that job, the visibility is the feature: you see the first rows parsed before you commit to anything, you can create a table from the file in the same dialog, and you do not need to remember the FIELDS TERMINATED BY syntax.

Why the difference in speed matters

What matters for a large file is the number of statements the server has to parse. A client that sends one INSERT per row pays for parsing, a network round trip and, depending on autocommit, a log flush for every row. A client that sends many rows per statement pays much less, and LOAD DATA pays least, since the server reads the file in one statement. I measured the three on MySQL 8.4 with the same data:

python bench_mysql.py

method                                          median s
LOAD DATA into a table with a primary key           5.86      341,455 rows/s
LOAD DATA, checks off, one transaction              5.84      342,476 rows/s
LOAD DATA with no key, key added after              7.99      250,321 rows/s
one INSERT per row, one commit (100,000 rows)       7.69       13,010 rows/s
multi-row INSERT, 1,000 rows each (100,000 rows)      0.96      104,253 rows/s

One INSERT per row, with one commit at the end, ran at 13,010 rows per second. A multi-row INSERT with 1,000 rows per statement ran at 104,253, eight times faster. LOAD DATA ran at 341,455 rows per second, 26 times faster than row at a time. The table prints seconds for 100,000 rows in the insert cases and for 2,000,000 in the LOAD DATA cases, so use the rows per second column to compare.

Applied to a file of 2,000,000 rows, those rates are about 154 seconds for the row at a time pattern, 19 seconds for batches of 1,000 and 6 seconds for LOAD DATA. Whether a wizard does any of the three I cannot tell you from the documentation. If it inserts a row at a time, a 2,000,000 row import is a coffee break; if it batches, a minute. For a file of a few thousand rows, none of it matters, and for the large case, the way to know is to measure on your machine.

Find out what any client sends

You do not need to take anyone's word for what a GUI does. MySQL can log every statement it receives, and for a minute of testing that is the easiest way to see how a tool loads data. Turn on the general log into a table, run the import, turn it off and read it:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
-- run the import in the tool
SET GLOBAL general_log = 'OFF';
SELECT event_time, LEFT(CONVERT(argument USING utf8mb4), 120) FROM mysql.general_log WHERE command_type = 'Query';

I ran that around two imports in a script, three rows with executemany and one LOAD DATA, to show how it looks:

python general_log_demo.py

Query  INSERT INTO probe VALUES (1, 'Ana'),(2, 'Bo'),(3, 'Cy')
Query  LOAD DATA LOCAL INFILE '/batches/csv-0001-0200/lab/data/my/customers.csv' INTO TABLE probe FIELDS TE

The driver's executemany was rewritten into one statement with three value tuples, which a client library may do to speed things up, and the LOAD DATA LOCAL INFILE appeared as one statement naming the file. That is the evidence you want from a tool you do not control: one line per statement. If you point the wizard at a 50,000 row file and the log fills with 50,000 single row inserts, you know its speed. If you see a handful of statements, you know it is batching. Turn the log off afterwards, since it records everything, including statements with passwords and data, and it grows without limit.

The checks are the same either way

Whichever you use, the questions after the load are the same. How many rows arrived, compared with a strict count of the file? Were there warnings, and in particular were any dates, numbers or strings altered? Did the character set survive? A wizard puts these checks behind a result screen that says the import finished, and a green tick does not answer the question "was every value stored as it was in the file". Run SELECT COUNT(*), look at MIN and MAX of the dates and numbers, and read a few rows containing accents and quotes.

Everything the import guide says about LOCAL and strictness applies if the tool uses LOAD DATA LOCAL underneath, and nothing of it applies if it sends INSERT statements, which honor strict mode and stop at the first bad value. That difference, errors against zero dates, is as good a reason as speed to know which one you are getting.

So which one

For a one-off import of a small file where I want to see what I am doing, a wizard is a sound choice, and I would use the preview to catch delimiter and encoding problems before they matter. For anything over about 100,000 rows, or anything I might run again, I write the LOAD DATA statement, because it is faster by an order of magnitude on the evidence above and because a statement in a script is something a colleague can read, review and rerun. The graphical tool can still help: build the table in it, set the types and look at the data, and then script the load.

If the wizard offers to show you the SQL it will run, or you can capture it with the general log, copy it into a file. That turns a click path that nobody can reproduce into code.

What I could not test

I did not run Workbench, so I have no measurement of its import speed, no view of how it handles quotes or NULL, and no knowledge of what its type guessing does with a column of ZIP codes. I would test those three things before using it on anything that matters: a file with a leading zero column, a file with an empty field in a numeric column and a file with a quoted comma. Other graphical clients, such as DBeaver, DataGrip and the TablePlus import, each have their own implementation, and the same log technique will show you what they do.

The command line side has its own limits that no interface removes: the local_infile switches, the secure_file_priv directory and the quiet conversion of bad values under LOCAL, which the local infile article and the 1290 error article cover.