How to Supercharge MySQL Queries: 8 Proven Optimization Techniques

1. LIMIT Clause

Pagination is a frequent scenario but often a source of performance problems. A naïve query with LIMIT 1000,10 forces MySQL to scan from the beginning even if indexes exist, causing slow execution.

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

Rewriting the query to use the maximum value of the previous page as a condition eliminates the need for a 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;

The new design yields a query time that remains constant regardless of table size.

2. Implicit Type Conversion

When a column’s type does not match the literal in the WHERE clause, MySQL may convert the column value, causing index loss. For example, a VARCHAR(20) column compared with a numeric literal forces a conversion and prevents index usage.

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

Such mismatches often arise from automatically generated parameters in application frameworks.

3. Join‑Based Updates and Deletes

MySQL 5.6 introduced materialized subqueries for SELECTs, but UPDATE/DELETE statements still use dependent subqueries, which are slow. Converting them to JOINs changes the execution plan from DEPENDENT SUBQUERY to DERIVED, dramatically improving speed.

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

Rewritten as:

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 Ordering

MySQL cannot use an index for mixed ORDER BY clauses. By separating the two possible values of is_reply (0 and 1) into two UNIONed subqueries, each can use an index and the overall execution time drops from seconds to milliseconds.

SELECT *
FROM (
  SELECT *
  FROM my_order o
  INNER JOIN my_appraise a ON a.orderid = o.id
  WHERE 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
  WHERE 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 executes EXISTS as a dependent subquery. Replacing it with an explicit JOIN removes the subquery and reduces execution time from ~2 seconds to 1 ms.

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

Rewritten as:

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 Push‑Down

Conditions cannot be pushed down into subqueries that involve aggregation, LIMIT, UNION, or scalar subqueries. By moving the filter into the outer query, the optimizer can apply the condition earlier, improving performance.

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

Rewritten as:

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

7. Early Result Limiting

When the final ORDER BY and LIMIT apply to the leftmost table, sorting and limiting before joining can drastically reduce the amount of data 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 Set Push‑Down

When a subquery produces a large intermediate result, limiting it early and using a WITH clause can avoid unnecessary work. The example shows rewriting a join with a derived table into a CTE, reducing execution time from seconds to 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
  JOIN a ON r.resourcesid = a.resourceid
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

Summary

The database compiler generates execution plans that dictate how SQL statements run, but it is not infallible. Understanding its behavior, avoiding common pitfalls, and writing clear, algorithm‑driven SQL—often using CTEs—helps produce high‑performance queries across different database systems.