Sql Optimization

1---2name: sql-optimization3description: 'Universal SQL performance optimization assistant for comprehensive query tuning, indexing strategies, and database performance analysis across all SQL databases (MySQL, PostgreSQL, SQL Server, Oracle). Provides execution plan analysis, pagination optimization, batch operations, and performance monitoring guidance.'4---5 6# SQL Performance Optimization Assistant7 8Expert SQL performance optimization for ${selection} (or entire project if no selection). Focus on universal SQL optimization techniques that work across MySQL, PostgreSQL, SQL Server, Oracle, and other SQL databases.9 10## 🎯 Core Optimization Areas11 12### Query Performance Analysis13```sql14-- ❌ BAD: Inefficient query patterns15SELECT * FROM orders o16WHERE YEAR(o.created_at) = 202417  AND o.customer_id IN (18      SELECT c.id FROM customers c WHERE c.status = 'active'19  );20 21-- ✅ GOOD: Optimized query with proper indexing hints22SELECT o.id, o.customer_id, o.total_amount, o.created_at23FROM orders o24INNER JOIN customers c ON o.customer_id = c.id25WHERE o.created_at >= '2024-01-01' 26  AND o.created_at < '2025-01-01'27  AND c.status = 'active';28 29-- Required indexes:30-- CREATE INDEX idx_orders_created_at ON orders(created_at);31-- CREATE INDEX idx_customers_status ON customers(status);32-- CREATE INDEX idx_orders_customer_id ON orders(customer_id);33```34 35### Index Strategy Optimization36```sql37-- ❌ BAD: Poor indexing strategy38CREATE INDEX idx_user_data ON users(email, first_name, last_name, created_at);39 40-- ✅ GOOD: Optimized composite indexing41-- For queries filtering by email first, then sorting by created_at42CREATE INDEX idx_users_email_created ON users(email, created_at);43 44-- For full-text name searches45CREATE INDEX idx_users_name ON users(last_name, first_name);46 47-- For user status queries48CREATE INDEX idx_users_status_created ON users(status, created_at)49WHERE status IS NOT NULL;50```51 52### Subquery Optimization53```sql54-- ❌ BAD: Correlated subquery55SELECT p.product_name, p.price56FROM products p57WHERE p.price > (58    SELECT AVG(price) 59    FROM products p2 60    WHERE p2.category_id = p.category_id61);62 63-- ✅ GOOD: Window function approach64SELECT product_name, price65FROM (66    SELECT product_name, price,67           AVG(price) OVER (PARTITION BY category_id) as avg_category_price68    FROM products69) ranked70WHERE price > avg_category_price;71```72 73## 📊 Performance Tuning Techniques74 75### JOIN Optimization76```sql77-- ❌ BAD: Inefficient JOIN order and conditions78SELECT o.*, c.name, p.product_name79FROM orders o80LEFT JOIN customers c ON o.customer_id = c.id81LEFT JOIN order_items oi ON o.id = oi.order_id82LEFT JOIN products p ON oi.product_id = p.id83WHERE o.created_at > '2024-01-01'84  AND c.status = 'active';85 86-- ✅ GOOD: Optimized JOIN with filtering87SELECT o.id, o.total_amount, c.name, p.product_name88FROM orders o89INNER JOIN customers c ON o.customer_id = c.id AND c.status = 'active'90INNER JOIN order_items oi ON o.id = oi.order_id91INNER JOIN products p ON oi.product_id = p.id92WHERE o.created_at > '2024-01-01';93```94 95### Pagination Optimization96```sql97-- ❌ BAD: OFFSET-based pagination (slow for large offsets)98SELECT * FROM products 99ORDER BY created_at DESC 100LIMIT 20 OFFSET 10000;101 102-- ✅ GOOD: Cursor-based pagination103SELECT * FROM products 104WHERE created_at < '2024-06-15 10:30:00'105ORDER BY created_at DESC 106LIMIT 20;107 108-- Or using ID-based cursor109SELECT * FROM products 110WHERE id > 1000111ORDER BY id 112LIMIT 20;113```114 115### Aggregation Optimization116```sql117-- ❌ BAD: Multiple separate aggregation queries118SELECT COUNT(*) FROM orders WHERE status = 'pending';119SELECT COUNT(*) FROM orders WHERE status = 'shipped';120SELECT COUNT(*) FROM orders WHERE status = 'delivered';121 122-- ✅ GOOD: Single query with conditional aggregation123SELECT 124    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_count,125    COUNT(CASE WHEN status = 'shipped' THEN 1 END) as shipped_count,126    COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_count127FROM orders;128```129 130## 🔍 Query Anti-Patterns131 132### SELECT Performance Issues133```sql134-- ❌ BAD: SELECT * anti-pattern135SELECT * FROM large_table lt136JOIN another_table at ON lt.id = at.ref_id;137 138-- ✅ GOOD: Explicit column selection139SELECT lt.id, lt.name, at.value140FROM large_table lt141JOIN another_table at ON lt.id = at.ref_id;142```143 144### WHERE Clause Optimization145```sql146-- ❌ BAD: Function calls in WHERE clause147SELECT * FROM orders 148WHERE UPPER(customer_email) = 'JOHN@EXAMPLE.COM';149 150-- ✅ GOOD: Index-friendly WHERE clause151SELECT * FROM orders 152WHERE customer_email = 'john@example.com';153-- Consider: CREATE INDEX idx_orders_email ON orders(LOWER(customer_email));154```155 156### OR vs UNION Optimization157```sql158-- ❌ BAD: Complex OR conditions159SELECT * FROM products 160WHERE (category = 'electronics' AND price < 1000)161   OR (category = 'books' AND price < 50);162 163-- ✅ GOOD: UNION approach for better optimization164SELECT * FROM products WHERE category = 'electronics' AND price < 1000165UNION ALL166SELECT * FROM products WHERE category = 'books' AND price < 50;167```168 169## 📈 Database-Agnostic Optimization170 171### Batch Operations172```sql173-- ❌ BAD: Row-by-row operations174INSERT INTO products (name, price) VALUES ('Product 1', 10.00);175INSERT INTO products (name, price) VALUES ('Product 2', 15.00);176INSERT INTO products (name, price) VALUES ('Product 3', 20.00);177 178-- ✅ GOOD: Batch insert179INSERT INTO products (name, price) VALUES 180('Product 1', 10.00),181('Product 2', 15.00),182('Product 3', 20.00);183```184 185### Temporary Table Usage186```sql187-- ✅ GOOD: Using temporary tables for complex operations188CREATE TEMPORARY TABLE temp_calculations AS189SELECT customer_id, 190       SUM(total_amount) as total_spent,191       COUNT(*) as order_count192FROM orders 193WHERE created_at >= '2024-01-01'194GROUP BY customer_id;195 196-- Use the temp table for further calculations197SELECT c.name, tc.total_spent, tc.order_count198FROM temp_calculations tc199JOIN customers c ON tc.customer_id = c.id200WHERE tc.total_spent > 1000;201```202 203## 🛠️ Index Management204 205### Index Design Principles206```sql207-- ✅ GOOD: Covering index design208CREATE INDEX idx_orders_covering 209ON orders(customer_id, created_at) 210INCLUDE (total_amount, status);  -- SQL Server syntax211-- Or: CREATE INDEX idx_orders_covering ON orders(customer_id, created_at, total_amount, status); -- Other databases212```213 214### Partial Index Strategy215```sql216-- ✅ GOOD: Partial indexes for specific conditions217CREATE INDEX idx_orders_active 218ON orders(created_at) 219WHERE status IN ('pending', 'processing');220```221 222## 📊 Performance Monitoring Queries223 224### Query Performance Analysis225```sql226-- Generic approach to identify slow queries227-- (Specific syntax varies by database)228 229-- For MySQL:230SELECT query_time, lock_time, rows_sent, rows_examined, sql_text231FROM mysql.slow_log232ORDER BY query_time DESC;233 234-- For PostgreSQL:235SELECT query, calls, total_time, mean_time236FROM pg_stat_statements237ORDER BY total_time DESC;238 239-- For SQL Server:240SELECT 241    qs.total_elapsed_time/qs.execution_count as avg_elapsed_time,242    qs.execution_count,243    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,244        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)245        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) as query_text246FROM sys.dm_exec_query_stats qs247CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt248ORDER BY avg_elapsed_time DESC;249```250 251## 🎯 Universal Optimization Checklist252 253### Query Structure254- [ ] Avoiding SELECT * in production queries255- [ ] Using appropriate JOIN types (INNER vs LEFT/RIGHT)256- [ ] Filtering early in WHERE clauses257- [ ] Using EXISTS instead of IN for subqueries when appropriate258- [ ] Avoiding functions in WHERE clauses that prevent index usage259 260### Index Strategy261- [ ] Creating indexes on frequently queried columns262- [ ] Using composite indexes in the right column order263- [ ] Avoiding over-indexing (impacts INSERT/UPDATE performance)264- [ ] Using covering indexes where beneficial265- [ ] Creating partial indexes for specific query patterns266 267### Data Types and Schema268- [ ] Using appropriate data types for storage efficiency269- [ ] Normalizing appropriately (3NF for OLTP, denormalized for OLAP)270- [ ] Using constraints to help query optimizer271- [ ] Partitioning large tables when appropriate272 273### Query Patterns274- [ ] Using LIMIT/TOP for result set control275- [ ] Implementing efficient pagination strategies276- [ ] Using batch operations for bulk data changes277- [ ] Avoiding N+1 query problems278- [ ] Using prepared statements for repeated queries279 280### Performance Testing281- [ ] Testing queries with realistic data volumes282- [ ] Analyzing query execution plans283- [ ] Monitoring query performance over time284- [ ] Setting up alerts for slow queries285- [ ] Regular index usage analysis286 287## 📝 Optimization Methodology288 2891. **Identify**: Use database-specific tools to find slow queries2902. **Analyze**: Examine execution plans and identify bottlenecks2913. **Optimize**: Apply appropriate optimization techniques2924. **Test**: Verify performance improvements2935. **Monitor**: Continuously track performance metrics2946. **Iterate**: Regular performance review and optimization295 296Focus on measurable performance improvements and always test optimizations with realistic data volumes and query patterns.