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

• Query Optimization: Analyzes and optimizes SQL queries
• Index Design: Creates appropriate indexes
• N+1 Resolution: Fixes N+1 query problems
• Caching: Implements caching layers (Redis, Memcached)
• Migration Optimization: Optimizes database migrations
• 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 = '[email protected]';

-- 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 = '[email protected]';

-- Execution time: 2ms

### Impact

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

Best Practices

Database Optimization

• Measure First: Use EXPLAIN ANALYZE
• Index Strategically: Not every column needs an index
• Monitor: Track slow query logs
• Cache: Cache expensive queries
• 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