Boost MySQL Performance: 8 Proven SQL Optimization Techniques

Pagination is a common but error‑prone scenario; a simple query with LIMIT 1000000,10 can be slow because the database must scan from the start even with indexes. Rewriting the query to use the previous page's maximum value as a filter makes execution time essentially constant regardless of data size.

1. LIMIT Clause Redesign

Replace offset‑based LIMIT with a condition on the last retrieved key, eliminating full scans.

2. Implicit Type Conversion

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

3. Update/Delete via JOIN

MySQL 5.6’s materialized subquery optimization applies only to SELECTs. For UPDATE/DELETE, rewrite statements as JOINs to avoid dependent subqueries, reducing execution from seconds to milliseconds.

4. Mixed Sorting

MySQL cannot use indexes for mixed orderings, but specific tricks (e.g., adding covering indexes or restructuring conditions) can dramatically cut execution time, as shown by before‑and‑after plans.

5. EXISTS Replacement

EXISTS often triggers nested subqueries; converting to JOIN eliminates the subquery and can drop execution time from ~2 s to ~1 ms.

6. Condition Push‑Down

External predicates cannot be pushed into complex views or subqueries such as aggregates, LIMIT‑containing subqueries, UNIONs, or scalar subqueries. When push‑down is possible, rewrite the query so the optimizer can apply filters early, improving performance.

7. Early Data Reduction

When the final WHERE clause and ORDER BY target the leftmost table, sort that table first and then perform LEFT JOINs. This reduces rows processed from hundreds of thousands to a few, cutting runtime from seconds to ~1 ms.

8. Intermediate Result Set Push‑Down

For queries with repeated subqueries, use a WITH clause (CTE) to materialize once and reference multiple times, simplifying the plan and reducing execution time from seconds to milliseconds.

Overall, understanding the database compiler’s limitations and applying these techniques enables developers to write high‑performance SQL across various database systems.