Boost MySQL Performance: 8 Proven Query Optimization Techniques

1. LIMIT clause

Pagination often causes performance issues; using a large offset like LIMIT 1000000,10 forces the database to scan many rows. Rewriting the query to filter by the last seen value (e.g., create_time > '2017-03-16 14:00:00') keeps execution time constant.

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 value, MySQL may convert the column, causing index loss. For example, comparing a VARCHAR(20) column bpn with a numeric literal forces a conversion and prevents index usage.

3. Update/Delete with JOIN

MySQL 5.6 executes sub‑queries in UPDATE/DELETE as dependent subqueries, which are slow. Rewriting them as JOINs changes the execution plan to DERIVED and can cut runtime from seconds to 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 ORDER BY columns. By separating the query into two UNION ALL parts—one for each is_reply value—and ordering each part individually, execution time drops from 1.58 s to 2 ms.

SELECT * FROM (
  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
) t
ORDER BY is_reply ASC, appraise_time DESC
LIMIT 20;

5. EXISTS clause

MySQL treats EXISTS as a nested subquery, which is inefficient. Replacing it with an INNER JOIN eliminates the subquery and reduces execution time from 1.93 s to 1 ms.

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

External conditions cannot be pushed into certain subqueries (e.g., aggregates, LIMIT, UNION). By moving the filter into the outer query, the plan changes from DERIVED to a simple SELECT with index usage.

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

7. Early result narrowing

When the final ORDER BY and WHERE apply to the leftmost table, sort and limit that table first, then join the remaining tables. This reduces the scanned rows dramatically, cutting execution time from ~12 s to ~1 ms.

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 subquery, limit the intermediate result set by joining only the needed rows. Using a WITH clause to materialize the small set first can shrink runtime 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

Understanding how MySQL’s query optimizer generates execution plans is essential for writing high‑performance SQL. By avoiding large offsets, ensuring type‑matched predicates, rewriting subqueries as joins, leveraging early limiting, and using CTEs, developers can dramatically improve query speed across many databases.