At some points, many of our customers need to handle insertions of large data sets and run into slow insert statements. This article will try to give some guidance on how to speed up slow INSERT SQL queries.
The following recommendations may help optimize your data loading operations:
-
Remove existing indexes - Inserting data to a MySQL table will slow down once you add more and more indexes. Therefore, if you're loading data to a new table, it's best to load it to a table without any indexes, and only then create the indexes, once the data was loaded.
When you're inserting records, the database needs to update the indexes on every insert, which is costly in terms of performance. It's much faster to insert all records without indexing them, and then create the indexes once for the entire table.
-
Inserting data in bulks - To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end. In practice, instead of executing an INSERT for one record at a time, you can insert groups of records, for example 1000 records in each INSERT statement, using this structure of query:
INSERT INTO table_name (column_list) VALUES (value1, value2) (value1, value2), ... (value1, value2);
- Load data from a nearby location - if you're loading gigabytes or terabytes of data to a remote server, keep in mind that regardless of how long it takes to actually insert records to the database, your SQL client needs to transmit that data to that server. Therefore, the network can be a significant bottleneck. Also, opening and closing connections on each insert (or group of inserts) can sum up to a large amount of time for large SQL dumps.
Therefore, ideally, you should transmit the file to the database server once, and load it from the local filesystem. - LOAD DATA INFILE - If you're loading data from CSV/TSV, you should try to load it using LOAD DATA INFILE, rather than using INSERT statements. This statement is optimized by MySQL for best data loading performance.
Not sure how to further optimize your SQL insert queries, or your entire database? use EverSQL and start optimizing for free.
