How to Diagnose and Optimize MySQL Slow Queries: Practical Tips and EXPLAIN Insights

Slow queries can quickly exhaust MySQL resources, fill the thread pool, drive CPU to 100%, and even cause system crashes, as illustrated by a real production incident where a 2‑3 second query without an index saturated the database.

Enabling the Slow Query Log

MySQL records queries that exceed the long_query_time threshold (default 10 s) in the slow query log, which is disabled by default. Follow these steps to enable it:

Check the current status: show variables like '%slow_query_log%'; Enable the log temporarily (until MySQL restarts): set global slow_query_log='ON'; Set the execution‑time threshold, e.g., 1 second: set global long_query_time=1; Find the log file location: show variables like '%slow_query_log_file%'; Restart MySQL or reconnect to apply the changes permanently.

Understanding Slow Query Log Entries

Timestamp of the query.

User, host, and thread ID.

Execution time, lock time, rows examined, rows sent.

SQL execution timestamp.

The actual SQL statement.

Analyzing Queries with EXPLAIN

EXPLAIN simulates the optimizer’s execution plan, revealing how MySQL accesses tables. Example on a table with 1.37 million rows:

SELECT * FROM vio_basic_domain_info WHERE app_name LIKE '%翻译%';

Execution time: 1.185 s, full table scan (type ALL), 1.37 M rows examined.

EXPLAIN SELECT * FROM vio_basic_domain_info WHERE app_name LIKE '%翻译%';
+----+-------------+-----------------------+------+-------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table                 | type | possible_keys | key  | key_len | ref | rows   | filtered | Extra       |
+----+-------------+-----------------------+------+-------+---------------+------+---------+------+---------+----------+-------------+
| 1  | SIMPLE      | vio_basic_domain_info | ALL  | NULL          | NULL | NULL    | NULL| 1377809| 11.11   | Using where |
+----+-------------+-----------------------+------+-------+---------------+------+---------+------+---------+----------+-------------+

When an index is used:

SELECT * FROM vio_basic_domain_info WHERE app_name LIKE '翻译%';

Execution time drops to 0.156 s, rows examined: 141, type range, key idx_app_name.

Optimizing Pagination Queries

Large LIMIT M, N offsets force MySQL to scan and discard M rows, leading to severe slowdown. Three practical solutions are presented:

Covering index scan: Select only indexed columns (e.g., app_name, createTime) and let MySQL use the index directly.

SELECT app_name, createTime FROM vio_basic_domain_info LIMIT 1000000,10;

Offset optimization using a subquery: Find the start ID first, then query by primary key.

SELECT * FROM vio_basic_domain_info WHERE id >= (
    SELECT id FROM vio_basic_domain_info ORDER BY id LIMIT 1000000,1
) LIMIT 10;

Delayed join (derived table): Retrieve the IDs with a limit, then join back to the full table.

SELECT * FROM vio_basic_domain_info INNER JOIN (
    SELECT id FROM vio_basic_domain_info ORDER BY id LIMIT 1000000,10
) AS t USING(id);

Common Index Pitfalls and Fixes

Avoid leading wildcards in LIKE '%text%' – they prevent index usage. Prefer LIKE 'text%' or full‑text search.

Replace NOT IN with NOT EXISTS to keep indexes.

Replace OR with UNION when possible.

Avoid functions or expressions on indexed columns in the WHERE clause; move them to the right side.

-- Bad: SELECT * FROM t WHERE score/10 = 9;
-- Good: SELECT * FROM t WHERE score = 90;

Do not use WHERE 1=1 as a placeholder; it disables index usage.

Do not use <> or != on indexed columns; consider redesigning the index.

When querying a composite index, always include the leftmost column(s) to satisfy the “leftmost prefix” rule.

Beware of implicit type conversion (e.g., comparing a VARCHAR column to a numeric literal) which can force a full scan.

Conclusion

By enabling the slow query log, using EXPLAIN to dissect execution plans, and applying the presented indexing and pagination strategies, developers can dramatically reduce query latency, prevent resource exhaustion, and maintain a stable MySQL service.