Database Optimization

Database Optimization

This skill optimizes database performance including query optimization, indexing strategies, N+1 problem resolution, and caching implementation.

When to Use This Skill

• When optimizing slow database queries
• When fixing N+1 query problems
• When designing indexes
• When implementing caching strategies
• When optimizing database migrations
• When improving database performance

What This Skill Does

1. Query Optimization: Analyzes and optimizes SQL queries 2. Index Design: Creates appropriate indexes 3. N+1 Resolution: Fixes N+1 query problems 4. Caching: Implements caching layers (Redis, Memcached) 5. Migration Optimization: Optimizes database migrations 6. Performance Monitoring: Sets up query performance monitoring

How to Use

Optimize Queries

Optimize this slow database query

Fix the N+1 query problem in this code

Specific Analysis

Analyze query performance and suggest indexes

Optimization Areas

Query Optimization

Techniques:

• Use EXPLAIN ANALYZE
• Optimize JOINs
• Reduce data scanned
• Use appropriate indexes
• Avoid SELECT *

Index Design

Strategies:

• Index frequently queried columns
• Composite indexes for multi-column queries
• Avoid over-indexing
• Monitor index usage
• Remove unused indexes

N+1 Problem

Pattern:

# Bad: N+1 queries
users = User.all()
for user in users:
    posts = Post.where(user_id=user.id)  # N queries

# Good: Single query with JOIN
users = User.all().includes(:posts)  # 1 query

Examples

Example 1: Query Optimization

Input: Optimize slow user query

Output:

## Database Optimization: User Query

### Current Query

SELECT * FROM users WHERE email = 'user@example.com'; -- Execution time: 450ms

### Analysis

- Full table scan (no index on email)
- Scanning 1M+ rows

### Optimization

-- Add index CREATE INDEX idx_users_email ON users(email);

-- Optimized query SELECT id, email, name FROM users WHERE email = 'user@example.com'; -- Execution time: 2ms

### Impact

- Query time: 450ms → 2ms (99.5% improvement)
- Index size: ~50MB

Best Practices

Database Optimization

1. Measure First: Use EXPLAIN ANALYZE 2. Index Strategically: Not every column needs an index 3. Monitor: Track slow query logs 4. Cache: Cache expensive queries 5. Denormalize: When justified by read patterns

Reference Files

• `references/query_patterns.md` - Common query optimization patterns, anti-patterns, and caching strategies

Related Use Cases

• Query optimization
• Index design
• N+1 problem resolution
• Caching implementation
• Database performance improvement

Database Query Optimization Patterns

EXPLAIN ANALYZE Usage

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'test@example.com';

MySQL

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

Common Query Anti-Patterns

1. SELECT * Instead of Specific Columns

-- Bad: Fetches all columns
SELECT * FROM users WHERE id = 1;

-- Good: Fetch only needed columns
SELECT id, email, name FROM users WHERE id = 1;

2. Missing WHERE Clause Index

-- Bad: Full table scan
SELECT * FROM orders WHERE status = 'pending';

-- Good: Add index on status
CREATE INDEX idx_orders_status ON orders(status);

3. Leading Wildcard in LIKE

-- Bad: Cannot use index
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Good: Use suffix column or full-text search
SELECT * FROM users WHERE email_domain = 'gmail.com';

4. Functions on Indexed Columns

-- Bad: Index not used
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Good: Use range comparison
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

5. OR Conditions

-- Bad: May not use index efficiently
SELECT * FROM users WHERE status = 'active' OR role = 'admin';

-- Good: Use UNION for separate index scans
SELECT * FROM users WHERE status = 'active'
UNION
SELECT * FROM users WHERE role = 'admin';

Index Design Patterns

Composite Index Order

-- Index columns in order of: equality, range, sort
-- Query: WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY name
CREATE INDEX idx_composite ON users(status, created_at, name);

Covering Index

-- Include all columns needed by query to avoid table lookup
CREATE INDEX idx_covering ON orders(customer_id, status)
INCLUDE (total, created_at);

Partial Index

-- Index only frequently queried subset
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

N+1 Query Solutions

ORM Eager Loading

SQLAlchemy:

# Bad: N+1
users = session.query(User).all()
for user in users:
    print(user.posts)  # Triggers query per user

# Good: Eager load
users = session.query(User).options(joinedload(User.posts)).all()

Django:

# Bad: N+1
users = User.objects.all()
for user in users:
    print(user.posts.all())

# Good: Prefetch
users = User.objects.prefetch_related('posts').all()

ActiveRecord:

# Bad: N+1
User.all.each { |u| puts u.posts }

# Good: Includes
User.includes(:posts).each { |u| puts u.posts }

Caching Strategies

Query Result Caching

# Redis caching pattern
import redis
import json

def get_user(user_id):
    cache_key = f"user:{user_id}"
    cached = redis.get(cache_key)
    if cached:
        return json.loads(cached)

    user = db.query("SELECT * FROM users WHERE id = %s", user_id)
    redis.setex(cache_key, 3600, json.dumps(user))  # 1 hour TTL
    return user

Cache Invalidation Patterns

• Time-based: Set TTL on cached entries
• Event-based: Invalidate on write operations
• Version-based: Include version in cache key