8 Common SQL Pitfalls and How to Optimize Them for High Performance

SQL Execution Order

1. LIMIT Clause

Pagination is a frequent use case, but a naïve LIMIT can become a performance killer when the offset is large. Even with an index, the engine must walk from the beginning to the requested offset.

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

When the offset grows to millions, the query remains slow because the database does not know where the millionth row starts.

A better pattern is to use the maximum value from the previous page as a filter:

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

Comparing a numeric column stored as varchar(20) with a number forces MySQL to convert the column values to numbers, which disables index usage.

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

The warning shows that the index on bpn cannot be used because of type conversion.

3. Update/Delete with Subqueries

MySQL executes a subquery in an UPDATE as a dependent subquery, which is very slow. Rewriting the statement as a JOIN turns the plan into a derived query and dramatically improves speed.

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 Sorting

MySQL cannot use an index for mixed ORDER BY clauses. By splitting the query into two parts—one for each value of is_reply —and then UNION ALL, the execution time drops from seconds to milliseconds.

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 treats EXISTS as a nested subquery. Replacing it with a JOIN removes the nesting and reduces execution time from nearly two seconds to one millisecond.

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 Push‑Down

Conditions cannot be pushed down into complex subqueries such as aggregated, LIMIT‑containing, or UNION subqueries. By moving the filter into the outer query, the plan becomes much simpler.

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

7. Early Limiting

When the final ORDER BY and LIMIT apply to the leftmost table, apply them before the joins. This reduces the amount of data the join has to process.

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. Using WITH for Repeated Subqueries

When a subquery is used multiple times, define it once with a CTE ( WITH) and reference it in the main query. This eliminates redundant work and makes the statement clearer.

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;

Conclusion

The database optimizer decides how a query is executed, but it is not perfect. Understanding its behavior and rewriting queries to avoid its blind spots can turn multi‑second queries into sub‑millisecond ones. Adopt habits such as using CTEs, pushing conditions early, and avoiding implicit conversions to keep your database performant.

Source: http://mysql.taobao.org/monthly/2016/07/08

Thanks for reading, hope this helps :)