19 Essential MySQL Optimization Techniques Every Developer Should Know

This article lists 19 commonly used MySQL optimization methods, covering query planning, index usage, query rewriting, pagination, join strategies, and other best‑practice techniques.

1. Use EXPLAIN to Inspect Execution Plans

Run EXPLAIN on your queries and focus on the following columns:

type : connection type; aim for range or better, avoid all .

key : index name used; NULL means no index, consider forcing an index.

key_len : length of the index.

rows : estimated rows scanned.

extra : additional info such as Using filesort , Using temporary .

2. Limit the Number of Values in IN Clauses

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

3. Specify Columns in SELECT Statements

Avoid SELECT * to reduce CPU, I/O, memory, and network usage, and to enable covering indexes.

4. Use LIMIT 1 When Only One Row Is Needed

This helps the optimizer choose the const type for the type column.

5. Minimize Sorting When the Sort Column Lacks an Index

Reduce or eliminate ORDER BY on non‑indexed columns.

6. Avoid OR on Non‑Indexed Columns

If either side of an OR condition lacks an index, the query may skip index usage. Consider rewriting with UNION ALL or UNION when appropriate.

7. Prefer UNION ALL Over UNION

UNION

performs a distinct operation, adding sorting and CPU cost. Use UNION ALL when duplicate rows are not a concern.

8. Avoid ORDER BY RAND()

Original query: 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 BETWEEN IN and EXISTS / NOT IN and NOT EXISTS

Example using IN: select * from 表A where id in (select id from 表B); Equivalent using EXISTS:

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

IN is suitable when the outer table is large and the inner table is small; EXISTS works better when the outer table is small and the inner table is large.

For NOT IN, prefer NOT EXISTS to avoid logical pitfalls. Example replacement:

select colname … from A表 left join B表 on a.id = b.id where b.id is null;

10. Use Efficient Pagination

Naïve pagination: select id,name from product limit 866613, 20; Optimized pagination using the last seen ID:

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

11. Segment Large Queries

When a time‑range query scans millions of rows, split the range into smaller segments, query each segment separately, and merge the results in application code.

12. Avoid NULL Checks in WHERE Clauses

Testing for NULL forces a full table scan because the optimizer cannot use an index.

13. Do Not Use Leading % in LIKE Patterns

Patterns like LIKE '%name' or LIKE '%name%' disable index usage. Use a trailing % instead (e.g., LIKE 'name%'). For full‑text search, create a full‑text index:

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

Query with the full‑text index:

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

14. Avoid Expressions on Indexed Columns in WHERE

Bad: select user_id,user_project from user_base where age*2=36; Good:

select user_id,user_project from user_base where age=36/2;

15. Prevent Implicit Type Conversions

Ensure that column types match the types of supplied parameters to avoid hidden conversion costs.

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

When an index covers (id, name, school), queries can use the index only if they filter on the left‑most columns in order (e.g., id or id+name). Do not start with name or school.

17. Use FORCE INDEX When Necessary

If the optimizer chooses a sub‑optimal index, add FORCE INDEX (index_name) to compel the desired index.

18. Be Careful with Range Conditions on Composite Indexes

Applying a range condition (e.g., BETWEEN, >, <) on the first column of a composite index disables the use of subsequent columns in that index.

19. Optimize JOINs

Key points:

LEFT JOIN uses the left table as the driver; INNER JOIN lets MySQL pick the smaller table.

Prefer INNER JOIN over LEFT JOIN when possible.

Place indexed columns of the driver table in the ON clause.

Use a small table to drive a large table.

Consider STRAIGHT_JOIN to force join order when the optimizer chooses a costly plan.

Example to emulate a FULL JOIN (MySQL lacks native FULL JOIN):

select * from A left join B on B.name = A.name where B.name is null union all select * from B;

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