Common MySQL Performance Issues and Their Optimization Techniques

MySQL continued strong popularity growth in 2016, with many applications built on it, yet users often encounter slow response times and high CPU usage. The Alibaba Cloud RDS expert team compiled a list of typical SQL problems and their solutions.

1. LIMIT clause

Pagination queries can become slow when using large offsets. Original query:

SELECT * FROM operation WHERE type = 'SQLStats' AND name = 'SlowLog' ORDER BY create_time LIMIT 1000, 10;

Rewritten to use the maximum value of the previous page:

SELECT * FROM operation WHERE type = 'SQLStats' AND name = 'SlowLog' AND create_time > '2017-03-16 14:00:00' ORDER BY create_time LIMIT 10;

2. Implicit conversion

When a column type does not match the literal type, MySQL converts the column, causing index loss. Example:

SELECT * FROM my_balance b WHERE b.bpn = 14000000123 AND b.isverified IS NULL;

The column bpn is VARCHAR(20) ; MySQL casts it to a number, disabling the index.

3. Update/Delete with sub‑queries

MySQL 5.6’s materialized sub‑query feature does not apply to UPDATE/DELETE, leading to dependent sub‑queries. Original UPDATE:

UPDATE operation o SET status = 'applying' WHERE o.id IN (SELECT id FROM (SELECT o.id, o.status FROM operation o WHERE o.group = 123 AND o.status NOT IN ('done') ORDER BY o.parent, o.id LIMIT 1) t);

Rewritten using JOIN:

UPDATE operation o JOIN (SELECT o.id, o.status FROM operation o WHERE o.group = 123 AND o.status NOT IN ('done') ORDER BY o.parent, o.id LIMIT 1) t ON o.id = t.id SET status = 'applying';

This changes the execution plan from DEPENDENT SUBQUERY to DERIVED , reducing runtime from seconds to milliseconds.

4. Mixed sorting

MySQL cannot use an index for mixed ORDER BY columns. Original query:

SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id ORDER BY a.is_reply ASC, a.appraise_time DESC LIMIT 0, 20;

By separating the two is_reply values with UNION ALL, the plan changes to a full‑table scan replaced by two indexed scans, cutting execution time from 1.58 s to 2 ms.

5. EXISTS clause

EXISTS often results in a dependent sub‑query. Original:

SELECT * FROM my_neighbor n LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id = 'xxx' WHERE n.topic_status < 4 AND EXISTS (SELECT 1 FROM message_info m WHERE n.id = m.neighbor_id AND m.inuser = 'xxx') AND n.topic_type <> 5;

Rewritten as a JOIN eliminates the sub‑query and reduces execution time from 1.93 s to 1 ms.

6. Condition push‑down

Conditions cannot be pushed into complex sub‑queries such as aggregated, LIMIT‑containing, UNION, or scalar sub‑queries. Example shows moving the WHERE target = 'rm‑xxxx' condition before the aggregation, changing the plan from a derived table scan to a simple indexed lookup.

7. Early range reduction

When the final ORDER BY and WHERE apply to the leftmost table, sort the main table first and then join. Original query with large sort cost is rewritten to a derived table that applies WHERE and ORDER BY before the LEFT JOINs, dropping execution time to about 1 ms.

8. Intermediate result push‑down

For a query that joins a large aggregated sub‑query, limit the rows early and use a WITH clause to materialize the small set, turning a multi‑second execution into a few milliseconds.

In summary, understanding MySQL’s query optimizer, avoiding implicit conversions, rewriting sub‑queries as joins or CTEs, and pushing predicates as early as possible are key to writing high‑performance SQL.