Why and How to Optimize Slow SQL Queries in MySQL

Why Manage Slow SQL

Each SQL statement consumes I/O resources, and its execution time directly determines how long those resources are occupied. If a business requires 100 queries per second but 10 of them are slow, the throughput drops to 90, causing queuing and affecting overall service.

Priorities for Optimization

Master‑slave architecture: read‑write separation reduces replication delay.

Prioritize SQL statements with high execution frequency.

Prioritize tables that experience concentrated concurrent access.

MySQL Execution Principle

Understanding MySQL's execution flow helps in optimizing slow queries.

Parsing: lexical analysis → syntax analysis → logical plan → query optimization → physical execution plan; the cache is checked before proceeding to mysql_execute_command.

Execution: verify user and table permissions → acquire shared read lock → fetch data into query_cache → release lock.

How to Detect Slow Queries

-- Set slow‑query threshold for the current session only
set long_query_time=1;
-- Enable slow‑query logging globally
set global slow_query_log='ON';
show variables like "%slow%";
show status like "%slow%";
show variables like "long_query_time";

You can also configure the my.ini file:

# Enable slow‑query log
slow_query_log = on
# Log file path (absolute path required on Windows)
slow_query_log_file = D:/mysql5.5.16/data/slow.log
# Threshold in seconds
long_query_time = 2
# Log queries that do not use indexes
log-queries-not-using-indexes

Enabling the slow‑query log adds CPU and I/O overhead, so it is recommended to enable it intermittently for monitoring.

Slow‑Query Analysis Example

# Time: 150530 15:30:58 -- query timestamp
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 1.134065  Lock_time: 0.000000  Rows_sent: 8  Rows_examined: 4000000

When many slow queries appear, manual analysis becomes impractical; use mysqldumpslow:

mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log   # Top 10 most frequent slow queries
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log   # Top 3 slowest queries by execution time
mysqldumpslow -s t -t 10 -g "left join" /database/mysql/slow-log   # Top 10 slow queries containing left join
mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqldslow.log   # Top 10 queries scanning most rows

Common SQL Optimizations

1. Avoid Subqueries

SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');

In MySQL 5.5 the optimizer scans the outer table first, which is slow for large datasets. MySQL 5.6 rewrites it to a join:

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

Subqueries are not effective for UPDATE/DELETE, so avoid them in production.

2. Avoid Functions on Indexed Columns

SELECT * FROM t WHERE YEAR(d) >= 2016;

Even if d is indexed, the function prevents index usage. Rewrite as:

SELECT * FROM t WHERE d >= '2016-01-01';

3. Use IN Instead of OR

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

Using IN (10,20,30) reduces index lookups from three to one.

SELECT * FROM t WHERE LOC_ID IN (10,20,30);

4. Avoid Leading Wildcards in LIKE

SELECT * FROM t WHERE name LIKE '%de%';

Rewrite to a right‑anchored pattern to enable index usage:

SELECT * FROM t WHERE name LIKE 'de%';

5. Add LIMIT M,N

Restrict the number of rows returned to reduce workload.

6. Ensure Consistent Data Types

SELECT * FROM t WHERE id = '19';   -- string literal forces a conversion
SELECT * FROM t WHERE id = 19;     -- numeric literal uses the index directly

7. Disable Sorting in GROUP BY

SELECT goods_id, COUNT(*) FROM t GROUP BY goods_id ORDER BY NULL;

Adding ORDER BY NULL prevents MySQL from sorting the groups, saving resources.

8. Remove Unnecessary ORDER BY

Eliminate ORDER BY clauses that do not affect the result set.

Summary

Keep SQL statements concise and remove non‑essential parts.

Optimize indexes so every query can leverage them.

If a table becomes a bottleneck, split it (keep each under ~10 million rows).

If a single database is a bottleneck, shard it and apply read‑write separation.

If hardware limits are reached, add more database nodes.