Boost MySQL Performance: Master Pagination, Indexes, JOINs, and Query Rewrite Techniques

01 Pagination Query Optimization

Typical pagination using LIMIT 1000,10 forces the database to scan millions of rows before returning the last ten, because the engine cannot jump directly to the offset. Adding a composite index on the filter columns ( type, name, create_time) helps only when the offset is small.

Solution: use the previous page's maximum create_time as a filter, e.g.:

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 the query time independent of the total row count.

02 Implicit Type Conversion

When a numeric column is compared with a string literal, MySQL converts the column values to numbers, causing index loss. Example:

SELECT *
FROM my_balance b
WHERE b.bpn = 14000000123;

Fix: ensure the literal matches the column type or cast explicitly.

03 Update/Delete Subquery Rewrite

MySQL 5.6’s materialized subquery optimization applies only to SELECTs. UPDATE statements with dependent subqueries become DEPENDENT SUBQUERY and run in seconds. Rewriting the UPDATE as a JOIN changes the plan to DERIVED and reduces execution from seconds to milliseconds.

UPDATE operation o
JOIN (
  SELECT id FROM operation WHERE type='SQLStats' AND name='SlowLog' AND create_time > '2017-03-16 14:00:00' LIMIT 10
) t ON o.id = t.id
SET o.status = 'applying';

04 Mixed Sorting Limitation

MySQL cannot use an index for mixed ASC/DESC sorting. A workaround is to split the query into two parts and sort only the necessary column, dramatically reducing execution time.

05 EXISTS Clause Replacement

MySQL still executes EXISTS as a nested subquery. Converting the EXISTS to an explicit JOIN eliminates the nested loop and drops execution from ~2 seconds to 1 ms.

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

06 Condition Push‑Down

Conditions cannot be pushed into complex derived tables, UNIONs, or LIMIT subqueries. When a condition can be pushed, rewrite the query so the filter is applied before the subquery materializes, e.g.:

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

07 Early Limit (Pre‑Sorting)

When the final ORDER BY and LIMIT apply to the leftmost table, sort that table first, then join. This reduces the rows processed in later joins from hundreds of thousands to a few dozen.

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;

08 Intermediate Result Push‑Down

When a derived table only needs a subset of columns for a later join, filter and aggregate it early. Using WITH (CTE) makes the intent clear and avoids repeated subqueries.

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

09 Summary

Database compilers generate execution plans, but they are not perfect. Understanding their behavior—index usage, condition push‑down, subquery materialization, and the limits of certain constructs—allows developers to write high‑performance SQL. Adopt CTEs ( WITH) for clarity, push filters as early as possible, and prefer JOINs over nested subqueries to keep the workload light.