19 Essential MySQL Optimization Techniques Every Developer Should Know

MySQL performance can be dramatically improved by applying a series of concrete techniques. Below is a detailed list of 19 optimization methods with explanations, examples, and sample SQL code.

1. Use EXPLAIN

Run EXPLAIN to view the execution plan. Pay attention to the type column (aim for range or better, avoid all ), key (index used), key_len (index length), rows (estimated rows scanned), and extra (e.g., Using filesort, Using temporary).

2. Keep IN Lists Small

MySQL stores IN constants in a sorted array; large lists increase overhead. Prefer BETWEEN for continuous ranges or replace IN with a join.

3. Specify Columns Instead of SELECT *

Using SELECT * adds unnecessary CPU, I/O, memory, and network load, and prevents index‑only (covering) scans. Always list required columns.

4. Use LIMIT 1 When Only One Row Is Needed

This forces the optimizer to choose a const access type for the type column.

5. Avoid Sorting When the Sort Column Is Not Indexed

Minimize ORDER BY on non‑indexed columns.

6. Reduce Use of OR When the Involved Columns Lack Indexes

OR can force a full table scan. Consider UNION ALL or UNION as alternatives.

7. Prefer UNION ALL Over UNION

UNION

removes duplicates, requiring sorting and extra CPU. Use UNION ALL when duplicate rows are impossible.

8. Avoid ORDER BY RAND()

Random ordering is expensive. Replace with a deterministic method, e.g., selecting a random id range.

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

IN executes the subquery first, then drives the outer query; EXISTS drives the outer table first. Use IN when the outer table is large and the inner table is small; use EXISTS for the opposite.

Example converting IN to EXISTS: select * from A where id in (select id from B); becomes

select * from A where exists (select * from B where B.id = A.id);

10. Efficient Pagination

Standard LIMIT offset, count slows as offset grows. Use the last retrieved id as a cursor:

select id, name from product where id > 866612 limit 20;

11. Segmented Queries

For massive scans, split the time range or ID range into smaller chunks and process them iteratively.

12. Avoid NULL Checks in WHERE

Expressions like WHERE col IS NULL can prevent index usage, leading to full scans.

13. Avoid Leading % in LIKE

Patterns like LIKE '%name' or LIKE '%name%' disable index usage. Use LIKE 'name%' instead, or employ full‑text indexes for flexible matching.

14. Do Not Apply Functions to Indexed Columns in WHERE

Arithmetic on columns (e.g., WHERE age*2 = 36) forces a table scan. Rewrite as WHERE age = 18.

15. Prevent Implicit Type Conversion

Ensure the data types of columns and literals match to avoid hidden conversions that bypass indexes.

16. Follow the Left‑most Prefix Rule for Composite Indexes

Only the leading columns of a multi‑column index can be used. Order index columns by query frequency.

17. Use FORCE INDEX When Needed

If the optimizer picks a suboptimal index, add FORCE INDEX (index_name) to direct it.

18. Beware of Range Conditions on Composite Indexes

Applying a range condition (e.g., BETWEEN, >, <) on one column prevents use of subsequent columns in the same index.

19. Join Optimization

Prefer INNER JOIN over LEFT JOIN when possible; the optimizer will choose the smaller table as the driver. Use STRAIGHT_JOIN to force join order in special cases. For missing FULL JOIN support, emulate with LEFT JOIN ... WHERE B.id IS NULL UNION ALL SELECT * FROM B.

Additional tips include creating appropriate full‑text indexes:

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

and querying with:

select id, fnum, fdst from dynamic_201606 where match(user_name) against('zhangsan' in boolean mode);

Before adding full‑text indexes, consult a DBA to ensure they are allowed.