Why Your MySQL Queries Are Slow and How to Fix Common Mistakes

MySQL continued strong growth in 2016, but many users encounter slow response times and high CPU usage. The following sections summarize typical SQL problems identified by Alibaba Cloud RDS experts and provide concrete rewrites to improve performance.

1. LIMIT Misuse

Pagination often uses LIMIT offset, count. When the offset is large (e.g., LIMIT 1000000,10), MySQL must scan from the beginning even if an index exists, causing severe slowdown.

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

Rewrite by using the previous page’s maximum value as a condition, eliminating the need to count rows:

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

This makes query time independent of table size.

2. Implicit Type Conversion

When a column’s type (e.g., VARCHAR(20)) is compared to a numeric literal, MySQL converts the column to a number, disabling index usage:

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

Such mismatches often stem from framework‑generated parameters. Ensure the literal matches the column type or cast explicitly.

3. Update/Delete with Subqueries

MySQL 5.6’s materialized subquery optimization applies only to SELECTs. UPDATE/DELETE statements that embed subqueries execute as dependent subqueries, which are slow.

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
);

Rewrite using JOIN to turn the dependent subquery into a derived table, reducing execution from seconds to milliseconds:

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 o.status='applying';

4. Mixed Sorting

MySQL cannot use an index for mixed ASC/DESC sorting. By separating the two sort directions, performance improves dramatically.

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;

Original plan performed a full table scan; after rewriting, execution dropped from 1.58 s to 2 ms.

5. EXISTS Clause

MySQL still treats EXISTS as a nested subquery, which is inefficient. Converting it to a JOIN removes the subquery layer.

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:

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;

Execution time fell from ~1.9 s to 1 ms.

6. Condition Push‑Down Limitations

External predicates cannot be pushed into complex views or subqueries when they involve aggregation, LIMIT, UNION, or subqueries in the SELECT list. Example:

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

After confirming the predicate can be applied before aggregation, rewrite as:

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

7. Early Range Reduction

When the final ORDER BY and LIMIT apply to the leftmost table, filter and sort that table first, then join the remaining tables. Original query scanned ~900 k rows and took 12 s:

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

Optimized version materializes the filtered, sorted my_order subquery first, reducing execution to ~1 ms:

SELECT *
FROM (
  SELECT *
  FROM my_order
  WHERE display=0 AND ostatus=1
  ORDER BY 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. Intermediate Result Push‑Down and WITH

Complex subqueries that aggregate large tables can be rewritten to filter early and use WITH (CTE) for reuse. Example of a query with two subqueries a and c:

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

After pushing the filter into the aggregation and defining a once with a CTE, execution dropped from 2 s to 2 ms:

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

AliSQL will soon support the WITH syntax.

Conclusion

The database optimizer generates execution plans, but it cannot compensate for poorly written SQL. Understanding how MySQL’s compiler works, avoiding large offsets, ensuring type‑consistent predicates, preferring JOINs over subqueries, pushing conditions early, and using CTEs lead to high‑performance queries and lighter load on the server.