Query Rewriting and Transformation
What you'll learn: How database optimizers logically rewrite your SQL queries into equivalent but faster-executing forms.
The Core Idea
Before a database executes your query, its optimizer performs logical transformations—rewriting the query into an equivalent form that produces the same results but runs more efficiently. Think of it like taking a detour that reaches the same destination faster: the starting and ending points are identical, but the route is smarter.
These transformations don't change what data you get, only how the database retrieves it.
Common Query Transformations
Predicate Pushdown
Instead of filtering data after joining tables, the optimizer "pushes down" WHERE conditions to filter rows before the join. This reduces the amount of data being joined.
Analogy: Rather than inviting everyone to a party and checking IDs at the entrance, you send invitations only to people who already meet the criteria.
Join Reordering
When joining multiple tables, the order matters for performance. The optimizer can rearrange joins to process smaller intermediate result sets first, reducing memory and CPU usage.
Example: Joining a small table with a large one first, then joining the result with another table, rather than starting with two large tables.
Expression Simplification
The optimizer can simplify or rewrite expressions. For instance, WHERE salary * 12 > 100000 might prevent index usage, but could be rewritten as WHERE salary > 8333.33 to allow the index on salary to be used.
Why This Matters
Query rewriting happens automatically based on the optimizer's cost estimates and cardinality predictions. It works hand-in-hand with index selection and join order decisions you've already learned. The optimizer explores multiple rewritten versions of your query and picks the cheapest execution plan.
Key Takeaway: Query rewriting and transformation automatically convert your SQL into logically equivalent but more efficient forms through techniques like predicate pushdown and join reordering, reducing unnecessary data processing.