MySQL Performance Optimization Techniques: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Condition Pushdown, and More

1. LIMIT Clause

Pagination is a frequent scenario but can become a performance bottleneck when using large offsets, e.g., LIMIT 1000000,10. Even with an index, MySQL must scan from the beginning to locate the millionth row.

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

A better approach is to use the maximum value of the previous page as a condition, eliminating the need for a large offset.

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

With this redesign the query time stays constant regardless of table size.

2. Implicit Conversion

When a column type does not match the query parameter type, MySQL performs an implicit conversion that can invalidate indexes. Example:

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

Because bpn is defined as VARCHAR(20), MySQL converts the string to a number before comparison, causing the index on bpn to be ignored.

3. Join‑Based Update/Delete

MySQL 5.6 introduced materialized sub‑query optimization for SELECTs, but UPDATE/DELETE statements still execute dependent sub‑queries. Rewriting them as JOINs changes the execution plan from DEPENDENT SUBQUERY to DERIVED, dramatically improving speed.

-- Original UPDATE (slow)
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 UPDATE (fast)
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, but splitting the query by the binary column and using UNION ALL allows each part to use the index, reducing 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;

5. EXISTS Clause

MySQL executes EXISTS as a dependent sub‑query. Converting it to a JOIN removes the sub‑query and can cut execution time from ~2 seconds to 1 ms.

-- Original with EXISTS (slow)
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 JOIN (fast)
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

Conditions cannot be pushed into certain sub‑queries (aggregates, LIMIT, UNION, etc.). By moving the filter before the aggregation, the planner can use indexes and reduce rows processed.

-- Original (condition after aggregation)
SELECT *
FROM (
  SELECT target, COUNT(*)
  FROM   operation
  GROUP BY target
) t
WHERE target = 'rm-xxxx';

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

7. Early Range Reduction

When the final ORDER BY and WHERE clauses apply only to the leftmost table, apply them first, limit the result set, and then join the remaining tables. This reduces the amount of data processed in later joins.

-- Original (large join then limit)
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 (limit 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 with WITH

Repeated sub‑queries can be factored out using a CTE ( WITH) to avoid redundant scans and simplify the statement.

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

Conclusion

The MySQL optimizer generates execution plans that determine how a query runs, but it is not perfect. Understanding its behavior—especially how it handles LIMIT offsets, type conversion, sub‑queries, and condition push‑down—allows developers to write SQL that avoids common pitfalls and achieves high performance.

Adopting clear, algorithm‑driven SQL, using CTEs, and keeping statements concise not only improves readability but also reduces the load on the database engine.