Master MySQL Execution Order: 8 Proven Techniques to Supercharge Query Performance

1. LIMIT Clause

Pagination is a common scenario that often causes performance problems. A simple query with LIMIT 1000,10 can be slow because MySQL must scan rows from the beginning to locate the offset. Using the previous page's maximum value as a filter can avoid this issue.

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

When the offset is large (e.g., LIMIT 1000000,10), the query remains slow even though only ten rows are returned, because the engine still scans from the start. Rewriting the query to filter by the last create_time of the previous page makes execution time essentially constant.

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 literal type, MySQL implicitly converts the literal, which can cause index loss. For example, comparing a VARCHAR column with a numeric literal forces a conversion and prevents index usage.

EXPLAIN EXTENDED SELECT *
FROM   my_balance b
WHERE  b.bpn = 14000000123
  AND  b.isverified IS NULL;
SHOW WARNINGS;
| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn' |

Such mismatches often stem from frameworks automatically binding parameters. Developers should ensure that bound values match column definitions to avoid hidden full‑table scans.

3. Join‑Based Update/Delete

MySQL 5.6 introduced materialized subquery optimization for SELECT, but UPDATE/DELETE still execute dependent subqueries. Rewriting them as JOINs converts the plan from DEPENDENT SUBQUERY to DERIVED, dramatically reducing execution time.

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

Original plan shows a DEPENDENT SUBQUERY with high cost. After rewriting:

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 directions. By splitting the query into two UNION ALL parts—one for each is_reply value—and sorting each part individually, the optimizer can use the index on appraise_time, reducing execution time from 1.58 s to 2 ms.

SELECT * FROM (
  SELECT * FROM my_order o
  INNER JOIN my_appraise a ON a.orderid = o.id
  WHERE 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
  WHERE 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 subquery, which is inefficient. Replacing it with a JOIN eliminates the subquery and brings execution time down from ~2 s to 1 ms.

SELECT *
FROM   my_neighbor n
LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id = 'xxx'
INNER JOIN message_info m ON n.id = m.neighbor_id AND m.inuser = 'xxx'
WHERE  n.topic_status < 4
  AND  n.topic_type <> 5;

6. Condition Push‑Down

External conditions cannot be pushed into subqueries that contain aggregation, LIMIT, UNION, or sub‑selects in the SELECT list. By moving the filter before the aggregation, the plan changes from a two‑step process to a simple indexed lookup.

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

7. Early Range Reduction

When the final ORDER BY and WHERE apply to the leftmost table, sorting that table first and then performing the joins can shrink the intermediate result set dramatically. The rewritten query materializes the ordered subset before the joins, cutting execution time from 12 s 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 Push‑Down

When a subquery produces a large intermediate result, joining it directly can be costly. By extracting the reusable subquery into a CTE ( WITH) and joining only the necessary rows, execution time drops from seconds to milliseconds.

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

Understanding MySQL's optimizer and execution plan allows developers to write high‑performance SQL by avoiding common pitfalls such as large offsets, implicit type conversion, dependent subqueries, and non‑pushable conditions.