Boost MySQL Performance: 9 Proven SQL Optimization Techniques

1. LIMIT clause

Pagination queries are common but can be slow when using large offsets. Adding a composite index on type, name, create_time helps, but LIMIT 1000000,10 still forces a full scan. Rewriting the query to use the previous page's maximum create_time makes 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 converts the string to a number, causing index loss. Example with varchar(20) column bpn being compared to a numeric literal leads to a warning and full scan.

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

3. Update/Delete with JOIN

MySQL 5.6 materialized subqueries only optimize SELECT. UPDATE/DELETE with IN subqueries become dependent subqueries and are slow. Rewriting them as JOINs changes the execution plan from DEPENDENT SUBQUERY to DERIVED, reducing runtime from seconds to milliseconds.

UPDATE operation o JOIN (SELECT id FROM operation WHERE `group`=123 AND status NOT IN ('done') ORDER BY parent, id LIMIT 1) t ON o.id = t.id SET status='applying';

4. Mixed ordering

MySQL cannot use indexes for mixed ORDER BY, but splitting the query with UNION ALL on the is_reply flag and ordering each part can reduce execution time dramatically.

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, appraisetime DESC LIMIT 20;

5. EXISTS clause

EXISTS is executed as a nested subquery. Converting it to a JOIN eliminates the subquery and cuts 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. Predicate pushdown

Conditions cannot be pushed down through aggregation, LIMIT, UNION, or subqueries in SELECT list. By moving the predicate into the outer query before grouping, the plan becomes a simple index lookup.

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

7. Early range reduction

When ordering and filtering apply to the leftmost table, sort and limit can be applied before joining, turning a 12‑second sort into a sub‑millisecond query.

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 pushdown

Using a WITH clause to materialize a small subquery and joining it to an aggregated query avoids scanning the whole table and reduces 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) allocated FROM my_resources r, a WHERE r.resourcesid=a.resourcesid GROUP BY resourcesid) c ON a.resourceid=c.resourcesid;

9. Summary

The database optimizer generates execution plans, but understanding its limitations and applying techniques such as predicate pushdown, JOIN rewrites, and WITH clauses enables writing high‑performance SQL.