Are You Still Using These 8 Inefficient SQL Patterns?

1. LIMIT Statement

Pagination is a frequent use case but often a source of performance problems. A simple query with a composite index on type, name, and create_time runs fast, yet using a large offset such as LIMIT 1000000,10 remains slow because the database must scan from the beginning even with an index. The author recommends using the maximum value of the previous page (e.g., create_time > '2017-03-16 14:00:00') as a filter, turning the query into a range scan whose execution time stays constant regardless of table size.

2. Implicit Conversion

When a query compares a VARCHAR(20) column bpn with a numeric literal, MySQL converts the string to a number, causing the index on bpn to become unusable. The EXPLAIN EXTENDED output shows a warning about type or collation conversion. This often originates from framework‑generated parameters, so developers should verify the data types of bound variables.

3. Update/Delete with Subqueries

MySQL 5.6 introduced materialized view optimizations only for SELECT statements; UPDATE or DELETE statements still execute dependent subqueries. An example UPDATE uses a nested subquery, and EXPLAIN shows a DEPENDENT SUBQUERY with a runtime of about 7 seconds. Rewriting the statement as a JOIN changes the plan to DERIVED, reducing execution time to roughly 2 milliseconds.

4. Mixed Ordering

MySQL cannot use an index for mixed ORDER BY clauses. The author splits the query into two parts, each filtering on a constant is_reply value (0 or 1) and ordering by appraise_time, then combines the results with UNION ALL. This transformation cuts the runtime from 1.58 seconds to about 2 milliseconds.

5. EXISTS Clause

MySQL still treats EXISTS as a nested subquery. An example query with EXISTS shows a DEPENDENT SUBQUERY plan and a runtime of 1.93 seconds. Replacing EXISTS with an explicit JOIN eliminates the subquery, yielding a plan with simple table accesses and a runtime of roughly 1 millisecond.

6. Condition Push‑Down

External query conditions cannot be pushed into certain subqueries, such as aggregate subqueries, subqueries with LIMIT, UNION / UNION ALL, or subqueries in the SELECT list. The article demonstrates an aggregate subquery where the outer WHERE target='rm‑xxxx' is applied after aggregation. By moving the condition into the inner query, the plan becomes a simple index lookup ( type=ref) and executes in a single row access.

7. Early Limiting

The original query joins three tables, filters on display and ostaus, orders by selltime, and limits to 15 rows. The optimizer estimates 900 k rows to sort, resulting in a 12‑second execution. Since the WHERE clause and ORDER BY apply only to the leftmost table ( my_order), the author first selects and orders the limited rows from my_order in a subquery, then performs the LEFT JOINs. This reduces the runtime to about 1 millisecond.

8. Intermediate Result Set Push‑Down

An example query selects from a subquery a (limited list of resourceid) and left‑joins a second subquery c that aggregates the entire my_resources table. Because c aggregates all rows, the original plan is slow (≈2 seconds). By joining c only on matching resourceid values and using a WITH clause to reuse a, the execution time drops to about 2 milliseconds.

Conclusion

The database compiler generates execution plans, but it is not infallible. Understanding its behavior—especially how it handles offsets, type conversion, subqueries, ordering, and condition push‑down—allows developers to write high‑performance SQL. Incorporating algorithmic thinking, using WITH for clarity, and avoiding patterns that force full scans or materialized subqueries lead to more efficient queries across different database systems.