Essential MySQL Optimization Tips: From EXPLAIN to Index Strategies

MySQL Performance Optimization Checklist

1. Use EXPLAIN to Inspect Execution Plans

Run EXPLAIN on your queries and focus on the following columns (marked 1‑5 in the example screenshot):

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

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

key_len : length of the index used.

rows : estimated number of rows scanned.

extra : additional details such as Using filesort or Using temporary.

2. Keep IN Lists Small

MySQL stores IN values in a sorted array; large lists increase overhead. Use BETWEEN for continuous ranges and replace IN with joins when possible.

3. Specify Columns Instead of *

Using SELECT * adds unnecessary CPU, I/O, memory, and network load, and prevents index‑only (covering) scans. List required columns explicitly.

4. Use LIMIT 1 When Only One Row Is Needed

This encourages the optimizer to choose the const access type.

5. Minimize Sorting When Indexes Aren’t Used

Avoid ordering by columns that lack supporting indexes.

6. Reduce OR Conditions

When one side of an OR is non‑indexed, the whole query may skip indexes. Consider rewriting with UNION ALL or other logic.

7. Prefer UNION ALL Over UNION

UNION

forces a distinct‑value sort, adding CPU and I/O. Use UNION ALL when duplicate rows are impossible.

8. Avoid ORDER BY RAND()

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

Replace with a join that generates a random value once and filters by it:

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)

IN loads the subquery result into a sorted array; EXISTS drives the outer table first. Use IN when the outer table is large and the inner table is small, and EXISTS for the opposite.

NOT IN can be logically incorrect; prefer NOT EXISTS.

Example replacement:

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

10. Use Efficient Pagination

Traditional LIMIT offset, count slows down as offset grows. Instead, remember the last seen id and query:

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

11. Segment Large Scans (Chunked Queries)

When a query scans millions of rows, split the time range into smaller chunks and process them sequentially, merging results in the application.

12. Avoid Functions on Indexed Columns in WHERE

Expressions like age*2 = 36 prevent index usage. Rewrite as age = 36/2.

13. Prevent Implicit Type Conversions

Ensure the data type of column and literal match; otherwise MySQL may cast and bypass indexes.

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

For an index on (id, name, school), queries can use the index if they filter on id alone or id, name, but not on name or school alone.

15. Force a Specific Index When Needed

Use FORCE INDEX (index_name) to override the optimizer’s choice.

16. Be Careful with Range Conditions on Composite Indexes

Applying BETWEEN, >, or < on a leading column disables use of subsequent index columns.

17. Optimize JOIN Order

MySQL chooses the smaller table as the driver for INNER JOIN. LEFT JOIN always drives from the left table. Prefer INNER JOIN when possible.

LEFT JOIN – left table is driver.

INNER JOIN – MySQL picks the smaller table.

RIGHT JOIN – right table is driver.

MySQL does not support FULL JOIN; emulate it with LEFT JOIN + UNION ALL.

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

18. Use STRAIGHT_JOIN for Forced Join Order

When GROUP BY, ORDER BY, or filesort/temporary tables appear, STRAIGHT_JOIN forces the left table to be the driver. It works only with inner joins.

select ... from A STRAIGHT_JOIN B on ... where ...;

Using these techniques can reduce query execution time by up to threefold.

These are the primary MySQL optimization strategies; many more exist, and developers are encouraged to experiment further.