MySQL's query optimizer evaluates queries to determine the most efficient execution plan. When multiple clauses like WHERE, GROUP BY, HAVING, and ORDER BY overlap in logic, the optimizer may rewrite the query internally to improve performance while preserving correctness.

Key Optimizations Performed by MySQL

1
Predicate Pushdown – WHERE conditions are applied as early as possible to reduce the number of rows processed by GROUP BY or JOIN operations.
2
Aggregation Reordering – GROUP BY and aggregate functions may be reordered or optimized to reduce computation, such as combining similar expressions or removing unnecessary grouping.
3
HAVING Simplification – If HAVING conditions can be expressed in terms of WHERE or indexed columns, the optimizer may push them down to filter rows earlier.
4
ORDER BY Optimization – If indexes exist that match the ORDER BY columns, MySQL may avoid a separate sort by reading rows in index order.
5
Subquery Flattening – Derived tables or subqueries may be merged into the outer query to avoid extra temporary tables when possible.
6
Expression Simplification – Constant expressions or redundant calculations are precomputed, reducing runtime evaluation overhead.

Overall, the optimizer ensures that filtering, grouping, aggregation, and sorting are executed efficiently. Even if clauses overlap logically (e.g., WHERE filters rows that could also be filtered by HAVING), the optimizer rearranges or merges operations to minimize the workload without changing the final result.

Example Showing Clause Overlap Optimization

Difficulty: 7/10

Topics: predicate pushdown, query rewrite, execution plan

Scenario Questions

0-2 years experience

1
You have a query SELECT col1 FROM t WHERE col1 > 10 GROUP BY col1 HAVING col1 > 10 ORDER BY col1. How will MySQL's optimizer treat the overlapping conditions?
2
If you add an index on col1, what part of this query will the optimizer try to satisfy using that index?

2-5 years experience

1
During a code review you notice a query with both WHERE and HAVING filtering the same column. The query is running slowly. Walk me through how you would debug the optimizer's rewrite steps and what changes you might make.
2
A feature adds an ORDER BY clause to an existing grouped query, and the execution time doubles. Explain why the optimizer might not be able to reuse the previous plan and how you could rewrite the query to help it.

5-8 years experience

1
Design a monitoring alert that detects when MySQL fails to push down predicates from HAVING to WHERE, causing full table scans. What metrics and EXPLAIN output would you look at?
2
You are scaling a reporting service that runs complex GROUP BY/HAVING queries on a 10 TB table. Discuss the trade‑offs of relying on the optimizer's rewrite versus manually materializing intermediate results.

8+ years experience

1
Our legacy system has many queries where WHERE, GROUP BY, HAVING, and ORDER BY overlap. We plan a migration to a newer MySQL version with a different optimizer. How would you assess the risk and plan the migration to ensure query semantics stay identical?
2
Across multiple services, teams are writing similar aggregation queries with redundant clauses. Propose an architectural guideline or tooling to enforce optimal query rewrites at compile‑time.

Follow-up Questions

❓How does predicate pushdown affect index usage?
❓What happens if the optimizer cannot eliminate a redundant ORDER BY?
❓Can you give an example where HAVING is evaluated before WHERE and why that matters?