Master MySQL Slow Query Optimization: EXPLAIN Tips & Practical SQL Tuning

While developing a CRM system I discovered many slow SQL statements in the slow‑query log, so I performed a series of optimizations and compiled a checklist.

1. Analyzing slow queries

Three common methods exist:

EXPLAIN

,

SHOW PROFILE

, and

TRACE

. This article focuses on using

EXPLAIN

to analyze slow SQL.

2. EXPLAIN parameters

Syntax:

EXPLAIN <SQL>

. Example:

EXPLAIN SELECT * FROM table1 WHERE b=500;

The result contains many fields; the most important ones are

select_type

,

type

, and

Extra

. Their possible values are shown in the tables below.

3. Common SQL optimization techniques

(1) Use indexes

Indexes speed up queries, but they are not used in certain cases:

Functions on columns – rewrite

SELECT * FROM table1 WHERE DATE(c)='2020-08-20'

as

SELECT * FROM table1 WHERE c>='2020-08-20 00:00:00' AND c<='2020-08-20 23:59:59'

.

Implicit type conversion – compare a varchar column with a string literal, e.g.,

SELECT * FROM table1 WHERE a='1000'

instead of

SELECT * FROM table1 WHERE a=1000

.

Leading wildcard LIKE –

LIKE '%1111%'

cannot use an index; use

LIKE '1111%'

when possible.

Range queries that cover too many rows – split a large range into smaller sub‑ranges.

Arithmetic in WHERE – move calculations out of the condition, e.g.,

SELECT * FROM table1 WHERE b=1000+1

instead of

SELECT * FROM table1 WHERE b-1=1000

.

OR conditions – replace with

UNION

when each side can use an index.

(2) Pagination optimization

Large offsets cause full index scans. Use a sub‑query to fetch primary keys first:

SELECT f.* FROM table1 f INNER JOIN (SELECT id FROM table1 ORDER BY a LIMIT 99000,10) g ON f.id=g.id;

or

SELECT * FROM table1 WHERE id>=(SELECT id FROM table1 ORDER BY a LIMIT 99000,1) LIMIT 10;

(3) Join optimization

Prefer Nested‑Loop Join (NLJ) by adding indexes on join columns; avoid Block Nested‑Loop Join (BNL) which occurs when join columns lack indexes.

(4) ORDER BY / GROUP BY optimization

Add indexes on sorting columns.

Use composite indexes that satisfy the leftmost‑prefix rule.

Combine equality conditions with sorting columns in a single index.

Select only needed columns instead of

*

.

When MySQL can use an ordered index it adds

Using index

in the

Extra

column; otherwise it resorts to

Using filesort

.