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

Common SQL Mistakes

1. LIMIT clause

Pagination is a typical scenario where performance issues arise; using a large offset forces the engine to scan from the beginning even with indexes.

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

When the offset grows to a million, the query remains slow because the database must count rows from the start.

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 rewritten query keeps the execution time constant regardless of data volume.

2. Implicit conversion

Mismatched data types between query literals and column definitions cause MySQL to convert values, disabling index usage.

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

Because bpn is defined as VARCHAR(20), MySQL converts the string to a number, leading to a warning and index loss.

3. Join updates/deletes

MySQL 5.6’s materialized subquery optimization does not apply to UPDATE/DELETE; they are executed as dependent subqueries.

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

Execution plan shows a DEPENDENT SUBQUERY.

+----+--------------------+-------+------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| id | select_type        | table | type | possible_keys | key     | key_len | ref   | rows | Extra                                               |
+----+--------------------+-------+------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| 1  | PRIMARY            | o     | index|               | PRIMARY | 8       |       | 24   | Using where; Using temporary                        |
| 2  | DEPENDENT SUBQUERY |       |      |               |         |         |       |      | Impossible WHERE noticed after reading const tables |
| 3  | DERIVED            | o     | ref  | idx_2,idx_5   | idx_5   | 8       | const | 1    | Using where; Using filesort                         |
+----+--------------------+-------+------+---------------+---------+---------+-------+------+-----------------------------------------------------+

Rewriting with a JOIN changes the select type to DERIVED and reduces execution time 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 status = 'applying';

4. Mixed ordering

MySQL cannot use an index for mixed ASC/DESC ordering, leading to full‑table scans.

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;

Plan shows an ALL scan on a. By filtering on is_reply before the UNION, execution time drops from 1.58 s to 2 ms.

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

5. EXISTS clause

MySQL still executes EXISTS as a nested subquery.

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;

Plan shows a DEPENDENT SUBQUERY. Rewriting to a JOIN eliminates the subquery and reduces execution time from 1.93 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. Condition pushdown

External conditions cannot be pushed into complex views or subqueries in the following cases:

Aggregate subqueries

Subqueries with LIMIT

UNION or UNION ALL subqueries

Subqueries appearing in output fields

Example where the filter applies after aggregation:

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

After confirming the condition can be pushed down, the query becomes:

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

7. Early range reduction

Original query performs left joins first, then sorts and limits, causing a 12 s scan of ~900 k rows.

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;

Rewriting by sorting the main table early reduces execution time 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. Intermediate result pushdown

When a subquery returns a large aggregated result set, joining it directly can be costly.

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) allocated
  FROM my_resources
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

Using a WITH clause makes the query clearer and faster.

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.resourceid
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

AliSQL will soon support the WITH syntax.

Conclusion

The database compiler generates execution plans that determine actual query behavior; understanding its limitations helps write high‑performance SQL.

Programmers should bring algorithmic thinking into data‑model design and query writing.

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

When encountering difficulties with cloud databases, seek expert assistance.