How to Optimize MySQL Load Data Infile for Faster Imports?

Optimizing MySQL Load Data Infile for Accelerated Import

When importing massive datasets into MySQL using "Load data infile," users often encounter performance bottlenecks. This article explores ways to accelerate the import process, particularly for large InnoDB tables with multiple keys.

Accelerating Import

To optimize import speed, consider the following:

1. Primary Key Ordering: Sort the CSV file in the same order as the primary key of the target table. This optimizes the import process by leveraging InnoDB's clustered primary keys.
2. Truncation and Autocommit: Truncate the table before loading data and disable autocommit to improve performance.
3. Database Settings Optimization: Temporarily disable unique checks, foreign key checks, and binary logging to reduce overhead.
4. Chunked Loading: Split the CSV file into smaller chunks to minimize memory requirements and reduce lock contention.

Example Optimized Import Syntax:

truncate <table>;

set autocommit = 0;

load data infile <path> into table <table>...

commit;

set autocommit = 1;
set unique_checks = 1;
set foreign_key_checks = 1;
set sql_log_bin=1;

Query Termination without Restart

To terminate a slow running query without restarting MySQL, use the following command:

kill <process_id>;

This command terminates the specified process by its process ID, which can be obtained from the show processlist command.

The above is the detailed content of How to Optimize MySQL Load Data Infile for Faster Imports?. For more information, please follow other related articles on the PHP Chinese website!