query-rewriter

# SQL Query Rewriting Expert ## Common Anti-Patterns | Anti-Pattern | Problem | Reference | |--------------|---------|-----------| | SELECT * | Fetches unnecessary data | `references/select-star.md` | | N+1 queries | Too many round trips | `references/n-plus-one.md` | | IN with subquery | May process too many rows | `references/exists-vs-in.md` | | OR across columns | Prevents index usage | `references/or-optimization.md` | | OR conditions | Cannot use index | `references/union-instead-of-or.md` | | Type mismatch | Implicit conversion | `references/implicit-conversion.md` | ## Audit Workflow ### Step 1: Identify Problem Patterns Check for: - SELECT * usage - N+1 query patterns - IN with large subqueries - OR conditions across columns - Type mismatches ### Step 2: Match Anti-Pattern Identify which anti-pattern the query matches and apply the corresponding solution from the reference file. ### Step 3: Apply Rewrite Follow the specific reference file for detailed instructions. ### Step 4: Verify Improvement ```sql EXPLAIN ANALYZE <rewritten_query>; ``` ## Benchmark Reference | Optimization | Before | After | Improvement | |--------------|--------|-------|-------------| | SELECT * → specific | 100ms | 20ms | 80% | | N+1 → batch | 500ms | 50ms | 90% | | IN → EXISTS | 500ms | 50ms | 90% | | OR → UNION | 200ms | 20ms | 90% | | Type fix | 150ms | 2ms | 98.7% | ## Output Format ``` ## Query Rewriting Report ### 1. Original Query ```sql <original_query> ``` ### 2. Anti-Patterns Detected | Pattern | Problem | Reference | |---------|---------|-----------| | ... | ... | ... | ### 3. Recommended Rewrite ```sql <optimized_query> ``` ### 4. Expected Improvement - Query time: Xms → Yms (Z% faster) - Rows processed: X → Y (Z% reduction) ### 5. Implementation Reference: references/<case-name>.md ```