MySQL Performance Optimization Techniques: Bulk Inserts, INSERT, GROUP BY, ORDER BY, OR, Nested Queries, and SQL Hints

1. Bulk Insert Optimization

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 . For InnoDB, loading data in primary‑key order dramatically reduces import time (e.g., 15.23 s vs 26.54 s).

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

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

Additional tricks include setting SET UNIQUE_CHECKS=0 , SET AUTOCOMMIT=0 , and increasing bulk_insert_buffer_size for MyISAM.

2. INSERT Optimization

Use multi‑value INSERT statements to reduce round‑trips, and consider INSERT DELAYED for high‑concurrency scenarios.

INSERT INTO tablename VALUES (1,2),(1,3),(1,4);

Benchmark shows eight single‑row INSERTs take ~0.033 s each, while a multi‑value INSERT for eight rows completes in 0.004 s.

3. GROUP BY Optimization

MySQL automatically adds an ORDER BY on GROUP BY columns; you can suppress unnecessary sorting with ORDER BY NULL to avoid the Using filesort operation.

4. ORDER BY Optimization

An index can satisfy ORDER BY when the WHERE clause and ORDER BY use the same composite index, the sort direction is uniform, and the index order matches the ORDER BY order. Mixed ASC/DESC or mismatched columns force a full sort.

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

5. OR Condition Optimization

MySQL can use index_merge when each OR operand has its own index. Using a composite index on one side and a non‑indexed column on the other results in a full table scan.

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

6. Nested Query Optimization

Replace sub‑queries with JOINs when possible, especially if the joined columns are indexed, to avoid temporary tables and improve performance.

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

7. SQL Hints

Use USE INDEX to force MySQL to consider only specific indexes, IGNORE INDEX to exclude them, and FORCE INDEX to compel the optimizer to use a particular index even if it predicts a full scan.

EXPLAIN SELECT * FROM user_info USE INDEX(id_index) WHERE user_id>0;

EXPLAIN SELECT * FROM user_info IGNORE INDEX(primary,ind_name_id,id_index) WHERE user_id>0;

EXPLAIN SELECT * FROM user_info FORCE INDEX(id_index) WHERE user_id>0;

Conclusion

Many performance issues stem from inefficient SQL; the techniques above provide a starting point for analysis and tuning, though real‑world scenarios may require deeper investigation.