Essential MySQL Optimization Tips: From EXPLAIN to Advanced Index Strategies

1. EXPLAIN

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

Key columns to focus on:

type : join type; aim for range level and avoid all.

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

key_len : length of the index.

rows : estimated number of rows scanned.

extra : additional info, watch for values like Using filesort or Using temporary.

2. Limit the number of values in IN clauses

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

3. Specify column names in SELECT

Avoid SELECT * as it adds unnecessary CPU, I/O, memory, and network overhead, reduces the chance of using covering indexes, and requires updates when the table schema changes. List needed columns explicitly.

4. Use LIMIT 1 when only one row is required

This helps the type column in EXPLAIN reach the const level.

5. Minimize sorting when the sort column is not indexed

Avoid unnecessary sorting operations that cannot use an index.

6. Reduce use of OR when other fields lack indexes

If any side of an OR condition involves a non‑indexed field, the query may skip indexes. Consider replacing OR with UNION ALL when appropriate.

7. Prefer UNION ALL over UNION

UNION

removes duplicates, requiring sorting and extra CPU; UNION ALL skips this step but should only be used when duplicate rows are not possible.

8. Avoid ORDER BY RAND()

Original inefficient query:

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

Optimized version using a random value join:

select id from `table_name` t1 join (select rand() * (select max(id) from `table_name`) as nid) t2 on t1.id > t2.nid limit 1000;

9. Distinguish IN vs EXISTS and NOT IN vs NOT EXISTS

Example using IN:

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

Equivalent using EXISTS:

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

IN drives the outer table first; EXISTS drives the inner table first. Use IN when the outer table is large and the inner table is small; use EXISTS for the opposite.

For NOT IN vs NOT EXISTS, NOT EXISTS is generally preferred for both performance and logical correctness.

Efficient replacement for NOT IN:

select colname … from TableA left join TableB on TableA.id = TableB.id where TableB.id is null;

10. Use efficient pagination

Standard limit pagination becomes slower as the table grows:

select id, name from table_name limit 866613, 20;

Better approach: use the maximum id from the previous page as the starting point:

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

11. Segment large queries

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

12. Avoid NULL checks in WHERE clauses

Testing for NULL can cause the optimizer to skip indexes and perform full table scans.

13. Do not use leading wildcard in LIKE

Patterns like LIKE "%name" or LIKE "%name%" prevent index usage. Use LIKE "name%" instead.

14. Avoid expressions on indexed columns in WHERE

Example that disables index usage:

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

Rewrite as:

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

15. Prevent implicit type conversion

When column types and parameter types differ, MySQL may convert types, causing index loss. Ensure parameter types match column types.

16. Follow the left‑most 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 without the leading id.

17. Use FORCE INDEX when necessary

If the optimizer chooses an index you do not want, add FORCE INDEX(index_name) to compel the desired index.

18. Be cautious with range queries on composite indexes

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

19. JOIN optimization

Join type influences which table drives the query:

LEFT JOIN – left table is the driver.

INNER JOIN – MySQL automatically picks the smaller table as the driver.

RIGHT JOIN – right table is the driver.

MySQL does not support FULL JOIN; emulate it with LEFT JOIN, UNION ALL, and a second SELECT.

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 when possible, as it allows the optimizer to choose the most efficient driver table.

20. Use STRAIGHT_JOIN for forced join order

When GROUP BY, ORDER BY, or other operations cause the optimizer to choose a sub‑optimal join order, prepend STRAIGHT_JOIN to enforce the left‑to‑right table order (only works with INNER JOIN).

Using STRAIGHT_JOIN can reduce execution time by up to three times in certain scenarios.