19 Essential MySQL Optimization Techniques Every Developer Should Know

This article presents nineteen commonly used MySQL optimization methods, covering query planning, index usage, query rewriting, join strategies, pagination, full‑text search, and other best practices.

1. EXPLAIN

Use EXPLAIN to view the execution plan of a query. Pay attention to the type , key , key_len , rows , and extra columns; aim for a range or better type and avoid ALL .

2. Limit IN List Size

MySQL stores IN‑list constants in a sorted array. Large IN lists increase cost. Prefer BETWEEN for continuous ranges or replace IN with a join.

3. Specify Columns in SELECT

Avoid SELECT * because it adds unnecessary CPU, I/O, memory, and network overhead and prevents use of covering indexes. List required columns explicitly.

4. Use LIMIT 1 When Only One Row Is Needed

This helps the optimizer achieve a const type for the type column.

5. Minimize Sorting When Index Is Not Used

If the ORDER BY column lacks an index, try to avoid sorting.

6. Reduce Use of OR When Unindexed Columns Are Involved

OR can force a full table scan if any side lacks an index. Consider rewriting with UNION ALL or UNION where appropriate.

7. Prefer UNION ALL Over UNION

UNION

removes duplicates, requiring sorting and extra CPU; UNION ALL skips this step when duplicate rows are not a concern.

8. Avoid ORDER BY RAND()

Replace random ordering with a more efficient technique, 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 is suitable when the outer table is large and the inner table is small; EXISTS works better when the outer table is small. NOT EXISTS is generally preferred over NOT IN for both performance and logical correctness.

Example conversion:

SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE B.id = A.id);

10. Use Efficient Pagination

Instead of LIMIT offset, count on large tables, remember the last id from the previous page and query:

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

11. Segmented Queries

When a user selects a very large time range, split the query into smaller segments, process each segment, and merge results to avoid scanning millions of rows.

12. Avoid NULL Checks in WHERE

Testing for NULL can cause the optimizer to skip index usage and perform a full table scan.

13. Avoid Leading Wildcards in LIKE

Patterns like LIKE '%name' or LIKE '%name%' prevent index usage. Use a trailing wildcard instead: LIKE 'name%'.

14. Avoid Expressions on Indexed Columns

Expressions such as age*2 = 36 disable index usage. Rewrite as age = 36/2.

15. Prevent Implicit Type Conversion

Ensure that column types match the types of supplied parameters to avoid costly conversions.

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

When creating a composite index, place the most frequently queried columns first; otherwise the index cannot be used.

17. Use FORCE INDEX When Necessary

If the optimizer chooses a suboptimal index, FORCE INDEX(index_name) can compel it to use the desired one.

18. Beware of Range Conditions on Composite Indexes

Applying a range condition (e.g., BETWEEN, >, <) on a leading column renders subsequent indexed columns ineffective.

19. JOIN Optimization

MySQL lacks a FULL JOIN; emulate it with:

SELECT * FROM A LEFT JOIN B ON B.name = A.name WHERE B.name IS NULL
UNION ALL
SELECT * FROM B;

Prefer INNER JOIN when possible, as MySQL will automatically choose the smaller table as the driver. Use STRAIGHT_JOIN to force join order when the optimizer picks an inefficient plan.

By applying these practices—proper use of EXPLAIN, careful index design, rewriting queries to avoid costly operations, and leveraging MySQL‑specific features—developers can significantly improve query performance and reduce server load.