Common MySQL Query Performance Issues and Optimization Techniques

Pagination queries often suffer when using large offsets in LIMIT clauses; even with appropriate indexes, MySQL must scan from the beginning. Rewriting the query to filter by the previous page's maximum create_time eliminates the offset scan and keeps execution time constant.

Implicit type conversion, such as comparing a VARCHAR column to a numeric literal, forces MySQL to convert values and disables index usage. Ensuring matching data types in predicates preserves index efficiency.

Update and delete statements that embed sub‑queries (e.g., WHERE id IN (SELECT ...)) trigger DEPENDENT SUBQUERY execution plans, leading to severe slowdown. Converting these to JOIN forms changes the plan to DERIVED, reducing execution time from seconds to milliseconds.

MySQL cannot use indexes for mixed ordering (e.g., ordering by is_reply and appraise_time). Splitting the query with UNION ALL for each is_reply value allows each part to use an index, cutting runtime from over a second to a few milliseconds.

The EXISTS clause often results in nested sub‑queries; rewriting it as a JOIN removes the sub‑query and improves performance dramatically (e.g., from 1.93 s to 1 ms).

External conditions cannot be pushed down into complex views or sub‑queries such as aggregate sub‑queries, LIMIT sub‑queries, or UNION constructs. By moving the filter condition before the GROUP BY, the plan changes from a derived table scan to a simple indexed lookup.

When the final ORDER BY and WHERE clauses target the leftmost table, sorting and limiting that table first (using a sub‑query) drastically reduces the row count before joins, shrinking execution time from seconds to about one millisecond.

Intermediate result set push‑down can be further optimized by using a WITH clause (common table expression) to materialize reusable sub‑queries once, eliminating repeated scans and cutting execution time from seconds to milliseconds.

Overall, understanding MySQL's query planner limitations and applying these rewrite patterns—index‑friendly predicates, join‑based updates, early limiting, and CTEs—helps developers write high‑performance SQL statements.