MySQL Query Optimization: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Predicate Pushdown, Early Limiting, and Intermediate Result Pushdown

The article introduces a series of MySQL performance‑tuning techniques aimed at improving query execution speed in common scenarios such as pagination, joins, and complex sub‑queries.

1. LIMIT statement

When using large offsets (e.g., LIMIT 1000000,10 ) MySQL must scan rows from the beginning, causing slowdown. Rewriting the query to use the previous page's maximum create_time as a filter makes the execution time independent of table 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;

2. Implicit conversion

Comparing a VARCHAR column with a numeric literal forces MySQL to convert the column to a number, which disables index usage. The example shows a warning about type conversion on the bpn index.

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

3. Join rewrite for UPDATE/DELETE

MySQL 5.6 executes correlated sub‑queries as DEPENDENT SUBQUERY, leading to poor performance. Converting the sub‑query to a JOIN changes the access type to DERIVED and reduces execution time from seconds to milliseconds.

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 ASC/DESC sorting. By splitting the query into two UNION ALL parts—one for each is_reply value—and sorting each part individually, the total execution time drops from 1.58 s to 2 ms.

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 statement

MySQL still treats EXISTS as a nested sub‑query. Rewriting it as a JOIN eliminates the sub‑query and reduces execution time from 1.93 s to 1 ms.

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. Predicate pushdown

Conditions cannot be pushed down into complex views or sub‑queries such as aggregation, LIMIT, UNION, or scalar sub‑queries. The example shows a derived table that first groups by target and then filters; moving the filter before the GROUP BY yields a more efficient plan.

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

7. Early limiting

When the final ORDER BY and WHERE apply only to the leftmost table, it is beneficial to apply LIMIT early on that table before performing the joins. This reduces the scanned rows dramatically and brings execution time down to about 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 pushdown

When a sub‑query returns a large aggregated result set that is later joined on a key, pushing the join condition into the sub‑query limits the amount of data processed. Using a CTE (WITH) 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) AS allocated
  FROM my_resources r
  JOIN a ON r.resourcesid = a.resourcesid
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

Summary

Database query planners strive to generate efficient execution plans, but they are not perfect; understanding their limitations and applying manual optimizations—such as proper indexing, predicate pushdown, early limiting, and using CTEs—allows developers to write high‑performance SQL statements.