Boost MySQL Performance: 8 Proven Query Optimization Techniques

1. LIMIT statement

Pagination is common but can be slow when using large offsets. The article shows a basic query and explains why

LIMIT 1000000,10

is inefficient because MySQL must scan from the beginning.

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

To avoid this, use the maximum value of the previous page as a condition, e.g.:

<code>SELECT * FROM operation WHERE type='SQLStats' AND name='SlowLog' AND create_time > '2017-03-16 14:00:00' ORDER BY create_time LIMIT 10;</code>

This makes query time independent of total rows.

2. Implicit conversion

When a column type does not match the literal, MySQL converts the string to a number, causing index loss. Example with

varchar(20)

column

bpn

compared to a numeric literal leads to a warning and full scan.

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

3. Update/Delete with join

MySQL 5.6 supports materialized subqueries for SELECT but UPDATE/DELETE still use dependent subqueries. Rewriting an UPDATE with JOIN dramatically reduces execution time.

<code>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);</code>

Rewritten as:

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

4. Mixed sorting

MySQL cannot use an index for mixed ASC/DESC sorting. By splitting the query into two parts based on

is_reply

value and

UNION ALL

, performance improves from seconds to milliseconds.

<code>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;</code>

5. EXISTS clause

EXISTS is executed as a nested subquery. Converting it to a JOIN removes the subquery and cuts execution time dramatically.

<code>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;</code>

Rewritten:

<code>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;</code>

6. Condition pushdown

Conditions cannot be pushed down through certain subqueries such as aggregates, LIMIT, UNION, or scalar subqueries. Example shows a derived table with GROUP BY where the outer WHERE can be pushed down.

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

Rewritten as:

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

7. Early limit

When the final ORDER BY and WHERE apply to the leftmost table, apply them first in a subquery to reduce rows before joins. This changes execution time from seconds to milliseconds.

<code>SELECT * FROM (SELECT * FROM my_order o WHERE o.display=0 AND o.ostaus=1 ORDER BY o.selltime DESC LIMIT 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 15;</code>

8. Intermediate result pushdown

When a subquery returns many rows but only a few are needed for the final join, push the join condition into the subquery or use WITH to avoid repeated scans.

<code>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) allocated FROM my_resources r WHERE r.resourcesid IN (SELECT resourceid FROM a) GROUP BY resourcesid) c ON a.resourceid=c.resourcesid;</code>

Summary

Database query planners generate execution plans, but they are not perfect. Understanding their behavior and rewriting SQL—using techniques like keyset pagination, implicit conversion avoidance, JOIN‑based updates, UNION‑ALL for mixed sorting, EXISTS replacement, condition pushdown, early LIMIT, and CTEs—can dramatically improve performance.