SQL Execution Order and Performance Optimization Techniques

The author, a senior architect, shares practical insights on MySQL SQL execution order and common performance problems, providing concrete rewrite examples that dramatically improve query speed.

1. LIMIT clause

Pagination is a frequent source of performance issues; using a large offset forces the database to scan many rows.

FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT

Original query with a large offset:

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

Optimized query that uses the previous page's maximum value:

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 value, MySQL may convert the column, causing index loss.

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' |

3. Update/Delete with JOIN

MySQL 5.6 materialized sub‑queries only help SELECT; UPDATE/DELETE need manual rewriting.

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

Rewritten using JOIN:

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 sorting

MySQL cannot use an index for mixed ORDER BY, but splitting by the binary column can help.

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;

Optimized version using 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,
          appraisetime DESC
LIMIT  20;

5. EXISTS clause

EXISTS often leads to nested sub‑queries; converting to JOIN can eliminate the extra level.

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 with JOINs:

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 predicates cannot be pushed into complex sub‑queries such as aggregates, LIMIT, UNION, or scalar sub‑queries.

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

After pushing the condition down:

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

7. Early range reduction

When the WHERE clause and ORDER BY apply to the leftmost table, filter and sort it first.

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;

Rewritten to limit the main table early:

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 push‑down

When a sub‑query returns many rows, filter it before joining.

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;

Using a CTE to avoid repeated sub‑queries:

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;

Conclusion: Understanding the MySQL optimizer’s behavior and applying these rewrite patterns—using proper indexes, limiting early, converting sub‑queries to JOINs or CTEs—can turn slow queries that take seconds into ones that finish in milliseconds.