Boost MySQL Performance: Bulk Insert, GROUP BY, ORDER BY, and OR Query Optimizations

1. Bulk Data Insertion Optimization

(1) For MyISAM tables, use DISABLE KEYS and ENABLE KEYS to suspend non‑unique index updates.

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

(2) For InnoDB tables, apply several tricks:

① Load data in primary‑key order; this can cut the import time from 26.54 s to 15.23 s for 126 732 rows.

mysql> load data infile 'mysql/bulk_insert.txt' into table user;
Query OK, 126732 rows affected (15.23 sec)

② Set SET UNIQUE_CHECKS=0 before loading and restore to 1 afterwards.

③ Set SET AUTOCOMMIT=0 before loading and restore to 1 afterwards.

2. INSERT Optimization

(1) Use multi‑value INSERT statements to reduce client‑server round‑trips. INSERT INTO tablename VALUES (1,2),(1,3),(1,4); Experiment inserting eight rows individually took 0.171 s; using a multi‑value form reduced it to 0.038 s.

(2) INSERT DELAYED can improve speed when many rows are inserted from different clients; LOW_PRIORITY does the opposite.

(3) Store index files and data files on separate disks (effective for InnoDB).

(4) Increase bulk_insert_buffer_size for MyISAM bulk loads.

(5) LOAD DATA INFILE is usually ~20× faster than INSERT.

3. GROUP BY Optimization

MySQL sorts GROUP BY results by default; adding ORDER BY NULL disables the sort and removes the “Using filesort” extra.

EXPLAIN SELECT id, SUM(money) FROM user_1 GROUP BY name;
-- with ORDER BY NULL the filesort disappears

4. ORDER BY Optimization

MySQL can satisfy ORDER BY using an index if the following conditions hold:

(1) WHERE and ORDER BY use the same composite index.

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

(2) ORDER BY column order matches the index order.

SELECT * FROM user ORDER BY key_part1, key_part2;

(3) All columns are sorted in the same direction (all ASC or all DESC).

SELECT * FROM user ORDER BY key_part1 DESC, key_part2 DESC;

MySQL cannot use an index when:

ORDER BY mixes ASC and DESC.

WHERE columns differ from ORDER BY columns.

ORDER BY uses different columns than the index.

5. OR Optimization

For OR conditions MySQL can use an index only if each operand has a separate index; a composite index will not be used.

Example table user_info with indexes PRIMARY(idcard), ind_name_id(user_id,name), id_index(user_id).

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

When one side of the OR lacks an index, the whole query falls back to a full table scan.

6. Nested Query Optimization

Replacing a sub‑query with a JOIN can avoid creating a temporary table in memory, especially when the ON columns are indexed.

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

The equivalent sub‑query version creates a temporary table and is slower.

7. SQL Hints

MySQL supports index hints to influence the optimizer: USE INDEX(index_list) forces the optimizer to consider only the listed indexes. IGNORE INDEX(index_list) removes the listed indexes from consideration. FORCE INDEX(index_list) makes the optimizer use the listed index even if it is not optimal.

EXPLAIN SELECT * FROM user_info FORCE INDEX(id_index) WHERE user_id>0;
-- uses range scan on id_index

Conclusion

Many performance problems stem from inefficient SQL; the techniques above provide a starting point for analysis and tuning.