8 Common SQL Pitfalls and How to Optimize Them for Lightning‑Fast Queries

Today I share several common SQL “bad habits” and optimization techniques.

SQL Execution Order

SQL statements are processed in the following order:

1. LIMIT clause

Pagination is a common scenario but often problematic. For a simple query, adding a composite index on type, name, and create_time lets the optimizer use the index efficiently.

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

When the LIMIT clause becomes LIMIT 1000000,10, the query remains slow because the database must scan from the beginning to locate the millionth row.

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

In the new design the query time stays constant regardless of data volume.

2. Implicit conversion

Mismatched types between query variables and column definitions cause implicit conversion, which disables index usage. Example:

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

The column bpn is defined as varchar(20); MySQL converts the string to a number before comparison, causing the index to be ignored.

3. Update/Delete with sub‑queries

MySQL 5.6’s materialized view feature optimizes only SELECT statements. UPDATE/DELETE statements must be rewritten as JOINs.

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 plan uses a DEPENDENT SUBQUERY, taking seconds. Rewriting as a JOIN changes the plan to DERIVED and reduces execution time from 7 seconds to 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';

4. Mixed ordering

MySQL cannot use an index for mixed ordering, but a special rewrite can improve performance.

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 shows a full table scan. Since is_reply has only two values, splitting the query by each value and UNION ALL yields a dramatic speedup.

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 nested sub‑query. Replacing it with a JOIN eliminates the sub‑query and speeds up the 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;

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 down into complex views or sub‑queries such as aggregated sub‑queries, sub‑queries with LIMIT, UNION/UNION ALL, or sub‑queries in the SELECT list.

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

After confirming the condition can be pushed down, rewrite as:

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

7. Early data reduction

Original query performs left joins then sorts and limits, causing a large temporary result set.

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;

Rewrite by first limiting the main table, then joining:

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 sub‑query returns a large aggregated result, limit its impact by joining only the needed rows.

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;

Understanding how the database compiler generates execution plans is essential for writing high‑performance SQL. Using clear, concise statements—preferably with WITH clauses—helps both developers and the optimizer.