Boost MySQL Performance: 8 Proven Tricks to Slash Query Times

1. LIMIT Statement

Pagination is a frequent scenario, but using a large offset like LIMIT 1000000,10 can be extremely slow because the database must scan from the beginning even with indexes. Rewriting the query to use the maximum value from the previous page as a condition keeps execution time constant regardless of data size.

2. Implicit Conversion

Mismatched types between query variables and column definitions cause MySQL to convert strings to numbers, leading to index loss. This often happens when frameworks automatically fill parameters, so developers must be cautious of hidden type conversions.

3. Join Updates and Deletes

MySQL 5.6’s materialized subquery feature only optimizes SELECT statements. UPDATE or DELETE statements that rely on subqueries execute as dependent subqueries, causing severe slowdown. Rewriting them as JOINs transforms the execution plan to DERIVED, reducing runtime from seconds to milliseconds.

4. Mixed Sorting

MySQL cannot use indexes for mixed sorting, often resulting in full table scans. By redesigning the query to filter on the binary flag is_reply first, execution time dropped from 1.58 seconds to 2 milliseconds.

5. EXISTS Clause

MySQL still treats EXISTS as a nested subquery. Replacing it with a JOIN eliminates the subquery, cutting execution time from 1.93 seconds to 1 millisecond.

6. Predicate Pushdown

External query predicates cannot be pushed down into complex views or subqueries such as aggregate subqueries, LIMIT subqueries, UNIONs, or subqueries in SELECT lists. By rewriting the query to push predicates into the outer query, the execution plan improves dramatically.

7. Early Range Reduction

When the final WHERE clause and ORDER BY target the leftmost table, sorting that table first and then performing the LEFT JOIN reduces the data set early, shrinking execution time from 12 seconds for 900 k rows to about 1 millisecond.

8. Intermediate Result Pushdown

For queries with subqueries that aggregate large tables, pushing the join condition into the subquery and using a WITH clause simplifies the plan and reduces runtime from seconds to milliseconds.

Conclusion

The database optimizer generates execution plans that dictate how SQL runs, but it is not perfect. Understanding its behavior and applying techniques such as using max values, rewriting subqueries as JOINs, employing WITH clauses, and pushing predicates can dramatically improve performance across many databases.