How Can I Optimize MySQL LOAD DATA INFILE for Faster Data Imports?

Optimizing MySQL LOAD DATA INFILE for Speedy Data Import

Importing massive datasets into MySQL using LOAD DATA INFILE can be time-consuming, particularly when creating indexes after the import. This article explores various techniques to accelerate the process and address the issue of terminated queries continuing to execute in the database.

Accelerating LOAD DATA INFILE

For InnoDB tables, consider the following optimization tips:

• Sort the CSV file in primary key order: InnoDB utilizes clustered primary keys, so sorting the file will significantly speed up the loading process.
• Truncate table before loading: Remove existing data to minimize the time spent reading and writing.
• Disable autocommit: Set autocommit=0 to delay the transaction commit until after the entire import, reducing the number of commits and improving performance.
• Disable checks: Temporarily disable unique and foreign key checks using set unique_checks=0 and set foreign_key_checks=0.
• Disable binary logging: Suspend binary logging with set sql_log_bin=0 to eliminate overhead associated with writing logs.
• Split CSV file into chunks: Divide the CSV file into smaller segments for faster loading.

Terminating Queries

To terminate a slow query without restarting MySQL, try the following:

• KILL command: Issue the KILL {query_id} command to terminate the specific query.
• Close client connection: Forcefully closing the terminal window where the query was executed will also stop the running query. However, this may cause data corruption or inconsistencies.

By applying these optimizations, you can significantly improve the performance of LOAD DATA INFILE and streamline the data import process.

The above is the detailed content of How Can I Optimize MySQL LOAD DATA INFILE for Faster Data Imports?. For more information, please follow other related articles on the PHP Chinese website!