Agent skill
database-optimization
Optimize SQL queries, analyze indexes, review Alembic migrations, and identify N+1 problems. Provides query execution plans, index recommendations, and migration best practices for SQLAlchemy async.
Install this agent skill to your Project
npx add-skill https://github.com/ils15/mythic-agents/tree/main/skills/database-optimization
SKILL.md
Database Optimization Skill
When to Use
Use this skill when:
- Optimizing slow SQL queries
- Analyzing missing or redundant indexes
- Reviewing Alembic migrations for safety
- Identifying N+1 query problems
- Designing database schemas
- Planning data migrations
- Reviewing query execution plans
Optimization Checklist
1. Index Analysis
-- Check missing indexes
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'your_table';
-- Identify unused indexes
SELECT * FROM pg_stat_user_indexes
WHERE idx_scan = 0;
Index Recommendations:
✅ Always index:
- Primary keys (automatic)
- Foreign keys
- Columns in WHERE clauses
- Columns in ORDER BY
- Columns in JOIN conditions
❌ Avoid indexing:
- Low cardinality columns (boolean, status)
- Frequently updated columns
- Small tables (<1000 rows)
2. N+1 Query Detection
# ❌ N+1 Problem
users = await session.execute(select(User))
for user in users:
# This triggers N additional queries!
orders = await session.execute(
select(Order).where(Order.user_id == user.id)
)
# ✅ Solution: Eager loading
from sqlalchemy.orm import selectinload
users = await session.execute(
select(User).options(selectinload(User.orders))
)
3. Query Optimization Patterns
# ✅ Use pagination
from sqlalchemy import select
from app.models import Product
async def get_products(skip: int = 0, limit: int = 20):
query = select(Product).offset(skip).limit(limit)
result = await session.execute(query)
return result.scalars().all()
# ✅ Use specific columns (not SELECT *)
query = select(Product.id, Product.name, Product.price)
# ✅ Use exists() for existence checks
from sqlalchemy import exists
query = select(exists().where(User.email == email))
4. Migration Safety
# ✅ Safe migration patterns
def upgrade():
# Add column with default (no table lock)
op.add_column('users', sa.Column('status', sa.String(20),
server_default='active'))
# ❌ Dangerous patterns
def upgrade():
# Avoid: Rename column (breaks app)
op.alter_column('users', 'name', new_column_name='full_name')
# Avoid: Change column type (data loss risk)
op.alter_column('users', 'age', type_=sa.String())
5. Async Best Practices
# ✅ Proper async session handling
from sqlalchemy.ext.asyncio import AsyncSession
async def get_user(session: AsyncSession, user_id: int):
result = await session.execute(
select(User).where(User.id == user_id)
)
return result.scalar_one_or_none()
# ✅ Bulk operations
async def bulk_insert(session: AsyncSession, items: list):
session.add_all(items)
await session.commit()
Output Format
## Database Optimization Report
### Query Analysis
- Queries analyzed: X
- Slow queries (>100ms): X
- N+1 problems: X
### Index Recommendations
| Table | Column | Type | Reason |
|-------|--------|------|--------|
| users | email | UNIQUE | WHERE clause filter |
| orders | user_id | INDEX | Foreign key JOIN |
### Optimization Suggestions
1. [Query] - [Current time] - [Optimized time] - [How]
### Migration Review
- ✅ Safe to run
- ⚠️ Requires maintenance window
- ❌ Breaking change detected
Example Usage
@database Optimize the slow query in order_service.py:120
@database Review the new migration for safety issues
@database Find N+1 problems in the user module
@database Suggest indexes for the products table
Recommended Agent Skills
Expand your agent's capabilities with these related and highly-rated skills.
ils15/mythic-agents
orchestration-workflow
Practical step-by-step walkthrough for orchestrating features end-to-end using the multi-agent system, from planning through deployment
ils15/mythic-agents
fastapi-async-patterns
Create async FastAPI endpoints with proper error handling, dependency injection, service/repository patterns, and type safety. Includes async database queries, pagination, filtering, and Gemini integration.
ils15/mythic-agents
frontend-analyzer
Analyze React/Next.js components to extract typography, colors, layout, fonts, spacing systems, and design tokens. Identifies accessibility issues, responsive breakpoints, and component hierarchies.
ils15/mythic-agents
performance-optimization
Performance optimization - query analysis, N+1 detection, caching, indexing strategies
ils15/mythic-agents
remote-sensing-analysis
ils15/mythic-agents
internet-search
Web research and internet search — source trust hierarchy, structured API patterns for general web (DuckDuckGo, Wikipedia, Jina Reader), tech community (Stack Overflow, Hacker News, Reddit, Dev.to), official vendor docs, academic databases (Semantic Scholar, CrossRef, arXiv), GitHub search, package registries, and remote sensing data sources. All sources are free and require no API key. Covers query construction, site-scoped search, parallel search strategy, and result synthesis.
Didn't find tool you were looking for?