8 Proven MySQL Tricks to Supercharge Your Queries

1. LIMIT Clause

Pagination often uses LIMIT offset, count, but large offsets force the engine to scan from the start. Rewriting the query to use the previous page's maximum value as a condition (e.g., WHERE create_time > '2017-03-16 14:00:00') makes execution time independent of table 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 Conversion

When a column’s type (e.g., VARCHAR(20)) does not match the literal’s type, MySQL converts the string to a number, causing index loss. Ensure matching types or cast explicitly to keep indexes usable.

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

3. Join Updates / Deletes

MySQL 5.6’s materialized subquery optimization applies only to SELECTs. For UPDATE/DELETE, rewrite the statement as a JOIN to avoid dependent subqueries and dramatically improve speed.

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

4. Mixed Sorting

MySQL cannot use indexes for mixed ORDER BY columns. Splitting the query into two UNION ALL parts, each filtering on a single is_reply value, reduces a full‑table scan to 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, appraise_time DESC LIMIT 20;

5. EXISTS Clause

MySQL treats EXISTS as a nested subquery, which is slow. Replacing it with a JOIN eliminates the dependent subquery and cuts execution time from seconds to a 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

External WHERE conditions cannot be pushed into complex views or subqueries such as aggregates, LIMIT‑containing subqueries, or UNIONs. When possible, move the condition before the subquery to let the optimizer apply indexes.

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

7. Early Row Limiting

When the final WHERE clause and ORDER BY apply to the leftmost table, sort and limit that table first, then join the rest. This reduces the scanned rows 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;

8. Intermediate Result Pushdown

For queries that join a large aggregated subquery, rewrite using a WITH clause (CTE) to materialize the small set once and join it, turning a multi‑second execution into 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 = a.resourcesid
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

Source: http://mysql.taobao.org/monthly/2016/07/08

Understanding how MySQL’s optimizer generates execution plans and the limitations of each strategy enables developers to write high‑performance SQL, avoid common pitfalls, and leverage features such as CTEs for clearer, more efficient queries.