Bits of .NET

🚀 10 Second Query → 0.1 Second

Don’t know SQL optimization? AI analyzes query, suggests indexes, rewrites for speed. Massive performance gains.

📝 The Prompt

I have a slow SQL query that needs optimization.

Current query:
[paste your SQL query]

Database: [PostgreSQL/MySQL/SQL Server]
Table sizes:
- users: 1 million rows
- orders: 5 million rows
- products: 100,000 rows

Current indexes:
[list current indexes if known]

Execution time: [e.g., 8 seconds]

Please:
1. Explain why this query is slow
2. Identify missing indexes needed
3. Rewrite query for better performance
4. Suggest query structure improvements
5. Explain the performance gain expected
6. Provide CREATE INDEX statements

Make the query as fast as possible while maintaining correctness.

✅ Example Optimization

-- ❌ Slow (8 seconds)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
  AND o.status = 'completed'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5;

-- AI Analysis:
-- 1. No index on users(created_at)
-- 2. LEFT JOIN but filtering on orders (use INNER JOIN)
-- 3. GROUP BY on multiple columns

-- ✅ Optimized (0.2 seconds)
CREATE INDEX idx_users_created ON users(created_at);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

SELECT u.name, COUNT(o.id) as order_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
  AND o.status = 'completed'
GROUP BY u.id
HAVING COUNT(o.id) > 5;

-- Performance: 8s → 0.2s (40x faster!)

💡 What AI Helps With

• Missing indexes (identifies columns that need indexing)
• Query rewriting (more efficient SQL structure)
• Join optimization (better join order and types)
• N+1 query detection
• EXPLAIN plan analysis