Essential MySQL SQL Optimization Tips to Speed Up Your Queries

Many developers spend minutes analyzing data only to have queries run for hours; this SQL optimization guide is worth bookmarking.

1. EXPLAIN

Use EXPLAIN to view the execution plan of a MySQL query.

Example:

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

key : index used; NULL means none; can force an index.

key_len : length of the index.

rows : estimated number of rows scanned.

extra : additional info, e.g., Using filesort, Using temporary.

2. Keep IN clause values small

MySQL stores IN constants in a sorted array; many values increase cost. Use BETWEEN for continuous ranges or replace with joins.

select id from table_name where num in(1,2,3)

3. Specify column names

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

4. Use LIMIT 1 when only one row is needed

This helps EXPLAIN show a const type.

5. Minimize sorting when the sort column lacks an index

Try to avoid ORDER BY on non‑indexed columns.

6. Replace OR with UNION ALL when possible

OR can prevent index usage; UNION ALL often yields better performance.

7. Prefer UNION ALL over UNION

UNION

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

8. Avoid ORDER BY RAND()

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

Optimized version:

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 and EXISTS (and NOT IN / NOT EXISTS)

IN drives the outer table when it is large and the inner table is small; EXISTS does the opposite. Use the form that matches the data size.

select * from tableA where id in (select id from tableB);

is equivalent to

select * from tableA where exists (
  select * from tableB where tableB.id = tableA.id
);

10. Efficient pagination

Using LIMIT offset, count becomes slower as the table grows. Instead, remember the maximum id from the previous page and query:

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

11. Segmented queries

When a query scans millions of rows, split the time range into smaller segments and combine results.

12. Avoid NULL checks in WHERE

Testing for NULL forces a full table scan and disables index usage.

13. Do not use leading % in LIKE

Patterns like LIKE "%name" or LIKE "%name%" invalidate indexes. Use a prefix LIKE "name%" or a full‑text index.

14. Avoid expressions on columns in WHERE

Arithmetic in the predicate prevents index use. Rewrite:

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

15. Avoid implicit type conversion

Ensure the data type of the parameter matches the column type to prevent costly conversions.

16. Composite index left‑most rule

An index on (id, name, school) can be used for queries on id alone or id, name, but not for name alone.

17. Use FORCE INDEX when needed

If the optimizer chooses a sub‑optimal index, FORCE INDEX can compel it to use the desired one.

18. Beware of range queries

Conditions like BETWEEN, >, < on a composite index invalidate the use of subsequent index columns.

19. JOIN optimization

Prefer INNER JOIN —MySQL automatically picks the smaller table as the driver. LEFT JOIN forces the left table to be the driver.

Use STRAIGHT_JOIN to force join order when necessary (must be an inner join).

These techniques can reduce query time by up to three times.