MySQL Query Optimization Techniques and Common Pitfalls

Pagination with LIMIT is a common scenario, but using large offsets (e.g., LIMIT 1000000,10 ) forces the database to scan many rows even with indexes. Rewriting the query to use the previous page's maximum value as a filter (e.g., WHERE create_time > '2017-03-16 14:00:00' ) keeps execution time constant regardless of table size.

Implicit type conversion can also degrade performance. When a numeric literal is compared to a VARCHAR column, MySQL converts the column to a number, causing index loss. Ensuring matching data types in predicates avoids this pitfall.

Updates or deletes that rely on sub‑queries often generate DEPENDENT SUBQUERY execution plans. Converting such statements to JOIN forms changes the plan to DERIVED , reducing execution time from seconds to milliseconds.

MySQL cannot use indexes for mixed sorting (different sort directions on multiple columns). By splitting the query into two UNION ALL parts—one for each is_reply value—and ordering each part separately, the execution time can drop from over a second to a few milliseconds.

The EXISTS clause is also implemented as a nested sub‑query. Rewriting it as a JOIN eliminates the sub‑query and can cut execution time from ~2 seconds to ~1 ms.

Condition push‑down is ineffective for certain constructs such as aggregate sub‑queries, sub‑queries with LIMIT , UNION , or scalar sub‑queries in the SELECT list. By moving the filter before the aggregation (e.g., applying WHERE target = 'rm-xxxx' before GROUP BY ), the plan becomes simpler and faster.

When the final WHERE clause and ordering apply only to the leftmost table, the query can be reordered to apply ORDER BY and LIMIT on that table first, then perform the remaining joins. This early‑limit strategy reduces the processed row count dramatically.

For queries with intermediate result sets, such as a left join where the right side aggregates a large table, using a WITH clause (common table expression) to materialize the small set once and then joining it avoids repeated scans and improves performance from seconds to milliseconds.

In summary, understanding how the MySQL optimizer generates execution plans and rewriting queries to avoid sub‑queries, push conditions early, and leverage JOIN and WITH can lead to high‑performance SQL.