Boost MySQL Performance: Master LIMIT, Implicit Conversion, JOIN Updates, and More

1. LIMIT Clause Optimization

Pagination often uses LIMIT offset, count, but large offsets force the engine to scan from the beginning. Instead of

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

, use the maximum create_time from the previous page as a filter:

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

This makes execution time independent of table size.

2. Implicit Type Conversion

When a column type does not match the literal, MySQL converts the literal, which can invalidate indexes. Example:

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

Column bpn is VARCHAR(20); MySQL casts it to a number, causing a warning and preventing index use. Ensure literals match column types or cast explicitly.

3. Rewriting Updates/Deletes with JOIN

MySQL 5.6’s materialized sub‑query feature does not accelerate UPDATE/DELETE; they still run as dependent sub‑queries. Rewrite them as JOINs to avoid the costly sub‑query execution.

UPDATE operation o
JOIN (
    SELECT o.id
    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';

The execution plan changes from DEPENDENT SUBQUERY to DERIVED, dropping runtime from seconds to milliseconds.

4. Mixed Ordering Workaround

MySQL cannot use an index for mixed ASC/DESC ordering. When is_reply has only two values, split the query into two UNIONed sub‑queries, each filtering one value and ordering by appraise_time:

SELECT *
FROM (
    SELECT *
    FROM   my_order o
    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
    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;

This reduces execution time from 1.58 s to 2 ms.

5. Replacing EXISTS with JOIN

MySQL executes EXISTS as a dependent sub‑query. Converting it to an inner join eliminates the sub‑query and speeds up the query dramatically.

SELECT *
FROM   my_neighbor n
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;

Runtime drops from ~2 s to 1 ms.

6. Condition Push‑Down Restrictions

Conditions cannot be pushed down into sub‑queries that contain aggregation, LIMIT, UNION, or appear in the SELECT list. Example where the condition is applied after aggregation:

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

Rewrite by pushing the predicate before grouping:

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

7. Early Limiting to Reduce Data Volume

When the final ORDER BY and LIMIT apply to the leftmost table, fetch the limited rows first, then join the remaining tables:

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;

This changes the derived table’s type to DERIVED and cuts execution time to about 1 ms.

8. Pushing Intermediate Result Sets

When a sub‑query produces a large intermediate result, limit it early and use a WITH clause (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) allocated
    FROM   my_resources r
    JOIN   a ON r.resourcesid = a.resourceid
    GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

This reduces runtime from seconds to a few milliseconds.

Summary

MySQL’s query optimizer makes best‑effort decisions, but many patterns—large OFFSET pagination, implicit type casts, sub‑query‑based updates, mixed ordering, and un‑pushed predicates—lead to severe performance degradation. By rewriting queries to use index‑friendly predicates, JOINs instead of sub‑queries, early LIMIT, and CTEs, developers can achieve order‑of‑magnitude speedups across a wide range of workloads.