How to Fix Common MySQL Performance Pitfalls: Real-World SQL Optimizations

Introduction

MySQL continues to grow in popularity; many customers experience slow response times or high CPU usage. This article summarizes common SQL problems from ApsaraDB expert diagnosis reports and shows how to rewrite them for better performance.

Common SQL Mistakes

1. LIMIT clause

Pagination often uses LIMIT offset, count. A small offset works with a composite index, but a large offset such as LIMIT 1000000,10 forces the engine to scan from the beginning, causing severe slowdown. Rewriting the query to filter by the last seen value keeps execution time constant.

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 type conversion

When a column type differs from the literal, MySQL converts the column to a number, which disables index usage. The example below compares a VARCHAR(20) column with a numeric literal, generating a warning and a full‑table scan.

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

3. Update/Delete with subqueries

MySQL 5.6’s materialized‑subquery optimization applies only to SELECT. An UPDATE … IN (SELECT …) becomes a DEPENDENT SUBQUERY, which is slow. Rewriting it as a JOIN changes the plan to DERIVED and reduces execution time from seconds to milliseconds.

UPDATE operation o
JOIN (
    SELECT o.id
    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 o.status = 'applying';

4. Mixed ordering

MySQL cannot use an index for mixed ASC/DESC ordering. By separating rows according to is_reply and using UNION ALL, the query runs in about 2 ms instead of 1.58 s.

SELECT * FROM (
    SELECT * FROM my_order o
    INNER JOIN my_appraise a ON a.orderid = o.id
    WHERE is_reply = 0
    ORDER BY appraise_time DESC
    LIMIT 0,20
) UNION ALL
SELECT * FROM (
    SELECT * FROM my_order o
    INNER JOIN my_appraise a ON a.orderid = o.id
    WHERE is_reply = 1
    ORDER BY appraise_time DESC
    LIMIT 0,20
) ORDER BY is_reply ASC, appraisetime DESC
LIMIT 20;

5. EXISTS clause

The EXISTS clause is executed as a nested subquery. Replacing it with an INNER JOIN removes the dependent subquery and cuts execution time from ~2 s to 1 ms.

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

6. Predicate push‑down

Conditions cannot be pushed into complex views or subqueries such as aggregated subqueries, LIMIT subqueries, UNION subqueries, or subqueries in the SELECT list. Moving the predicate before the subquery changes the plan from DEPENDENT SUBQUERY to SIMPLE, dramatically improving performance.

SELECT target, COUNT(*)
FROM operation
WHERE target = 'rm-xxxx'
GROUP BY target;

7. Early row reduction

When WHERE and ORDER BY apply to the leftmost table, sorting that table first and then joining reduces the number of rows processed. The rewritten query drops execution time from 12 s to about 1 ms.

SELECT *
FROM (
    SELECT *
    FROM my_order o
    WHERE o.display = 0 AND o.ostaus = 1
    ORDER BY o.selltime DESC
    LIMIT 0,15
) o
LEFT JOIN my_userinfo u ON o.uid = u.uid
LEFT JOIN my_productinfo p ON o.pid = p.pid
ORDER BY o.selltime DESC
LIMIT 0,15;

8. Pushing intermediate result sets

For a query that joins a filtered subquery with an aggregated subquery, moving the aggregation into a WITH clause and joining only the needed keys reduces runtime from seconds to milliseconds.

WITH a AS (
    SELECT resourceid
    FROM my_distribute
    WHERE isdelete = 0 AND cusmanagercode = '1234567'
    ORDER BY salecode
    LIMIT 20
)
SELECT a.*, c.allocated
FROM a
LEFT JOIN (
    SELECT resourcesid, SUM(IFNULL(allocation,0)*12345) AS allocated
    FROM my_resources r
    WHERE r.resourcesid = a.resourcesid
    GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

Conclusion

Database compilers generate execution plans that heavily influence performance. Understanding their behavior and avoiding common pitfalls—large offsets, implicit conversions, subquery patterns, lack of predicate push‑down, and inefficient ordering—allows developers to write efficient SQL, reduce load on cloud databases, and achieve millisecond‑level response times.