Boost MySQL Performance: Practical Tips for LIMIT, Implicit Conversion, Joins, and More

1. Optimizing LIMIT Queries

Pagination often suffers from performance issues when using large offsets, e.g., LIMIT 1000000,10. Adding a composite index on type, name, and create_time helps, but the real fix is to use the previous page's maximum create_time 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 keeps query time constant regardless of table size.

2. Avoiding Implicit Type Conversion

Comparing a numeric literal with a VARCHAR column forces MySQL to convert the column to a number, disabling index usage:

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

Ensure the literal matches the column type (e.g., quote the value) to keep the index effective.

3. Rewriting UPDATE/DELETE with JOIN

MySQL 5.6’s materialized subquery optimization applies only to SELECTs. For UPDATEs, rewrite dependent subqueries as JOINs to avoid the costly DEPENDENT SUBQUERY plan:

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 o.status = 'applying';

The plan changes to DERIVED, reducing execution from seconds to milliseconds.

4. Handling Mixed Ordering

When ordering by a low‑cardinality column (e.g., is_reply), split the query into two UNION ALL parts, each filtering on a specific value, then combine and order the results:

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 from 1.58 s to 2 ms.

5. Replacing EXISTS with JOIN

MySQL often executes EXISTS as a nested subquery. Converting it to a direct JOIN eliminates the subquery 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;

Execution time drops from ~2 s to ~1 ms.

6. Pushing Down Conditions

Conditions cannot be pushed into subqueries that contain aggregation, LIMIT, UNION, or scalar subqueries. Rewrite the query so the filter is applied before aggregation:

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

The plan changes from a derived table scan to a simple indexed lookup.

7. Early Row Limiting

When the final ORDER BY and LIMIT apply to the leftmost table, sort and limit that table first, then perform the joins. This reduces the intermediate row count dramatically:

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;

Execution time falls to around 1 ms.

8. Pushing Down Intermediate Result Sets

For queries that join a large aggregated subquery, filter the main table first and use a WITH clause to materialize the small set, then join:

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 execution from seconds to a few milliseconds.

Conclusion

The MySQL optimizer generates execution plans that heavily influence query performance, but it is not perfect. Understanding its behavior—especially around LIMIT offsets, type conversion, subquery materialization, and condition push‑down—allows developers to write SQL that avoids common pitfalls and achieves near‑optimal performance across databases.