8 SQL Anti-Patterns That Kill Performance (and How to Fix Them)

1. LIMIT Pagination with Large Offsets

Offset pagination (e.g., LIMIT 1000000, 10) forces MySQL to scan and discard the first 1,000,000 rows even with an index. The fix is cursor-based pagination: use the last seen create_time as a filter.

-- Before (slow for large offsets)
SELECT *
FROM operation
WHERE type = 'SQLStats'
  AND name = 'SlowLog'
ORDER BY create_time
LIMIT 1000000, 10;

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

Query time becomes fixed regardless of data volume.

2. Implicit Type Conversion

Comparing a VARCHAR column to a numeric literal triggers implicit conversion ( CAST(bpn AS UNSIGNED)), which applies a function to the column and invalidates the index.

-- bpn is VARCHAR(20)
EXPLAIN EXTENDED SELECT *
FROM my_balance b
WHERE b.bpn = 14000000123
  AND b.isverified IS NULL;
-- Warning: Cannot use ref access on index 'bpn' due to type conversion

Always match literal types to column definitions; watch for framework-generated parameters.

3. Correlated UPDATE/DELETE with Subqueries

MySQL 5.6+ materializes subqueries only for SELECT. UPDATE/DELETE with IN (SELECT ...) runs as a DEPENDENT SUBQUERY (nested loop), causing massive slowdowns.

-- Before: 7 seconds
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 DEPENDENT SUBQUERY and Using temporary; Using filesort.

-- After: rewrite as JOIN, 2 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';

Plan now shows DERIVED for the subquery, eliminating the nested loop.

4. Mixed Sorting Across Joined Tables

MySQL cannot use an index for ORDER BY a.is_reply ASC, a.appraise_time DESC when columns come from different tables or have mixed directions. The plan shows Using filesort on 1.9M rows (1.58 s).

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;

Since is_reply has only two values (0,1), split into two queries and UNION ALL:

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;

Execution time drops to 2 ms.

5. EXISTS Subquery

MySQL executes EXISTS as a DEPENDENT SUBQUERY, scanning the outer table (1M rows) and probing the inner table per row (1.93 s).

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;

Rewrite as an INNER JOIN to allow the optimizer to choose join order:

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;

New plan shows all SIMPLE joins with ref / eq_ref access; time falls to 1 ms.

6. Condition Pushdown Blocked by Aggregation

Outer WHERE cannot be pushed into a derived table that uses GROUP BY, LIMIT, UNION, or select-list subqueries. The example aggregates the whole operation table before filtering.

-- Before: scans 20 rows in derived table after full aggregation
SELECT *
FROM (
  SELECT target, COUNT(*)
  FROM operation
  GROUP BY target
) t
WHERE target = 'rm-xxxx';

Plan: DERIVED on operation (20 rows scanned via index), then ref on derived table.

-- After: push predicate into aggregation
SELECT target, COUNT(*)
FROM operation
WHERE target = 'rm-xxxx'
GROUP BY target;

Plan becomes a single

SIMPLE

ref

on idx_4 (1 row). Reference: http://mysql.taobao.org/monthly/2016/07/08.

7. Early Range Reduction (Filter Before Join)

Joining large tables before applying WHERE and LIMIT on the driving table causes massive sorts (900K rows, 12 s).

-- Before: 12 seconds
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;

Plan shows ALL on my_order with Using temporary; Using filesort.

Restrict the driving table first in a derived table, then join:

-- After: ~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;

Derived table ( select_type=DERIVED) materializes only 15 rows; outer join operates on tiny set.

8. Intermediate Result Pushdown with CTE

A left join where the right side aggregates the entire my_resources table (2 s). Only rows matching the left side's resourceid are needed.

-- Before: full aggregation on my_resources
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;

Push the left-side keys into the right-side aggregation:

-- After: 2 ms
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 r,
       (
         SELECT resourceid
         FROM my_distribute d
         WHERE isdelete = 0 AND cusmanagercode = '1234567'
         ORDER BY salecode LIMIT 20
       ) a
  WHERE r.resourcesid = a.resourceid
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

Eliminate duplicate subquery a using a CTE ( WITH clause):

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;

Summary

Database compilers generate execution plans but are not perfect. Understanding optimizer limitations — such as lack of condition pushdown into certain derived tables, no materialization for UPDATE/DELETE subqueries, and inability to use indexes for mixed sorting — lets developers rewrite queries for orders-of-magnitude speedups. Adopt algorithmic thinking when designing schemas and SQL; use WITH (CTE) for clarity and to avoid repeated subqueries.