Master MySQL Optimization: 19 Practical EXPLAIN Tips and Best Practices

1. EXPLAIN

When optimizing MySQL, use EXPLAIN to view the execution plan.

Below is a simple example, with points (1,2,3,4,5) we need to focus on.

type column: join type. A good query should reach at least range level; avoid all.

key column: index name used. If no index, value is NULL. You can force an index. key_len column: index length.

rows column: number of rows scanned (estimated).

extra column: detailed notes. Watch for unfriendly values like Using filesort, Using temporary.

2. Avoid too many values in IN clause

MySQL stores IN constants in a sorted array, but large numbers cause overhead. For consecutive values, use BETWEEN instead of IN, or replace with a join.

3. Always specify column names in SELECT

SELECT * adds unnecessary overhead (CPU, I/O, memory, bandwidth) and prevents use of covering indexes; also requires updates when schema changes. Specify fields explicitly.

4. Use LIMIT 1 when only one row is needed

This helps EXPLAIN's type column become const.

5. Minimize sorting when the order column lacks an index

6. Minimize use of OR when other fields lack indexes

If either side of OR is not indexed, the query may not use indexes. Consider using UNION ALL or UNION (when necessary) instead of OR for better performance.

7. Prefer UNION ALL over UNION

UNION requires deduplication, which involves sorting and extra CPU; UNION ALL is fine when result sets have no duplicates.

8. Avoid ORDER BY RAND()

select id from `dynamic` order by rand() limit 1000;

The above can be optimized to:

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, NOT IN vs NOT EXISTS

select * from TableA where id in (select id from TableB)

Equivalent to:

select * from TableA where exists (select * from TableB where TableB.id = TableA.id)

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

For NOT IN vs NOT EXISTS, prefer NOT EXISTS due to efficiency and logical issues with NOT IN.

Example inefficient query:

select colname … from A where a.id not in (select b.id from B)

Efficient rewrite:

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

10. Use efficient pagination

select id,name from product limit 866613, 20

As data grows, LIMIT offset pagination slows down. Optimize by using the last seen id:

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

11. Segmented queries

When users select large time ranges, scanning millions of rows, split the query into segments and combine results.

12. Avoid NULL checks in WHERE

NULL checks cause the engine to forgo indexes and perform full table scans.

13. Do not use leading % in LIKE

LIKE '%name' or '%name%' disables indexes and triggers full scans. Use LIKE 'name%' instead. For full‑text search, create a FULLTEXT index.

Creating a full‑text index:

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

Using the full‑text index:

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

Note: Consult DBA before creating full‑text indexes and adjust query syntax accordingly.

14. Avoid expressions on columns in WHERE

For example:

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

This prevents index use; rewrite as:

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

15. Avoid implicit type conversion

When column type differs from parameter type, MySQL converts types, preventing index usage. Ensure matching types.

16. Follow leftmost prefix rule for composite indexes

For an index on (id, name, school), you can use id alone or id,name, but not name or school alone. Order index columns by most common query predicates.

17. Use FORCE INDEX when necessary

If the optimizer chooses an undesired index, FORCE INDEX can compel use of a specific index.

18. Beware of range queries on composite indexes

Using range conditions (BETWEEN, >, <) on a composite index can render subsequent index columns ineffective.

19. JOIN optimization

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

MySQL lacks FULL JOIN; emulate with UNION ALL and LEFT JOIN.

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

Prefer INNER JOIN over LEFT JOIN. Use the driver table's indexed columns in ON conditions, and let smaller tables drive larger ones.

Use STRAIGHT_JOIN to force join order when needed (e.g., with GROUP BY or ORDER BY causing filesort or temporary tables). Only for inner joins.