8 SQL Pitfalls That Can Slow Your Queries 100‑Fold – How to Avoid Them

1. LIMIT Clause

Pagination is a frequent source of performance problems. A query like

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

can be fast with a proper index, but using a large offset such as LIMIT 1000000,10 forces the engine to scan from the beginning, making it slow.

Rewriting the query to filter by the last seen create_time value eliminates the offset scan:

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

With this design the query time stays roughly constant regardless of table size.

2. Implicit Conversion

When a column’s type does not match the literal value, MySQL converts the literal, which can disable index usage. Example:

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

The column bpn is defined as VARCHAR(20); MySQL converts the string to a number, causing a warning and preventing ref access on the index.

Such mismatches often arise from framework‑generated parameters and should be avoided by using matching types.

3. Update/Delete with Sub‑queries

MySQL 5.6’s materialized view optimization applies only to SELECT statements. UPDATE statements that use sub‑queries are executed as dependent sub‑queries, leading to poor performance.

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

The original execution plan shows a DEPENDENT SUBQUERY taking about 7 seconds. Rewriting with an explicit JOIN changes the plan to DERIVED and reduces execution time to roughly 2 ms:

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 ordering, but a UNION‑ALL trick can help when the ordering column has low cardinality (e.g., is_reply = 0/1).

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;

The original plan performs a full table scan. By splitting the query into two parts—one for each is_reply value—and UNION‑ALL them, the 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, appraise_time DESC
LIMIT 20;

5. EXISTS Clause

MySQL treats EXISTS as a nested sub‑query, which can be slow. Converting the pattern to a JOIN removes the sub‑query.

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;

The original plan shows a DEPENDENT SUBQUERY with execution time ~1.93 s. Rewriting with an INNER JOIN yields a plan with only index lookups and reduces time 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 Push‑down

Conditions cannot be pushed down into certain sub‑queries, such as aggregated sub‑queries, LIMIT‑containing sub‑queries, UNION/UNION ALL sub‑queries, or sub‑queries in the SELECT list.

Example where the filter is applied after aggregation:

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

After confirming the predicate can be pushed down, the query is rewritten as:

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

The new plan uses an index ( idx_4) and executes in a few milliseconds.

7. Early Range Reduction

When the final ORDER BY and WHERE clauses target the leftmost table, sorting can be performed before the joins, dramatically shrinking the data set early.

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;

The original plan estimates 900 k rows and takes ~12 s. By materializing the ordered subset first and then joining, execution time falls 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 Set Push‑down (WITH)

Repeated sub‑queries can be factored out with a WITH clause to avoid redundant scans.

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

This rewrite reduces execution time from about 2 seconds to 2 milliseconds by eliminating duplicated scans of the my_distribute table.

Conclusion

The database optimizer generates execution plans, but it is not perfect. Understanding its behavior and applying the patterns above—using proper indexes, avoiding large offsets, eliminating implicit conversions, preferring joins over sub‑queries, pushing conditions early, and leveraging WITH —allows developers to write high‑performance SQL across different database systems.