Boost MySQL Performance: 8 Common SQL Pitfalls and How to Fix Them

Introduction

MySQL continued strong growth in 2016, with many applications and migrations from Oracle, but users often encounter slow response times or CPU saturation. The following examples illustrate typical SQL issues and their optimized rewrites.

1. LIMIT Clause

Pagination queries often suffer when using large offsets. Adding a composite index on the filtered columns helps, but when the offset is huge (e.g., LIMIT 1000000,10) the query remains slow because MySQL must scan from the beginning.

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

Rewriting the query to use the maximum value of the previous page as a condition fixes the problem:

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

The execution time becomes essentially constant regardless of table size.

2. Implicit Conversion

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

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

Because bpn is VARCHAR(20), MySQL converts it to a number, disabling the index. The solution is to ensure matching types or avoid implicit casts.

3. Update/Delete with Subqueries

MySQL 5.6’s materialized subquery feature only optimizes SELECTs. UPDATE/DELETE statements with subqueries are executed 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
);

Rewriting with a JOIN changes the execution plan from DEPENDENT SUBQUERY to DERIVED, reducing runtime 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 ORDER BY directions. By filtering on the is_reply flag first, the query can avoid a full table scan:

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

Execution time drops from 1.58 s to 2 ms.

5. EXISTS Clause

EXISTS often leads to nested subqueries. Converting it to a JOIN eliminates the extra level and speeds up the query dramatically.

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;

Runtime improves from ~2 s to 1 ms.

6. Condition Push‑Down

External query conditions cannot be pushed into complex views or subqueries such as aggregation, LIMIT, UNION, or scalar subqueries. By moving the filter into the inner query, the plan becomes simpler and faster:

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

7. Pre‑Filtering (Early Limit)

When the final WHERE and ORDER BY apply to the leftmost table, apply them early in a derived table to reduce rows before joins:

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;

This reduces execution time from ~12 s to ~1 ms.

8. Intermediate Result Push‑Down and WITH

For queries that join a large aggregated subquery, filter the subquery first and use a WITH clause to avoid repeated evaluation:

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 resourcesid, SUM(IFNULL(allocation,0)*12345) allocated
  FROM my_resources r, a
  WHERE r.resourcesid=a.resourcesid
  GROUP BY resourcesid
) c ON a.resourceid=c.resourcesid;

AliSQL will soon support WITH syntax.

Conclusion

Database compilers generate execution plans, but they are not perfect; understanding their behavior helps write high‑performance SQL.

Developers should bring algorithmic thinking into data model and query design.

Adopt WITH statements for complex queries to keep them clear and efficient.

When using cloud databases, leverage vendor expert services for difficult problems.