Course contentsShow
← Computer Science
Lesson 1703 of 2,87238. Database Transactions and IndexingFree lesson

Query Rewriting and Transformation

Logical transformations like predicate pushdown and join reordering that improve query performance.

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.