Turn Slow MySQL Queries into Millisecond Performance with Smart Rewrites

1. LIMIT Pagination

Using a large offset in LIMIT forces MySQL to scan many rows before discarding them, which remains slow even with indexes. A better approach is to use the maximum value of the previous page as a filter, making the query time independent of total 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 Type Conversion

When a column defined as VARCHAR is compared with a numeric literal, MySQL converts the string to a number, causing the index on that column to be ignored. The fix is to ensure the compared values have matching types.

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

3. Update/Delete with JOIN

MySQL 5.6 materializes only SELECT statements; an UPDATE that contains a sub‑query is executed as a DEPENDENT SUBQUERY, which is very slow. Rewriting the UPDATE as a JOIN changes the execution plan to DERIVED and reduces runtime from seconds to milliseconds.

UPDATE operation o
JOIN (
    SELECT id
    FROM (
        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
) t ON o.id = t.id
SET status = 'applying';

4. Mixed Sorting

MySQL cannot use an index when the ORDER BY mixes ASC and DESC on different columns. By splitting the query with UNION ALL for each is_reply value, the optimizer can use a simple index scan and the execution time drops from seconds to a few milliseconds.

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

The EXISTS construct is implemented as a dependent sub‑query, which is inefficient. Converting it to an explicit JOIN removes the sub‑query and cuts execution time from about two seconds to one millisecond.

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 Pushdown

Conditions cannot be pushed into sub‑queries that contain aggregation, LIMIT, UNION, or other complex constructs. When a condition can be applied before aggregation, rewrite the query so the filter is evaluated first, dramatically improving the plan.

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

7. Early Range Reduction

If the WHERE clause and ORDER BY apply to the leftmost (driving) table, sorting and limiting that table before performing the joins reduces the number of rows processed downstream. The rewritten query runs in about one millisecond instead of over ten seconds.

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 (CTE)

Repeated sub‑queries can be factored out with a Common Table Expression ( WITH) to avoid redundant scans. The example shows how moving the filtered list of resourceid into a CTE and joining it once reduces execution time from seconds to a few 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
    WHERE r.resourcesid IN (SELECT resourceid FROM a)
    GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

Conclusion

Understanding how MySQL's query compiler builds execution plans is essential for writing high‑performance SQL. By applying algorithmic thinking—using appropriate indexes, rewriting sub‑queries as joins, pushing filters early, and leveraging CTEs—developers can dramatically reduce database load and achieve millisecond‑level response times.