19 Common MySQL Optimization Techniques

MySQL performance can be significantly improved by applying a series of optimization techniques. Below are nineteen commonly used methods.

1. EXPLAIN command – Use EXPLAIN to view the execution plan and focus on the type , key , key_len , rows , and extra columns.

2. Limit values in IN clause – Avoid large IN lists; replace with BETWEEN for continuous ranges or use joins.

3. Specify columns in SELECT – Do not use SELECT * to reduce unnecessary CPU, I/O, memory, and network overhead.

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 the use of ORDER BY on non‑indexed columns.

6. Reduce use of OR when fields are not indexed – Prefer UNION ALL or UNION to avoid full table scans.

7. Prefer UNION ALL over UNION – UNION incurs extra sorting for deduplication; UNION ALL is faster when duplicates are impossible.

8. Avoid ORDER BY RAND() – Replace with a more efficient random selection, e.g.:

SELECT id FROM `dynamic` ORDER BY RAND() LIMIT 1000;

Optimized version:

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 outer table; EXISTS drives the inner table. Use EXISTS when the outer table is small.

Example of NOT EXISTS replacement:

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 on large tables, remember the last retrieved id and query:

SELECT id, name FROM product WHERE id > 866612 LIMIT 20;

11. Segmented queries – Split large time‑range queries into smaller batches to reduce scanned rows.

12. Avoid NULL checks in WHERE – They can prevent index usage and cause full scans.

13. Avoid leading wildcard in LIKE – Use LIKE 'name%' instead of LIKE '%name%'; for the latter, consider full‑text indexes.

14. Avoid expressions on indexed columns – Rewrite arithmetic in the constant side, e.g.:

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.

16. Follow the left‑most prefix rule for composite indexes – Place the most frequently queried columns first.

17. Use FORCE INDEX when necessary – Override the optimizer’s choice to use a specific index.

18. Be careful with range queries on composite indexes – Conditions like BETWEEN, >, < can invalidate subsequent index columns.

19. Join optimization – Prefer INNER JOIN over LEFT JOIN when possible; let the optimizer choose the smaller table as the driver, or use STRAIGHT_JOIN to force join order when needed.

Additional notes: MySQL does not support FULL JOIN directly; it can be simulated with a LEFT JOIN … UNION ALL. Use FULLTEXT indexes for fuzzy searches, and create them with:

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

When using the full‑text index, query like:

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

Always consult a DBA before creating full‑text indexes and be aware of differences from ordinary indexes.