Boost MySQL Performance: 8 Proven SQL Optimization Techniques

1. LIMIT Clause

Pagination often causes performance issues. A simple query with LIMIT 1000, 10 forces MySQL to scan from the beginning to locate the millionth row, even if an index exists. Rewriting the query to use the previous page's maximum create_time as a filter eliminates the scan.

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 Type Conversion

When a column type does not match the literal type, MySQL converts the literal, which can invalidate index usage. For example, comparing a VARCHAR(20) column bpn with a numeric literal causes a warning and forces a full scan.

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

3. Join‑Based Updates and Deletes

MySQL 5.6’s materialized subquery optimization does not apply to UPDATE/DELETE statements, so they are executed as dependent subqueries. Rewriting them as JOINs changes the execution plan from DEPENDENT SUBQUERY to DERIVED, reducing execution time 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 status = 'applying';

4. Mixed Ordering

MySQL cannot use an index for mixed ASC/DESC ordering. By separating the query into two UNION ALL subqueries—one for each is_reply value—the optimizer can use the index on appraise_time, dropping execution time 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 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. Replacing EXISTS with JOIN

MySQL executes EXISTS as a nested subquery, which is inefficient. Converting the pattern to an explicit INNER JOIN removes the subquery and cuts 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

Conditions cannot be pushed down into subqueries that contain aggregation, LIMIT, UNION, or appear in the SELECT list. By moving the filter target = 'rm-xxxx' into the outer query, the plan changes from a derived table scan to a simple indexed lookup.

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

7. Early Data Reduction

When the final ORDER BY and LIMIT apply to the leftmost table, sorting can be performed before joining. Wrapping the initial query in a derived table reduces the rows processed by subsequent joins, shrinking 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. Pushing Down Intermediate Results

When a subquery produces a large intermediate result (e.g., a full‑table aggregation), joining it directly can be costly. By first limiting the primary set (using WITH or a derived table) and then joining only matching rows, execution time drops 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

MySQL’s query optimizer determines the actual execution plan, but it is not perfect. Understanding its limitations—such as how it handles LIMIT, implicit conversions, joins, ordering, EXISTS, and condition push‑down—allows developers to rewrite SQL for dramatically better performance across different database systems.