8 MySQL Tricks to Supercharge Query Performance and Slash Execution Time

Pagination and other frequent query patterns often become performance bottlenecks in MySQL because the optimizer cannot efficiently locate the starting row for large OFFSET values or may apply costly implicit conversions.

1. Optimizing LIMIT with Large Offsets

Instead of using LIMIT 1000000,10, retrieve the maximum key from the previous page and use it as a filter, e.g., SELECT * FROM t WHERE id > :last_id ORDER BY id LIMIT 10. This keeps the query time constant regardless of table size.

2. Avoiding Implicit Type Conversion

When a numeric column is compared with a string (e.g., a VARCHAR(20) field compared to a number), MySQL converts the string to a number, causing index loss. Ensure matching data types or cast explicitly to preserve index usage.

3. Rewriting UPDATE/DELETE with JOIN

MySQL 5.6’s materialized subquery feature only optimizes SELECTs. For UPDATE or DELETE, rewrite the statement as a JOIN to avoid dependent subqueries. This can shrink execution time from several seconds to a few milliseconds.

4. Mixed Ordering Work‑arounds

MySQL cannot use an index for mixed ORDER BY clauses, but in some cases a composite index or a covering index can still improve performance. The article shows an example where rewriting the query reduces a full‑table scan to an index‑guided execution.

5. Replacing EXISTS with JOIN

MySQL often executes EXISTS as a nested subquery. Converting the EXISTS clause to an equivalent JOIN eliminates the subquery, cutting execution time from ~2 seconds to ~1 ms.

6. Condition Push‑Down

Conditions cannot be pushed down into complex views or subqueries that involve aggregation, LIMIT, UNION, or output‑field subqueries. When push‑down is possible, rewriting the query yields dramatically better plans.

Aggregated subqueries

Subqueries containing LIMIT

UNION / UNION ALL subqueries

Subqueries in SELECT list

7. Early Data Reduction

When the final WHERE clause and ORDER BY apply only to the leftmost table, sort that table first and then join. This reduces the row count early, turning a 12‑second scan of 900 k rows into a sub‑millisecond execution.

8. Pushing Down Intermediate Result Sets

By ensuring the main table’s filtering predicates are applied before joining, even complex subqueries can be materialized (DERIVED) and executed efficiently, reducing execution time from seconds to a few milliseconds.

In summary, MySQL’s query optimizer does its best, but developers must understand its limits and rewrite queries—using proper indexes, avoiding large OFFSETs, matching data types, converting subqueries to joins, and pushing predicates early—to achieve high‑performance SQL.