Boost MySQL Bulk Insert and Query Performance with Proven Optimizations

The article provides practical tips for optimizing large data insertions and query performance in MySQL.

Bulk Insert Optimizations

For MyISAM tables you can disable and enable non‑unique indexes with ALTER TABLE tbl_name DISABLE KEYS and ALTER TABLE tbl_name ENABLE KEYS, which speeds up bulk loads.

For InnoDB tables, sort the input file by primary key, set SET UNIQUE_CHECKS=0 and SET AUTOCOMMIT=0 before loading, use LOAD DATA INFILE (often 20× faster than INSERT), increase bulk_insert_buffer_size (MyISAM only), and consider INSERT DELAYED or LOW_PRIORITY when appropriate.

ALTER TABLE tbl_name DISABLE KEYS;
-- load the data
ALTER TABLE tbl_name ENABLE KEYS;

Example: loading 126,732 rows sorted by primary key took 15.23 s, while unsorted data took 26.54 s.

Using multi‑value INSERT reduces client‑server round‑trips. Inserting eight rows with separate statements took ~0.171 s total; the same eight rows with a single multi‑value INSERT took only 0.038 s.

GROUP BY Optimization

MySQL automatically sorts GROUP BY results; adding ORDER BY NULL disables the extra sort and can be verified with EXPLAIN.

SELECT id, SUM(money) FROM user_1 GROUP BY name ORDER BY NULL;

ORDER BY Optimization

MySQL can satisfy an ORDER BY using an index when the WHERE clause and ORDER BY use the same composite index, the index order matches the ORDER BY direction, and all columns are sorted the same way (all ASC or all DESC). Mixed directions or mismatched columns prevent index usage.

SELECT * FROM user WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

OR Condition Optimization

For OR queries each condition must have an independent index; otherwise MySQL falls back to a full table scan. Using independent indexes enables the index_merge strategy, while mixing indexed and non‑indexed columns results in an ALL scan.

EXPLAIN SELECT * FROM user_info WHERE user_id=1 OR idcard='222222';

SQL Hints

USE INDEX

forces MySQL to consider only the listed indexes; IGNORE INDEX removes indexes from consideration; FORCE INDEX forces the use of a specific index even if the optimizer would otherwise choose a table scan.

SELECT * FROM user_info USE INDEX(id_index) WHERE user_id>0;
SELECT * FROM user_info IGNORE INDEX(primary) WHERE user_id>0;
SELECT * FROM user_info FORCE INDEX(id_index) WHERE user_id>0;

Nested Query vs Join

Replacing a sub‑query with a LEFT JOIN can avoid temporary tables and improve performance when the join columns are indexed.

SELECT * FROM student u LEFT JOIN major m ON u.major_id=m.major_id WHERE m.major_id IS NULL;

Conclusion

Many performance issues stem from sub‑optimal SQL; the article lists straightforward optimizations—bulk‑load settings, index‑aware ORDER BY and GROUP BY, proper use of OR conditions, and SQL hints—that can be applied during troubleshooting to regain efficiency.