How to Supercharge MySQL Queries: 9 Proven Optimization Techniques

1. LIMIT clause

Pagination is a frequent scenario, but using a large offset (e.g., LIMIT 1000000,10) forces the database to scan many rows, causing slowdown.

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

Rewrite the query to use the maximum value of the previous page as a condition, eliminating the large offset.

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 type does not match the query variable type, MySQL may convert the column, causing index loss.

EXPLAIN EXTENDED SELECT *
FROM my_balance b
WHERE b.bpn = 14000000123
  AND b.isverified IS NULL;
SHOW WARNINGS;
-- Warning: Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn'

The column bpn is defined as VARCHAR(20); MySQL converts the string to a number, disabling the index.

3. Join‑based update/delete

MySQL 5.6 introduced materialized sub‑query optimization for SELECT only. For UPDATE/DELETE, rewrite the statement as a JOIN to avoid dependent sub‑queries.

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

Execution plan before rewrite shows a DEPENDENT SUBQUERY with high cost. After rewriting as a JOIN, the plan becomes DERIVED and runs in milliseconds.

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

4. Mixed sorting

MySQL cannot use an index for mixed ASC/DESC sorting. By separating the query into two parts—one for each sort direction—the optimizer can use indexes.

SELECT *
FROM my_order o
INNER JOIN my_appraise a ON a.orderid = o.id
WHERE a.is_reply = 0
ORDER BY a.appraise_time DESC
LIMIT 0, 20
UNION ALL
SELECT *
FROM my_order o
INNER JOIN my_appraise a ON a.orderid = o.id
WHERE a.is_reply = 1
ORDER BY a.appraise_time DESC
LIMIT 0, 20
ORDER BY is_reply ASC, appraise_time DESC
LIMIT 20;

5. EXISTS clause

MySQL executes EXISTS as a nested sub‑query, which is slow. Rewriting it as a JOIN removes the nesting.

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

6. Condition push‑down

Conditions cannot be pushed down into complex views or sub‑queries such as aggregation, LIMIT, UNION, or sub‑queries in the SELECT list.

Aggregation sub‑query

Sub‑query with LIMIT

UNION / UNION ALL sub‑query

Sub‑query in output fields

Example before push‑down:

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

After moving the condition before the aggregation:

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

7. Early range reduction

When the final WHERE and ORDER BY apply to the leftmost table, sort that table first and then join, drastically reducing rows processed.

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 push‑down

For a query that joins a large aggregated sub‑query, filter the sub‑query early using the same condition as the outer query.

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
  JOIN a ON r.resourcesid = a.resourceid
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

9. Summary

The database query planner generates execution plans that determine how SQL runs, but it is not perfect. Understanding its behavior helps avoid common pitfalls and write high‑performance SQL.

Key takeaways:

Design queries with algorithmic thinking.

Prefer WITH (CTE) for complex statements to improve readability and performance.

Push conditions as early as possible, avoid large offsets, and replace sub‑queries with joins when feasible.