Boost MySQL Performance: 8 Proven SQL Optimization Techniques

1. LIMIT clause

Pagination is a frequent scenario that often causes performance issues. A simple query with LIMIT 1000, 10 can be slow because the database must scan from the beginning to locate the offset. Rewriting the query to use the previous page's maximum value as a condition eliminates this overhead:

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 execution time becomes stable regardless of data size.

2. Implicit conversion

When query variables and column types mismatch, MySQL implicitly converts strings to numbers, causing index loss. For example:

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

The warning indicates that the index on bpn cannot be used due to type conversion.

3. Join updates and deletes

MySQL 5.6’s materialized view feature only optimizes SELECT statements. UPDATE or DELETE statements with subqueries can lead to dependent subqueries and poor performance. Rewriting with JOIN transforms the plan to DERIVED and speeds up execution dramatically:

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

4. Mixed sorting

MySQL cannot use indexes for mixed ORDER BY directions. By splitting the query with UNION ALL based on the is_reply flag, the execution time drops from seconds 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

Using EXISTS often results in dependent subqueries. Rewriting it as a JOIN removes the nested subquery and reduces execution time from seconds to milliseconds:

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 query conditions cannot be pushed down into complex views or subqueries such as aggregation, LIMIT, UNION, or subqueries in output fields. By moving the condition into the inner 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 result limiting

When the final WHERE clause and ORDER BY apply to the leftmost table, sort and limit can be performed early to reduce the data set before joins. The rewritten query limits the my_order table first, cutting execution time to around 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 set pushdown

For a query with a subquery that aggregates a large table, limiting the intermediate result set with a WITH clause avoids full table scans and reduces 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
  WHERE r.resourcesid = a.resourceid
  GROUP BY resourcesid
) c ON a.resourceid = c.resourcesid;

Summary

The database compiler generates execution plans that determine how SQL runs, but it is not perfect. Understanding its characteristics helps avoid pitfalls and write high‑performance SQL. Using clear, algorithm‑driven query designs—such as employing WITH for complex statements—reduces database load and improves efficiency.