Database Query Optimization

Key strategies for database optimization:

Index Strategy

-- Composite indexes (order matters)
CREATE INDEX idx_user_status_created ON users(status, created_at);

-- Partial indexes for better performance
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Covering indexes
CREATE INDEX idx_user_profile ON users(id) INCLUDE (name, email, status);

Query Optimization Patterns

-- Use EXISTS instead of IN for subqueries
SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Avoid functions in WHERE clauses
-- Bad: WHERE YEAR(created_at) = 2024
-- Good: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

-- Use LIMIT for large result sets
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0;

Connection Pooling

• Set appropriate pool size (usually 10-20 connections)
• Monitor connection usage
• Use connection timeouts
• Close connections properly

Monitoring Queries

• Use EXPLAIN ANALYZE to understand query execution
• Monitor slow query logs
• Set up alerts for long-running queries
• Track query performance over time