How to Bulk Load Data in MySQL

Why choose LOAD DATA INFILE over many INSERTs?

LOAD DATA INFILE streams the entire file directly into the storage engine, bypassing SQL parsing for every row. This cuts network round-trips and reduces redo/undo overhead, resulting in imports that are 10-20× faster than loops of INSERT statements.

What file formats work best?

Plain CSV or TSV files exported from spreadsheets, ETL tools, or other databases import cleanly. Use the same delimiter, enclosure, and line-ending settings in both export and LOAD DATA INFILE to avoid parsing errors.

How do I point MySQL to the file location?

Use LOCAL if the file sits on the client machine:
LOAD DATA LOCAL INFILE '/path/to/orders.csv' ...
Omit LOCAL when the file is on the MySQL server and secure_file_priv allows the directory.

Which options should I always set?

Specify FIELDS TERMINATED BY ',' and ENCLOSED BY '"' for CSV, and match LINES TERMINATED BY '\n'. Include IGNORE 1 LINES to skip headers. Add SET created_at = NOW() to populate audit columns not present in the file.

Can I load into selected columns?

Yes—list the target columns after the table name. Columns omitted from the list take their default values, letting you ignore auto-increment IDs or computed fields.

How do I handle duplicate keys?

Add REPLACE to overwrite conflicting rows or IGNORE to skip them. Pick one; omitting both aborts the entire load on the first duplicate.

Is the load transaction-safe?

LOAD DATA INFILE obeys autocommit. Wrap it in START TRANSACTION / COMMIT to ensure an all-or-nothing import. This is crucial when loading into multiple related tables.

How do I verify performance?

Enable SET GLOBAL general_log = 'ON' temporarily and watch the rows-per-second metric. For million-row CSVs on SSDs, speeds of 50k–150k rows/sec are typical.

Best practices recap

Pre-disable secondary indexes, load from the same server disk, import into an empty staging table first, and always validate row counts. Re-enable indexes and run checksum comparisons before promoting data.