8 Common SQL Pitfalls and How to Optimize Them for Faster Queries

1. LIMIT Clause Misuse

Pagination often uses LIMIT offset, count. A simple query with a composite index on type, name, and create_time runs fast, but LIMIT 1000000,10 forces the engine to scan from the beginning, causing slowdown.

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 makes the execution time independent of data size:

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, invalidating indexes:

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

The warning shows a conversion on field bpn, causing a full‑table scan.

3. Update/Delete with Sub‑queries

MySQL 5.6’s materialized sub‑query optimization applies only to SELECTs. UPDATE statements that use IN (SELECT …) become dependent sub‑queries and run slowly.

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 as a JOIN changes the execution plan from DEPENDENT SUBQUERY to DERIVED, dropping 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 indexes for mixed ASC/DESC ordering. By splitting the query on the binary is_reply flag and using UNION ALL, the plan changes from a full‑table scan with filesort to two indexed scans, reducing runtime 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 executes EXISTS as a dependent sub‑query. Converting it to a JOIN eliminates the sub‑query and cuts execution time from ~2 s to 1 ms.

SELECT *
FROM   my_neighbor n
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 into certain sub‑queries (aggregates, LIMIT, UNION, scalar sub‑queries). By moving the filter into the outer query, the plan becomes a simple index lookup.

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

7. Early Limiting (Pre‑filtering)

When the final ORDER BY and LIMIT apply to the leftmost table, filter and sort that table first, then join the remaining tables. This reduces rows processed from ~900 k to 15, cutting runtime to ~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 Push‑Down with CTE

Repeated sub‑query a can be factored out using a Common Table Expression (CTE). This avoids redundant scans and brings execution time from seconds to milliseconds.

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

Conclusion

Understanding MySQL’s optimizer and its limitations enables developers to rewrite queries—using proper indexes, avoiding implicit conversions, replacing sub‑queries with joins, applying early limits, and leveraging CTEs—to achieve dramatic performance gains across many database systems.

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