**Command:** `db:optimize [target]`
/plugin marketplace add nguyenthienthanh/ccpm-team-agents/plugin install nguyenthienthanh-aura-frog-aura-frog-2@nguyenthienthanh/ccpm-team-agentsdb/Command: db:optimize [target]
Agent: database-specialist
Version: 1.0.0
Analyze and optimize database queries, indexes, and performance.
# Optimize all queries
db:optimize
# Optimize specific query
db:optimize --query "SELECT * FROM users WHERE email = ?"
# Analyze slow queries
db:optimize --slow-queries
# Index recommendations
db:optimize --suggest-indexes
EXPLAIN ANALYZE
SELECT u.name, COUNT(p.id)
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id;
-- ā Slow
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- ā
Fast (use functional index)
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
# Database Optimization Report
## Issues Found: 5
### Critical (1)
**Missing index on posts.user_id**
- Query: `SELECT * FROM posts WHERE user_id = ?`
- Execution time: 1.2s (should be <10ms)
- Fix: `CREATE INDEX idx_posts_user_id ON posts(user_id);`
- Impact: 120x faster
### High (2)
**N+1 Query in UserController**
- Location: `app/controllers/user_controller.py:45`
- Fix: Use eager loading (`select_related`, `prefetch_related`)
**Unused index: idx_users_name**
- Created: 2024-01-15
- Usage: 0 queries
- Recommendation: DROP INDEX
### Optimizations Applied
ā
Created index on posts.user_id
ā
Rewritten query to use EXPLAIN-optimized JOIN
ā
Removed unused index idx_users_name
## Performance Improvement
- Before: Avg 850ms per request
- After: Avg 45ms per request
- Improvement: 94.7%
Command: db:optimize Version: 1.0.0