How to Supercharge MySQL Queries: 8 Proven Optimization Techniques

1. LIMIT Clause

Pagination is a frequent use case that can easily become a performance bottleneck. A naïve query such as

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

forces the engine to scan from the beginning even when an index exists, because the database does not know where the million‑th row starts. Rewriting the query to use the previous page's maximum timestamp eliminates the offset:

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 constant regardless of table size.

2. Implicit Type Conversion

When a column type does not match the literal type, MySQL converts the column value, which can invalidate indexes. For example:

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

MySQL converts bpn (VARCHAR) to a number, causing a full index scan. The fix is to ensure the literal matches the column type or cast explicitly.

3. Update/Delete with Joins

MySQL 5.6 introduced materialized sub‑queries for SELECT only; UPDATE/DELETE still use dependent sub‑queries, leading to poor performance. An example update:

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

produces a DEPENDENT SUBQUERY plan. Rewriting it as a JOIN changes the plan to DERIVED and drops execution time from seconds to 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 status = 'applying';

4. Mixed Sorting

MySQL cannot use an index for mixed ASC/DESC sorting. The original query:

SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id ORDER BY a.is_reply ASC, a.appraise_time DESC LIMIT 0, 20;

results in a full table scan. Splitting the query by the binary is_reply flag and UNION‑ALLing the two parts allows the optimizer to use indexes, reducing runtime from 1.58 s to 2 ms.

5. EXISTS Clause

MySQL treats EXISTS as a nested sub‑query. The following query:

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;

produces a DEPENDENT SUBQUERY plan. Rewriting it as a straight JOIN eliminates the sub‑query and drops execution time from 1.93 s to 1 ms.

6. Condition Push‑Down

External predicates cannot be pushed into certain sub‑queries, such as aggregation, LIMIT, UNION, or scalar sub‑queries. Example:

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

After moving the predicate into the derived table, the plan becomes a simple index lookup, dramatically improving performance.

7. Early Range Reduction

When the final ORDER BY and WHERE clauses target the leftmost table, sorting can be performed before the joins. The original query scans ~900 k rows before sorting:

SELECT * FROM my_order o LEFT JOIN my_userinfo u ON o.uid = u.uid LEFT JOIN my_productinfo p ON o.pid = p.pid WHERE o.display = 0 AND o.ostaus = 1 ORDER BY o.selltime DESC LIMIT 0, 15;

By first selecting and sorting the my_order rows, then joining the smaller result set, execution time drops to about 1 ms.

8. Intermediate Result Push‑Down

When a sub‑query returns a large intermediate result, pushing the join condition into the sub‑query reduces work. The original query joins a large my_resources aggregation with a sub‑query that is executed multiple times. Using a WITH clause to materialize the sub‑query once and then joining eliminates redundant scans, cutting runtime from seconds to milliseconds.

Conclusion

The MySQL optimizer generates execution plans, but it is not perfect. Understanding its behavior—especially how it handles LIMIT offsets, type conversion, sub‑query materialization, and predicate push‑down—allows developers to write SQL that avoids common pitfalls and achieves high performance.