19 Essential MySQL Optimization Techniques Every Developer Should Know

This article presents 19 commonly used MySQL optimization methods, each explained with purpose, best‑practice advice, and example SQL statements.

1. EXPLAIN

Use EXPLAIN to view the execution plan of a query and identify inefficient operations.

2. Keep IN Lists Small

Large IN lists increase overhead; prefer BETWEEN for continuous ranges or replace with joins.

3. Specify Columns in SELECT

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

4. Use LIMIT 1 When Only One Row Is Needed

This helps the optimizer choose a const access type.

5. Minimize Sorting When Index Is Not Used

Avoid ORDER BY on columns without indexes.

6. Reduce OR Usage When Possible

OR can prevent index usage; consider UNION ALL or rewriting the condition.

7. Prefer UNION ALL Over UNION

UNION removes duplicates and requires sorting; UNION ALL skips this step when duplicate rows are not a concern.

8. Avoid ORDER BY RAND()

Replace random ordering with a more efficient method, 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 outer table first; EXISTS drives the inner table first. Use NOT EXISTS instead of NOT IN for better performance and correctness.

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

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

10. Use Efficient Pagination

Instead of offset‑based LIMIT, query the next page by the last seen id:

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

11. Segmented Queries

For very large scans, split the time range into smaller segments and query each segment separately.

12. Avoid NULL Checks in WHERE

NULL predicates can force full table scans.

13. Do Not Use Leading % in LIKE

LIKE '%name' disables index usage; use a trailing % instead.

14. Avoid Expressions on Indexed Columns in WHERE

Rewrite arithmetic expressions to compare constants:

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 conversions.

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

Order index columns by query frequency; only the leftmost columns can be used.

17. Use FORCE INDEX When Needed

Force the optimizer to use a specific index if it chooses a sub‑optimal one.

18. Be Careful with Range Queries

Range conditions (BETWEEN, >, <) on a composite index stop the use of subsequent index columns.

19. Optimize JOINs

Prefer INNER JOIN; let MySQL pick the smaller table as the driver. Use LEFT JOIN only when necessary and consider STRAIGHT_JOIN to control join order.

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

These techniques collectively help reduce I/O, CPU usage, and query latency.