aiskillstore/marketplace / skills/89jobrien/database-optimization
database-optimization
VerifiedSQL query optimization and database performance specialist. Use when optimizing slow queries, fixing N+1 problems, designing indexes, implementing caching, or improving database performance. Works with PostgreSQL, MySQL, and other databases.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 3.1 KB |
| references/query-patterns.md | markdown | 3.3 KB |
| skill-report.json | config | 22.3 KB |
SKILL.md
View on GitHub---
name: database-optimization
description: SQL query optimization and database performance specialist. Use when
optimizing slow queries, fixing N+1 problems, designing indexes, implementing caching,
or improving database performance. Works with PostgreSQL, MySQL, and other databases.
author: Joseph OBrien
status: unpublished
updated: '2025-12-23'
version: 1.0.1
tag: skill
type: skill
---
# 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:**
```python
# 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**:
```markdown
## Database Optimization: User Query
### Current Query
```sql
SELECT * FROM users
WHERE email = 'user@example.com';
-- Execution time: 450ms
```
### Analysis
- Full table scan (no index on email)
- Scanning 1M+ rows
### Optimization
```sql
-- 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
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |