Query Rewriting for Performance
What you'll learn: How to transform slow queries into faster, equivalent versions that return the same results but execute more efficiently.
The Core Idea
Query rewriting means taking a logically correct SQL query and restructuring it into a form that produces identical results but runs significantly faster. Your database optimizer tries to do this automatically, but you can often help by recognizing patterns that perform poorly and manually rewriting them before they hit production.
Think of it like giving driving directions: "Go through every neighborhood until you find the red house" is semantically the same as "Turn left on Oak Street, it's the third house on the right"—but one route is dramatically faster.
Common Rewriting Patterns
Replace Subqueries with Joins: Instead of running a subquery for each row (nested loop behavior), join the tables directly so the database can process them together.
-- Slow: subquery executes repeatedly
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);
-- Fast: join processes both tables together
SELECT DISTINCT u.name FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;
Push Down Filters Early: Filter data as early as possible to reduce the working set size, rather than filtering after expensive operations like joins or aggregations.
Break Complex Queries into Stages: Sometimes splitting one monster query into multiple simpler ones (with temp tables) helps the optimizer make better decisions for each piece.
Why It Works
The database optimizer has rules and heuristics, but it can't always pick the perfect execution plan—especially with complex nested logic. Rewriting gives you control over how the database accesses and processes data, letting you leverage indexes and reduce intermediate result sizes.
Key Takeaway: Query rewriting transforms semantically identical queries into faster forms by eliminating inefficient patterns like correlated subqueries, pushing filters earlier, and making better use of joins and indexes.