How to Supercharge MySQL Queries: 8 Proven Optimization Techniques

1. LIMIT statement

Pagination is a frequent scenario but often problematic. A typical DBA might add a composite index on type, name, and create_time to let the optimizer use the index for sorting, improving performance.

However, when the LIMIT clause becomes LIMIT 1000000,10, developers complain that fetching only 10 rows is still slow because MySQL must scan from the beginning to locate the millionth row, even with an index.

In front‑end pagination or large‑batch export cases, the maximum value of the previous page can be used as a query condition, rewriting the SQL to keep execution time constant regardless of data size.

2. Implicit conversion

When query variables and column types mismatch, MySQL implicitly converts strings to numbers before comparison, causing index loss. For example, comparing a VARCHAR(20) column bpn with a numeric value forces a conversion, rendering the index ineffective.

3. Join update/delete

MySQL 5.6 introduced materialized subquery optimization for SELECT, but UPDATE and DELETE still require manual rewriting to JOIN. An UPDATE that uses a dependent subquery can be rewritten as a JOIN, changing the execution plan from a dependent subquery to a derived table, reducing runtime from seconds to milliseconds.

4. Mixed sorting

MySQL cannot use an index for mixed sorting, but special techniques can improve performance. By rewriting the query to avoid full‑table scans, execution time can drop from over a second to a few milliseconds.

5. EXISTS statement

MySQL executes EXISTS clauses as nested subqueries. Replacing EXISTS with a JOIN eliminates the nested subquery, cutting execution time from nearly two seconds to about one millisecond.

6. Condition pushdown

External query conditions cannot be pushed down into complex views or subqueries such as aggregate subqueries, subqueries with LIMIT, UNIONs, or scalar subqueries. By rewriting the query to push conditions into the inner query, performance improves dramatically.

Aggregate subqueries

Subqueries containing LIMIT

UNION or UNION ALL subqueries

Scalar subqueries in SELECT list

7. Early range reduction

When the final WHERE clause and ORDER BY target the leftmost table, sorting that table first reduces the data set before performing the LEFT JOIN, shrinking execution time from 12 seconds to about 1 ms.

8. Intermediate result pushdown

In a LEFT JOIN where the subquery produces a large aggregation, only rows matching the main table are needed. Rewriting the query with a WITH clause and pushing the join condition into the subquery reduces runtime from seconds to a few milliseconds.

Summary

The database compiler generates execution plans that dictate how SQL runs, but it is not perfect. Understanding its behavior and rewriting queries—using index‑friendly pagination, avoiding implicit conversions, replacing subqueries with JOINs, pushing conditions down, and employing WITH clauses—can dramatically improve performance across many databases.