Boost MySQL Performance: 9 Proven Query Optimization Techniques

1. LIMIT Clause

Pagination often uses LIMIT offset, count, but large offsets force the engine to scan many rows. By using the maximum value of the previous page as a filter, the query becomes independent of total row count.

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 query value type does not match the column type, MySQL converts the value, which can disable index usage. The example shows a VARCHAR(20) column compared to a numeric literal, triggering a warning and a full scan.

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

3. Update/Delete with Subqueries

MySQL 5.6 materializes subqueries only for SELECT. UPDATE/DELETE statements that contain subqueries must be rewritten as JOINs. The original UPDATE uses a DEPENDENT SUBQUERY and runs for seconds; the JOIN version finishes in milliseconds.

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 o.status='applying';

4. Mixed Sorting

MySQL cannot use an index for ORDER BY on columns that are not part of the index. An example query orders by a.is_reply and a.appraise_time, causing a full scan. Splitting the query by a.is_reply values with UNION ALL lets each part use the index, reducing execution time from 1.58 s to about 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 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 20
) t ORDER BY is_reply ASC, appraise_time DESC LIMIT 20;

5. EXISTS Clause

MySQL treats EXISTS as a nested subquery, often leading to a full scan. Rewriting the query as a JOIN eliminates the subquery and drops execution time from ~2 s to 1 ms.

SELECT * FROM my_neighbor n
LEFT 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

Conditions cannot be pushed into certain subqueries such as aggregate subqueries, LIMIT subqueries, UNION subqueries, or subqueries in the SELECT list. The example shows a derived table with GROUP BY where the outer WHERE cannot be pushed, and rewriting the query to apply the filter before aggregation yields a more efficient plan.

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

7. Early Range Reduction

If ordering and limiting apply to the leftmost table, perform them in a subquery first, then join the remaining tables. This drastically reduces the number of rows processed, cutting execution from 12 s to roughly 1 ms.

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;

8. Intermediate Result Pushdown

When a subquery aggregates a large table, push the join condition into the subquery so that only matching rows are aggregated. Using a CTE ( WITH) avoids repeated execution of the same subquery and improves performance 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;

9. Summary

Database query planners generate execution plans, but they are not flawless. Understanding their behavior—avoiding large offsets, matching data types, rewriting subqueries as joins, using CTEs, and applying algorithmic thinking—enables developers to write high‑performance SQL across different DBMS.

Source: https://juejin.cn/post/6844903998974099470