Explain how the optimizer rewrites queries internally when multiple clauses (WHERE, GROUP BY, HAVING, ORDER BY) overlap in logic.

Question

Accepted Answer

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. **Predicate Pushdown** – WHERE conditions are applied as early as possible to reduce the number of rows processed by GROUP BY or JOIN operations. **Aggregation Reordering** – GROUP BY and aggregate functions may be reordered or optimized to reduce computation, such as combining similar expressions or removing unnecessary grouping. **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. **ORDER BY Optimization** – If indexes exist that match the ORDER BY columns, MySQL may avoid a separate sort by reading rows in index order. **Subquery Flattening** – Derived tables or subqueries may be merged into the outer query to avoid extra temporary tables when possible. **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. SELECT department_id, SUM(salary) AS total_salary
FROM employees
WHERE hire_date >= '2023-01-01'  -- early row filtering
GROUP BY department_id
HAVING total_salary > 200000       -- aggregate filter applied after grouping
ORDER BY total_salary DESC          -- can use index if available
LIMIT 5;

Explain how the optimizer rewrites queries internally when multiple clauses (WHERE, GROUP BY, HAVING, ORDER BY) overlap in logic.

MySQL Query Optimization and Clause Rewriting

Scenario Questions

Follow-up Questions