Advanced MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Join Rewrite, and More

Database compilers generate execution plans that determine how SQL statements run, but they are not perfect; understanding their behavior enables developers to write high‑performance queries.

1. LIMIT clause – Using a large offset (e.g., LIMIT 1000000,10 ) forces the engine to scan many rows before returning the requested ten, causing slowness. Rewriting the query to filter by the maximum value of the previous page (e.g., WHERE create_time > '2017-03-16 14:00:00' ) makes the 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;

2. Implicit conversion – Mismatched data types (e.g., comparing a VARCHAR column with a numeric literal) cause MySQL to convert the column value, invalidating indexes. The example shows a warning about ref access loss due to conversion.

mysql> explain extended SELECT *
    FROM my_balance b
    WHERE b.bpn = 14000000123
      AND b.isverified IS NULL ;
mysql> show warnings;
| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn' |

3. Update/Delete with JOIN – MySQL 5.6’s materialized subquery optimization applies only to SELECT. For UPDATE/DELETE, rewrite the statement using JOIN to avoid dependent subqueries and achieve dramatic speedups.

UPDATE operation o
JOIN (
    SELECT id
    FROM operation
    WHERE `group` = 123
      AND status NOT IN ('done')
    ORDER BY parent, id
    LIMIT 1
) t ON o.id = t.id
SET status = 'applying';

4. Mixed ordering – MySQL cannot use indexes for mixed ASC/DESC ordering. By separating the query into two UNION ALL parts filtered on the boolean is_reply column, the planner can use indexes and 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, appraisetime DESC
LIMIT 20;

5. EXISTS clause – EXISTS is executed as a nested subquery. Replacing it with an explicit JOIN eliminates the subquery and cuts execution time from ~2 seconds 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. Condition push‑down – Conditions cannot be pushed into subqueries that involve aggregation, LIMIT, UNION, or scalar subqueries. By moving the filter into the outer query, the planner can use indexes directly.

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

7. Early result set reduction – When the final ORDER BY and LIMIT apply to the leftmost table, sort that table first, then perform the joins. This reduces the intermediate row count and brings execution time down to milliseconds.

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. Pushing down intermediate result sets – For a query that joins a large aggregated subquery, limit the intermediate set by first selecting the needed keys, then join only those rows, achieving a reduction 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.resourceid
    GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

9. Summary – Understanding how the database compiler works and applying these optimization patterns—smart LIMIT usage, avoiding implicit conversions, rewriting subqueries with JOIN or CTE, and pushing filters early—helps developers write concise, efficient SQL that reduces load on the database engine.