19 Common MySQL Optimization Techniques

This article presents nineteen frequently used MySQL optimization techniques that can significantly improve query performance and reduce resource consumption.

1. EXPLAIN – Use EXPLAIN to view the execution plan and focus on the type , key , key_len , rows , and extra columns. A good query should reach at least range type and avoid all.

2. Limit the number of values in IN clauses – Large IN lists increase cost; use BETWEEN for continuous ranges or replace with joins.

3. Specify column names instead of SELECT * – Selecting all columns adds unnecessary CPU, I/O, memory, and network overhead and prevents index covering.

4. Use LIMIT 1 when only one row is needed – Helps the optimizer choose a const access type.

5. Minimize sorting when the sort column is not indexed – Reduce or eliminate ORDER BY operations that cannot use an index.

6. Avoid OR when neither side is indexed – Replace with UNION ALL or other constructs to keep the query index‑friendly.

7. Prefer UNION ALL over UNION – UNION forces a distinct step that adds sorting and CPU cost; use UNION ALL when duplicate rows are not a concern.

8. Do not use ORDER BY RAND() – Replace with a more efficient random‑row selection, e.g.:

SELECT id FROM `dynamic` t1 JOIN (SELECT RAND() * (SELECT MAX(id) FROM `dynamic`) AS nid) t2 ON t1.id > t2.nid LIMIT 1000;

9. Distinguish IN vs. EXISTS and NOT IN vs. NOT EXISTS – IN drives the subquery first, while EXISTS drives the outer table; choose based on table sizes. Prefer NOT EXISTS over NOT IN for correctness and performance.

SELECT colname … FROM A LEFT JOIN B ON a.id = b.id WHERE b.id IS NULL;

10. Use efficient pagination – Instead of LIMIT offset, count, remember the last seen primary‑key value and query: SELECT id, name FROM product WHERE id > 866612 LIMIT 20; 11. Segment queries for large time ranges – Split a massive scan into smaller time‑based chunks and merge results in application code.

12. Avoid NULL checks in WHERE clauses – They cause full table scans; rewrite logic to eliminate IS NULL where possible.

13. Do not use leading‑wildcard LIKE patterns – Patterns like LIKE '%name%' bypass indexes; use LIKE 'name%' or full‑text indexes instead.

ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

SELECT id, fnum, fdst FROM dynamic_201606 WHERE MATCH(user_name) AGAINST('zhangsan' IN BOOLEAN MODE);

14. Avoid expressions on indexed columns in WHERE – Rewrite arithmetic expressions so the column appears alone, e.g. replace age*2=36 with age=18.

SELECT user_id, user_project FROM user_base WHERE age = 36/2;

15. Prevent implicit type conversion – Ensure the data types of columns and parameters match to avoid hidden conversion costs.

16. Follow the left‑most prefix rule for composite indexes – Indexes are used only from the first column onward; place the most selective columns first.

17. Use FORCE INDEX when the optimizer chooses a sub‑optimal index – Explicitly tell MySQL which index to use.

18. Be careful with range queries on composite indexes – A range condition on one column disables index usage for subsequent columns.

19. Optimize JOINs – Prefer INNER JOIN when possible; understand driver table selection; use STRAIGHT_JOIN to force join order in special cases; and consider rewriting full joins with UNION ALL if needed.

These nineteen tips aim to help developers write faster, more efficient MySQL queries.