MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Join Updates, Mixed Sorting, EXISTS, Condition Pushdown, Early Limiting, and Intermediate Result Pushdown

Pagination is a common scenario that often leads to performance problems; using a simple LIMIT clause like SELECT * FROM operation WHERE type='SQLStats' AND name='SlowLog' ORDER BY create_time LIMIT 1000, 10; may be slow when the offset is large because the database must scan from the beginning.

By using the maximum value of the previous page as a filter, the query can be rewritten to keep execution time constant regardless of data size:

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

Implicit type conversion also hurts performance. For example, comparing a VARCHAR column to a numeric literal forces MySQL to convert the column values, causing index loss:

SELECT * FROM my_balance b
WHERE b.bpn = 14000000123
  AND b.isverified IS NULL;

Updating or deleting with subqueries can be inefficient; rewriting them as JOINs changes the execution plan from DEPENDENT SUBQUERY to DERIVED and speeds up the operation dramatically:

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

MySQL cannot use indexes for mixed sorting, but splitting the query by the binary column and unioning the results can reduce execution time from seconds to milliseconds:

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;

Replacing EXISTS with a JOIN eliminates nested subqueries and reduces execution time from seconds to a millisecond:

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;

Condition push‑down cannot be applied to certain subqueries (aggregates, LIMIT, UNION, etc.). When possible, moving the filter into the inner query simplifies the plan:

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

Early limiting can shrink the data set before expensive joins. By materializing the limited result set first, the query runs in about 1 ms instead of many seconds:

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;

Intermediate result push‑down reduces the cost of a large aggregation by joining only the needed keys, and using a WITH clause makes the query clearer and faster:

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;

In summary, understanding how the database compiler generates execution plans and applying these optimization patterns—proper indexing, avoiding implicit conversions, rewriting subqueries as joins, limiting early, and using CTEs—can significantly improve SQL performance across many database systems.