Master MySQL Slow Query Optimization: Proven Techniques & Real-World Cases

1. Slow SQL Optimization Approach

MySQL does not enable the slow query log by default, so you must turn it on manually. Use SHOW VARIABLES LIKE 'slow_query_log%'; to verify that slow_query_log is ON and to locate the log file ( slow_query_log_file ). The long_query_time variable defines the threshold (in seconds) for logging a query; check it with SHOW VARIABLES LIKE 'long_query_time';. Once enabled, the log helps you identify low‑efficiency statements for deeper analysis.

2. Using EXPLAIN to Analyze Execution Plans

After locating a slow statement, run EXPLAIN SELECT ... to view the optimizer's execution plan. Pay special attention to the columns type , rows , filtered , extra , and key :

type (join type) indicates how indexes are used, ordered from best to worst: system → const → eq_ref → ref → ref_or_null → index_merge → unique_subquery → index_subquery → range → index → ALL.

rows shows the optimizer's estimate of how many rows must be read.

filtered is a percentage of rows that satisfy the condition after filtering.

extra may contain values such as Using filesort, Using index, Using temporary, Using where, or Using index condition, each indicating specific processing steps.

key displays the actual index chosen (often examined together with possible_keys).

Understanding these fields lets you pinpoint why a query is slow and which part of the plan needs improvement.

3. Profiling to Measure Real Execution Time

EXPLAIN shows estimated costs; to see actual resource consumption, enable profiling with SET profiling = ON;. After running the target statements, SHOW PROFILES; lists recent queries (default history size is 15). Use SHOW PROFILE FOR QUERY n; or SHOW PROFILE ALL FOR QUERY n; to view detailed CPU, I/O, and memory usage for a specific query.

4. Optimizer Trace for Detailed Plan

Profiler shows time but not the optimizer's decision process. Enable it with SET optimizer_trace="enabled=on";, execute the query, then retrieve the trace via SELECT * FROM information_schema.optimizer_trace;. The trace is divided into three stages: join_preparation , join_optimization , and join_execution , revealing how the optimizer builds and chooses the execution tree.

5. Common Problem Patterns and Fixes

The article presents ten typical slow‑SQL scenarios and corresponding solutions.

Case 1 – Implicit Conversion : Querying a VARCHAR column with a numeric literal forces MySQL to convert types, causing index loss. Enclose the value in quotes to preserve index usage.

CREATE TABLE user (
  id INT(11) NOT NULL AUTO_INCREMENT,
  userId VARCHAR(32) NOT NULL,
  age VARCHAR(16) NOT NULL,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_userid (userId) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Bad: index ignored
SELECT * FROM user WHERE userId = 12345;

-- Good: index used
SELECT * FROM user WHERE userId = '12345';

Case 2 – Left‑most Prefix : In a composite index (a,b,c), only queries that start with the leftmost column can use the index. Querying only name in an index (user_id, name) fails; querying user_id works.

CREATE TABLE user (
  id INT AUTO_INCREMENT,
  user_id VARCHAR(32) NOT NULL,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_userid_name (user_id, name) USING BTREE
) ENGINE=InnoDB;

-- Index not used (violates leftmost rule)
EXPLAIN SELECT * FROM user WHERE name = 'Alice';

-- Index used
EXPLAIN SELECT * FROM user WHERE user_id = 'U001';

Case 3 – Deep Pagination : LIMIT offset, n scans offset + n rows, then discards the first offset. This leads to large scans and many back‑table lookups. Optimize by using a bookmark (e.g., last seen id) or a subquery that selects primary keys first.

-- Bookmark method
SELECT id, name, balance FROM account WHERE id > 100000 LIMIT 10;

Case 4 – Too Many IN Elements : An IN list larger than the optimizer's eq_range_index_dive_limit (default 200) prevents accurate cost estimation, often causing full scans. Split the list into batches of ≤200 elements.

SELECT user_id, name FROM user WHERE user_id IN (1,2,3,...,200);

Case 5 – ORDER BY Filesort : When the sort cannot be satisfied by an index, MySQL performs a file sort, which may spill to disk if the data exceeds sort_buffer_size. Create an index that matches the ORDER BY columns or increase sort_buffer_size.

Case 6 – IS NULL / IS NOT NULL : Separate indexes on columns used with IS NULL or IS NOT NULL may be ignored when combined with other predicates (e.g., OR). Ensure the optimizer can use the index or rewrite the query.

Case 7 – <> or <> Operators : Inequality conditions often prevent index usage because the optimizer expects to scan many rows. Prefer range conditions or redesign the schema if possible.

Case 8 – Mismatched Character Sets : Joining tables with different column collations (e.g., utf8mb4 vs utf8) forces a full scan. Align the character sets to enable index usage.

Case 9 – GROUP BY Using Temporary Table and Filesort : GROUP BY creates an internal temporary table and may sort the result, leading to extra I/O. Optimize by adding an index on the GROUP BY columns, using ORDER BY NULL, or employing SQL_BIG_RESULT to force a disk‑based temporary table when appropriate.

Case 10 – DELETE with IN Subquery : MySQL can transform SELECT ... IN (subquery) into a semi‑join, allowing index usage, but it does not apply the same rewrite for DELETE. Be aware that such DELETE statements may perform full scans.

By following these steps—enabling diagnostics, interpreting EXPLAIN, profiling, tracing the optimizer, and addressing the listed patterns—you can systematically reduce MySQL query latency.

References

慢SQL优化一点小思路 – https://juejin.cn/post/7048974570228809741#heading-7

SQL优化万能公式：5 大步骤 + 10 个案例 – https://developer.aliyun.com/article/980780