Practical MySQL Query Optimizations: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Predicate Push‑down, Early Row Limiting, and Intermediate Result Push‑down

1. LIMIT Clause

Pagination is a frequent use case but can become a performance bottleneck when large offsets are used; a composite index on type , name and create_time helps, yet LIMIT 1000000,10 still forces the engine to scan from the beginning.

By passing the maximum create_time from the previous page as a condition, the query time becomes essentially constant regardless 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

When a query compares a column and a literal of mismatched types, MySQL implicitly converts the string to a number, which disables index usage. The example shows a varchar(20) column bpn compared to a numeric literal, triggering a warning and a full scan.

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

3. Join‑based Update/Delete

MySQL 5.6’s materialized subquery optimization applies only to SELECT statements; UPDATE/DELETE statements still execute as dependent subqueries, leading to poor performance. Rewriting them as JOINs changes the execution plan from DEPENDENT SUBQUERY to DERIVED , cutting 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 o.status = 'applying';

4. Mixed Sorting

MySQL cannot use an index for mixed ordering on columns with different sort directions. By splitting the query into two UNION‑ALL subqueries—one for each is_reply value—and then ordering the combined result, the execution time drops from 1.58 s to 2 ms.

SELECT *
FROM (
  SELECT *
  FROM my_order o
  JOIN my_appraise a ON a.orderid = o.id AND a.is_reply = 0
  ORDER BY a.appraise_time DESC
  LIMIT 0,20
  UNION ALL
  SELECT *
  FROM my_order o
  JOIN my_appraise a ON a.orderid = o.id AND a.is_reply = 1
  ORDER BY a.appraise_time DESC
  LIMIT 0,20
) t
ORDER BY is_reply ASC, appraise_time DESC
LIMIT 20;

5. EXISTS Clause

MySQL still treats EXISTS as a nested subquery, which can be slower than an equivalent JOIN. Replacing the EXISTS with an inner join removes the dependent subquery 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 Push‑down

Conditions cannot be pushed into certain subqueries such as aggregated, LIMIT‑containing, UNION, or scalar subqueries. By moving the target = 'rm‑xxxx' predicate into the outer query, the plan changes from a derived table scan to a simple indexed lookup.

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

7. Early Row Limiting

When the final WHERE clause and ordering apply only to the leftmost table, sorting that table first and then performing the joins can dramatically reduce the amount of data processed. The rewritten query limits my_order before the LEFT JOINs, cutting execution time 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 Push‑down

When a subquery produces a large intermediate result (e.g., a full‑table aggregation), joining it directly can be expensive. By materializing the small set of resourceid values first (using WITH ) and then 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) AS allocated
  FROM my_resources r
  JOIN a ON r.resourcesid = a.resourcesid
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

Summary

The database optimizer generates execution plans that dictate how SQL statements run, but no optimizer is perfect. Understanding its behavior—especially around indexes, joins, subqueries, and predicate push‑down—allows developers to write high‑performance SQL. Using modern constructs such as WITH and avoiding unnecessary large offsets or implicit conversions leads to clearer, faster queries.