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

Today we discuss several SQL anti‑patterns that often cause performance problems and show how to rewrite them for much faster execution.

1. LIMIT clause

Pagination is a common scenario but can be very slow when using a large offset because the database must scan from the beginning. Rewriting the query to use the previous page's maximum value as a condition makes the execution time independent of the offset.

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 conversion

When a column type does not match the query variable, MySQL performs an implicit conversion that can invalidate indexes. For example, the column bpn is defined as VARCHAR(20), but the query supplies a numeric literal, causing the engine to convert the string to a number and ignore the index.

mysql> explain extended SELECT * FROM my_balance b WHERE b.bpn = 14000000123 AND b.isverified IS NULL;
| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn' |

3. UPDATE/DELETE with subqueries

MySQL 5.6 introduced materialized subquery optimization for SELECTs only; UPDATE/DELETE statements still execute the subquery as a dependent subquery, which is very 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 the statement with a JOIN changes the execution plan from DEPENDENT SUBQUERY to DERIVED, reducing 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 ORDER BY clauses. By separating the two possible values of is_reply and using UNION ALL, the query can leverage indexes and reduce execution time dramatically.

SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id WHERE 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 WHERE is_reply = 1 ORDER BY appraise_time DESC LIMIT 0,20
ORDER BY is_reply ASC, appraise_time DESC LIMIT 20;

5. EXISTS clause

MySQL still executes EXISTS as a nested subquery, which can be replaced by a JOIN to avoid the extra subquery overhead.

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 version:

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

External query conditions cannot be pushed into complex subqueries such as aggregates, LIMIT‑containing subqueries, UNIONs, or subqueries in the SELECT list. By moving the filter into the inner query, the optimizer can use indexes directly.

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, sorting can be performed before the joins, drastically reducing the amount of data processed.

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. Pushing intermediate result sets

When a subquery returns a large intermediate result, limiting it early and using a WITH clause can avoid repeated scans and improve performance.

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

Conclusion

The database compiler generates execution plans that determine how SQL runs, but it is not perfect. Understanding its behavior and avoiding common pitfalls enables you to write high‑performance SQL statements.

Source: yq.aliyun.com/articles/72501