Bits of .NET

⚡ Query Optimization = Database Performance

Slow queries kill performance. Query optimization speeds up database. Faster responses, better UX.

📝 Optimization Techniques

# 1. Avoid SELECT *
-- Bad
SELECT * FROM users;

-- Good
SELECT id, name, email FROM users;

# 2. Use WHERE conditions
-- Bad
SELECT * FROM users;

-- Good
SELECT * FROM users WHERE active = 1;

# 3. Use indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);

# 4. Avoid functions in WHERE
-- Bad
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- Good
SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
AND created_at < '2025-01-01';

# 5. Use EXISTS instead of IN
-- Bad
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders);

-- Good
SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

# 6. Limit results
SELECT * FROM users LIMIT 100;

# 7. Avoid OR (use UNION)
-- Bad
SELECT * FROM users WHERE status = 'active' OR status = 'pending';

-- Good
SELECT * FROM users WHERE status = 'active'
UNION
SELECT * FROM users WHERE status = 'pending';

🎯 Advanced Optimization

# Analyze query plan
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

# Use covering indexes
CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name, created_at);

# Partition large tables
CREATE TABLE orders (
    id INT,
    order_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(order_date));

# Denormalization (for performance)
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);
UPDATE orders o SET customer_name = (SELECT name FROM users WHERE id = o.user_id);

# Use materialized views
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) as month,
    SUM(amount) as total_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

# Batch operations
-- Bad
FOR EACH order IN orders
    UPDATE stock SET quantity = quantity - 1 WHERE product_id = order.product_id;

-- Good
UPDATE stock s
SET quantity = quantity - o.quantity
FROM orders o
WHERE s.product_id = o.product_id;

# Use connection pooling
-- Increase pool size for high traffic
-- Set appropriate timeout values

✅ Optimization Checklist

• ✓ Use EXPLAIN to analyze queries
• ✓ Create appropriate indexes
• ✓ Avoid SELECT *
• ✓ Use LIMIT for large result sets
• ✓ Monitor slow query logs