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

Sharing several common "bad habits" in SQL and practical optimization tricks.

SQL Execution Order

1. LIMIT Clause

Pagination is a typical scenario where developers often add a composite index on

type

,

name

, and

create_time

to let the ORDER BY use the index. However, using a large offset such as

LIMIT 1000000,10

forces the engine to scan from the beginning, causing slowness.

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

Rewriting the query to use the maximum value of the previous page as a condition eliminates the large offset:

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

2. Implicit Type Conversion

When a column type does not match the query value, MySQL converts the string to a number, causing index loss. Example:

<code>SELECT *
FROM   my_balance b
WHERE  b.bpn = 14000000123
      AND b.isverified IS NULL;</code>

Here

bpn

is

VARCHAR(20)

; the conversion disables the index on

bpn

.

3. Update/Delete with Sub‑queries

MySQL 5.6’s materialized sub‑query feature only optimizes SELECTs. UPDATE/DELETE statements with sub‑queries are executed as dependent sub‑queries, which are slow.

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

Rewriting with a JOIN changes the execution plan from

DEPENDENT SUBQUERY

to

DERIVED

, reducing execution time from seconds to milliseconds:

<code>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';</code>

4. Mixed Ordering

MySQL cannot use an index for mixed ORDER BY clauses. By separating the two possible values of

is_reply

(0 and 1) into two UNIONed queries, the query runs in milliseconds.

<code>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;</code>

5. EXISTS Clause

MySQL treats

EXISTS

as a dependent sub‑query. Rewriting it as a JOIN removes the sub‑query and drops execution time from seconds to a millisecond.

<code>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;</code>

Rewritten:

<code>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;</code>

6. Condition Push‑down

External 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 SELECT list. Example:

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

After pushing the condition down:

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

7. Early Row Limiting

When the final ORDER BY and LIMIT apply to the leftmost table, sort and limit before joining can drastically reduce the data processed.

<code>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;</code>

Rewritten:

<code>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;</code>

8. Intermediate Result Push‑down

Using CTEs (WITH) to materialize a small set before joining large tables avoids costly full‑table aggregations.

<code>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;</code>

Summary

The database optimizer decides the actual execution plan, but it is not perfect. Understanding its behavior and avoiding common pitfalls—such as large OFFSETs, implicit conversions, sub‑query updates, mixed ordering, unnecessary EXISTS, non‑pushable conditions, late filtering, and unbounded intermediate results—allows developers to write high‑performance SQL. Adopting clear, concise queries and using CTEs where appropriate reduces database load.

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