Master MySQL Slow Query Optimization: Explain, Indexes, and Best Practices

1. Analyzing Slow Queries

Common methods for diagnosing slow MySQL queries include EXPLAIN, SHOW PROFILE, and tracing the optimizer; this guide focuses on the EXPLAIN approach.

2. EXPLAIN Parameters Explained

The basic syntax is EXPLAIN <SQL statement>. For example, to evaluate SELECT * FROM table1 WHERE b=500;, run EXPLAIN SELECT * FROM table1 WHERE b=500; and examine the output.

The result contains many columns; the most important ones are highlighted in the accompanying screenshots (select_type, type, Extra, etc.). The type column’s values indicate performance from best to worst.

3. Common SQL Optimization Techniques

(1) Use Indexes

Indexes are essential for large datasets. MySQL’s B‑tree can locate rows in 2‑4 levels even with billions of rows. However, many queries still bypass indexes. Typical reasons and fixes include:

Functions on columns: replace SELECT * FROM table1 WHERE DATE(c)='2020-08-20'; with a range condition on c.

Implicit type conversion: compare varchar columns with strings, e.g., SELECT * FROM table1 WHERE a='1000';.

LIKE patterns starting with %: SELECT * FROM table1 WHERE a LIKE '%1111%'; does not use an index; use LIKE '1111%' when possible.

Wide range queries: narrow the range or split into multiple queries.

Arithmetic in WHERE: rewrite WHERE b-1=1000 as WHERE b=1001.

OR conditions: replace with UNION when each side can use an index.

(2) Pagination Optimization

For queries like SELECT * FROM table1 ORDER BY a LIMIT 99000,10; that skip indexes, rewrite using a subquery or a join to fetch only the needed IDs first.

(3) Join Optimization

Prefer Nested‑Loop Join (NLJ) over Block Nested‑Loop Join (BNL) by adding indexes on join columns.

(4) ORDER BY and GROUP BY Optimization

Add indexes on sorting columns.

Create composite indexes for multi‑column sorts or for queries that filter then sort.

Follow the left‑most prefix rule for composite indexes.

Remove unnecessary columns from the SELECT list.

When MySQL cannot use an index, it falls back to Filesort, which is slower.

For more details, see the original article: https://www.cnblogs.com/lmz-blogs/p/13655970.html