Advanced MySQL Query Optimization Techniques: LIMIT, Implicit Conversion, Join Updates, Mixed Sorting, EXISTS, Predicate Pushdown, Early Range Reduction, and CTEs

Pagination is a frequent source of performance problems; using a large OFFSET in a LIMIT clause forces the engine to scan many rows. Rewriting the query to filter by the maximum value of the previous page avoids this cost.

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 conversion occurs when a column type does not match the literal type, causing MySQL to convert values and disable index usage. The example shows a VARCHAR column compared with a numeric literal, leading to a full scan.

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

Update and delete statements that contain sub‑queries are executed as dependent sub‑queries, which are very slow. Converting them to explicit JOIN forms changes the execution plan to DERIVED and dramatically improves speed.

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

MySQL cannot use indexes for mixed sorting (different ASC/DESC directions). By splitting the query into two parts—one for each sort direction—and using UNION ALL , the optimizer can use indexes and reduce execution time from seconds to a few 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;

The EXISTS clause often results in a dependent sub‑query. Rewriting it as a JOIN eliminates the nested loop and cuts execution time from seconds to milliseconds.

SELECT * FROM my_neighbor n LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id='xxx' INNER JOIN message_info m ON n.id = m.neighbor_id AND m.inuser='xxx' WHERE n.topic_status < 4 AND n.topic_type <> 5;

Predicate push‑down cannot be applied to certain constructs such as aggregated sub‑queries, LIMIT‑containing sub‑queries, UNION/UNION ALL sub‑queries, or sub‑queries in the SELECT list. When possible, move the filter into the outer query to avoid materialising large intermediate results.

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

Early range reduction: if the final ORDER BY and LIMIT apply to the leftmost table, sort that table first and then perform the joins, reducing the amount of data processed in later stages.

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

Intermediate result push‑down: when a sub‑query returns a large aggregated set, filter it early or rewrite using a CTE to avoid repeated scans.

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

In summary, the MySQL optimizer generates execution plans that are not always optimal. Understanding its limitations and applying techniques such as proper indexing, rewriting sub‑queries as joins, early filtering, and using CTEs can dramatically improve SQL performance.