# Database Optimizer

> Database Optimizer Skill

- Skill: `lodetomasi/database-optimizer` (Agent Skill)
- Install (CLI): `npx skillmds@latest add lodetomasi/database-optimizer`
- Raw SKILL.md: https://api.skillmd.com/api/skills/lodetomasi/database-optimizer/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: lodetomasi (https://skillmd.com/u/lodetomasi)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/lodetomasi/database-optimizer

---


# Database Optimizer Skill

## Overview
Comprehensive database optimization skill for detecting and fixing N+1 queries, optimizing ORM configurations, analyzing slow queries, and implementing efficient batch operations.

## Capabilities

### 1. N+1 Query Detection
- Identify N+1 query patterns
- Analyze ORM-generated queries
- Suggest eager loading strategies
- Detect inefficient lazy loading

### 2. Query Optimization
- Slow query analysis
- Execution plan review
- Index recommendations
- Query rewriting

### 3. ORM Configuration
- JPA/Hibernate tuning (Java)
- SQLAlchemy optimization (Python)
- Django ORM best practices
- Connection pool configuration

### 4. Batch Operations
- JDBC batch processing
- Bulk inserts/updates
- Transaction optimization
- Connection pooling

## N+1 Query Detection

### What is N+1?
```sql
-- 1 query to get all users
SELECT * FROM users;  -- Returns 100 users

-- N queries to get each user's orders (N = 100)
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
...
SELECT * FROM orders WHERE user_id = 100;

-- Total: 1 + 100 = 101 queries (INEFFICIENT!)
```

### Solution: Eager Loading
```sql
-- 1 query with JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Total: 1 query (EFFICIENT!)
```

## JPA/Hibernate Optimization (Java)

### Detect N+1 in JPA
```java
// ❌ BAD: Lazy loading causes N+1
@Entity
public class User {
    @OneToMany(fetch = FetchType.LAZY)  // Default
    private List<Order> orders;
}

List<User> users = userRepository.findAll();  // 1 query
for (User user : users) {
    user.getOrders().size();  // N queries!
}
```

### Fix with Eager Loading
```java
// ✅ GOOD: Use JOIN FETCH
@Query("SELECT u FROM User u LEFT JOIN FETCH u.orders")
List<User> findAllWithOrders();

// Or use EntityGraph
@EntityGraph(attributePaths = {"orders"})
List<User> findAll();
```

### Batch Configuration
```properties
# application.properties
spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.batch_versioned_data=true
```

### Lazy Loading Configuration
```java
// Set LazyCollectionOption for specific cases
@OneToMany(fetch = FetchType.LAZY)
@LazyCollection(LazyCollectionOption.FALSE)  // Eager loading
private List<Order> criticalOrders;

@OneToMany(fetch = FetchType.LAZY)
@LazyCollection(LazyCollectionOption.EXTRA)  // Smart queries
private List<Order> manyOrders;
```

## SQLAlchemy Optimization (Python)

### Detect N+1 in SQLAlchemy
```python
# ❌ BAD: Lazy loading
users = session.query(User).all()  # 1 query
for user in users:
    print(user.orders)  # N queries!
```

### Fix with Eager Loading
```python
# ✅ GOOD: Use joinedload
from sqlalchemy.orm import joinedload

users = session.query(User).options(
    joinedload(User.orders)
).all()  # 1 query with JOIN
```

## Django ORM Optimization

### Detect N+1 in Django
```python
# ❌ BAD: Lazy loading
users = User.objects.all()  # 1 query
for user in users:
    print(user.orders.all())  # N queries!
```

### Fix with select_related / prefetch_related
```python
# ✅ GOOD: Use select_related (ForeignKey, OneToOne)
users = User.objects.select_related('profile').all()

# ✅ GOOD: Use prefetch_related (ManyToMany, reverse ForeignKey)
users = User.objects.prefetch_related('orders').all()
```

## Integration Scripts

### detect_n_plus_one.py
Automated N+1 detection:
```python
#!/usr/bin/env python3
import re
from collections import defaultdict

def analyze_query_log(log_file):
    """Analyze SQL log for N+1 patterns"""
    queries = []
    with open(log_file) as f:
        for line in f:
            if 'SELECT' in line.upper():
                # Extract SQL query
                query = re.search(r'SELECT.*?FROM.*?(?:WHERE|;)', line, re.IGNORECASE)
                if query:
                    queries.append(query.group(0))

    # Group similar queries
    query_patterns = defaultdict(int)
    for query in queries:
        # Normalize (replace IDs with placeholder)
        normalized = re.sub(r'\d+', 'ID', query)
        query_patterns[normalized] += 1

    # Report N+1 patterns (same query repeated many times)
    print("=== Potential N+1 Query Patterns ===")
    for pattern, count in query_patterns.items():
        if count > 10:  # Threshold
            print(f"\n⚠️  FOUND N+1 PATTERN (executed {count} times):")
            print(f"   {pattern[:100]}...")
            print(f"   💡 Consider using JOIN FETCH or eager loading")

analyze_query_log('sql_queries.log')
```

### batch_analyzer.py
JPA batch configuration validator:
```python
#!/usr/bin/env python3
import re

def analyze_jpa_config(properties_file):
    """Check JPA batch configuration"""
    with open(properties_file) as f:
        content = f.read()

    checks = {
        'jdbc.batch_size': r'hibernate\.jdbc\.batch_size\s*=\s*(\d+)',
        'order_inserts': r'hibernate\.order_inserts\s*=\s*(true|false)',
        'order_updates': r'hibernate\.order_updates\s*=\s*(true|false)',
    }

    print("=== JPA Batch Configuration Analysis ===\n")

    for name, pattern in checks.items():
        match = re.search(pattern, content)
        if match:
            value = match.group(1)
            print(f"✓ {name}: {value}")

            # Recommendations
            if name == 'jdbc.batch_size':
                batch_size = int(value)
                if batch_size < 20:
                    print(f"  ⚠️  Recommended: 20-50 (current: {batch_size})")
                elif batch_size > 100:
                    print(f"  ⚠️  Too high, may cause memory issues (current: {batch_size})")
        else:
            print(f"✗ {name}: NOT CONFIGURED")
            print(f"  💡 Add: hibernate.{name}=true")

    print("\n=== Recommendations ===")
    print("• Set batch_size between 20-50 for optimal performance")
    print("• Enable order_inserts and order_updates")
    print("• Monitor memory usage with batch operations")
```

### lazy_loading_validator.py
Validate lazy loading configurations:
```python
#!/usr/bin/env python3
import re
import os

def scan_lazy_loading(directory):
    """Scan Java entities for lazy loading issues"""
    issues = []

    for root, dirs, files in os.walk(directory):
        for file in files:
            if file.endswith('.java'):
                filepath = os.path.join(root, file)
                with open(filepath) as f:
                    content = f.read()

                # Check for lazy loading without proper handling
                lazy_patterns = re.findall(
                    r'@OneToMany.*?fetch\s*=\s*FetchType\.LAZY.*?private.*?(\w+);',
                    content,
                    re.DOTALL
                )

                if lazy_patterns:
                    # Check if there's JOIN FETCH in repository
                    has_join_fetch = 'JOIN FETCH' in content
                    has_entity_graph = '@EntityGraph' in content

                    if not (has_join_fetch or has_entity_graph):
                        issues.append({
                            'file': filepath,
                            'fields': lazy_patterns,
                            'suggestion': 'Add @EntityGraph or JOIN FETCH query'
                        })

    print("=== Lazy Loading Analysis ===\n")
    if issues:
        for issue in issues:
            print(f"⚠️  {issue['file']}")
            print(f"   Lazy fields: {', '.join(issue['fields'])}")
            print(f"   💡 {issue['suggestion']}\n")
    else:
        print("✓ No obvious lazy loading issues found")

scan_lazy_loading('./src/main/java')
```

## Query Analysis

### Slow Query Detection
```sql
-- MySQL: Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- Queries > 2 seconds

-- PostgreSQL: Enable logging
ALTER DATABASE mydb SET log_min_duration_statement = 2000;
```

### EXPLAIN Analysis
```sql
-- MySQL
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- Look for:
-- • type = ALL (full table scan) ❌
-- • type = index (index scan) ✓
-- • type = ref (indexed lookup) ✓
-- • rows (estimated rows) - lower is better
```

## Index Optimization

### Missing Index Detection
```sql
-- Check for queries without indexes
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb';

-- Add indexes for frequently queried columns
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_created_at ON orders(created_at);

-- Composite indexes for multi-column queries
CREATE INDEX idx_user_status ON orders(user_id, status);
```

### Index Guidelines
- Index foreign keys
- Index columns in WHERE clauses
- Index columns in ORDER BY
- Index columns in JOIN conditions
- Avoid over-indexing (slows writes)
- Use composite indexes for multi-column queries

## Connection Pool Configuration

### HikariCP (Java - Spring Boot)
```properties
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=600000
spring.datasource.hikari.connection-timeout=30000
```

### Recommendations
- **Pool size**: (CPU cores × 2) + effective_spindle_count
- **Timeout**: 30 seconds connection timeout
- **Max lifetime**: 30 minutes (less than DB timeout)
- **Monitor**: Active connections, wait time, timeouts

## Best Practices

1. **Always use eager loading** for collections accessed in loops
2. **Enable batch operations** for bulk inserts/updates
3. **Configure connection pools** appropriately
4. **Monitor query logs** for N+1 patterns
5. **Add indexes** for foreign keys and WHERE columns
6. **Use EXPLAIN** to analyze query plans
7. **Set LazyCollectionOption.FALSE** for critical relations
8. **Avoid SELECT \*** - specify needed columns
9. **Use pagination** for large result sets
10. **Cache frequently accessed data**

## Performance Metrics

- **Query execution time**: < 100ms for simple queries
- **N+1 patterns**: 0 (should never occur)
- **Connection pool usage**: < 80% max capacity
- **Database CPU**: < 70% under normal load
- **Index hit ratio**: > 95%
- **Cache hit ratio**: > 80% for read-heavy workloads

## Requirements

```bash
# MySQL
sudo apt-get install mysql-client

# PostgreSQL
sudo apt-get install postgresql-client

# Python tools
pip install sqlalchemy psycopg2-binary pymysql

# Java tools
# HikariCP (included in Spring Boot)
# p6spy (SQL logging)
```