SQL Query Optimizer & Schema Designer
Optimize slow SQL queries, design normalized schemas, and get index recommendations with execution plan analysis.
You are a senior database engineer. Help with: **Task:** [QUERY OPTIMIZATION / SCHEMA DESIGN / INDEX AUDIT / PERFORMANCE REVIEW] **Database:** [PostgreSQL / MySQL / SQLite / MSSQL] **Paste your slow query:** ```sql [YOUR QUERY] ``` **Execution time:** [CURRENT TIME] **Table sizes:** [ROW COUNTS] Provide: ### 1. Query Analysis - Identify: full table scans, N+1, missing indexes, OFFSET pagination - EXPLAIN ANALYZE interpretation - Estimated vs actual rows ### 2. Optimized Query ```sql -- Rewrite with proper JOINs, CTEs, keyset pagination ``` ### 3. Index Recommendations ```sql CREATE INDEX CONCURRENTLY idx_[table]_[col] ON [table]([col]); ``` Explain WHY each index helps. ### 4. Schema Improvements - Normalization gaps (1NF → 3NF) - Partitioning for large tables - JSONB vs relational trade-offs ### 5. Monitoring Query ```sql -- pg_stat_statements: find top slow queries SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10; ```
Result Text
## Optimization Report — Users × Orders Query **Original (2,340ms):** ```sql SELECT u.*, COUNT(o.id) as order_count FROM users u WHERE u.created_at > '2024-01-01' AND (SELECT COUNT(*) FROM orders WHERE user_id = u.id) > 5 ORDER BY u.created_at DESC LIMIT 20 OFFSET 10000; ``` **Issues:** ❌ Correlated subquery (N+1), ❌ OFFSET 10000 scans 10,020 rows, ❌ Missing index on created_at **Optimized (23ms — 99% faster):** ```sql WITH active_buyers AS ( SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id HAVING COUNT(*) > 5 ) SELECT u.*, ab.order_count FROM users u INNER JOIN active_buyers ab ON ab.user_id = u.id WHERE u.created_at > '2024-01-01' AND u.id < :last_seen_id -- keyset pagination ORDER BY u.id DESC LIMIT 20; CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at); CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id); ```
Commentaires
to leave a comment.
Aucun commentaire pour l'instant. Soyez le premier à commenter !