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-report.json
View on GitHub{
"schema_version": "2.0",
"meta": {
"generated_at": "2026-07-05T11:39:56.488Z",
"slug": "89jobrien-database-optimization",
"source_url": "https://github.com/89jobrien/steve/tree/main/steve/skills/database-optimization",
"source_ref": "main",
"model": "codex",
"analysis_version": "3.0.0",
"source_type": "community",
"content_hash": "9435d58003bbb43a8aee79e2281a233aa9af93a17e68c2b4905fe2617c70fe4c",
"tree_hash": "562bbfa717ee74a9fdd9bb6430944b47cab4057e27e6045b9a89120f18d97067",
"provenance": {
"model_effective": "codex",
"fallback_chain": [
{
"agent": "codex",
"outcome": "success"
}
],
"analysis_version": "3.0.0"
}
},
"skill": {
"name": "database-optimization",
"description": "SQL query optimization and database performance specialist for slow queries, N+1 access patterns, index design, caching, migrations, and monitoring.",
"summary": "Optimizes SQL queries, indexes, caching patterns, and N+1 database access issues.",
"icon": "📦",
"version": "1.0.2",
"author": "Joseph OBrien",
"license": "MIT",
"tags": [
"sql",
"database-performance",
"indexing",
"query-optimization",
"caching"
],
"supported_tools": [
"claude",
"codex",
"claude-code"
],
"risk_factors": [
"external_commands"
],
"category": "data"
},
"security_audit": {
"risk_level": "safe",
"is_blocked": false,
"safe_to_publish": true,
"analysis_status": "ok",
"summary": "The static command execution alerts are Markdown code fences and database examples, not executable shell use. The reconnaissance alerts are SQL optimization examples about SELECT patterns and indexing. No prompt injection, data exfiltration, or malicious intent was found in SKILL.md or references/query-patterns.md.",
"remediation": [],
"risk_factor_evidence": [
{
"factor": "external_commands",
"evidence": [
{
"file": "SKILL.md",
"line_start": 40,
"line_end": 42
},
{
"file": "SKILL.md",
"line_start": 42,
"line_end": 44
},
{
"file": "SKILL.md",
"line_start": 44,
"line_end": 46
},
{
"file": "SKILL.md",
"line_start": 46,
"line_end": 50
},
{
"file": "SKILL.md",
"line_start": 50,
"line_end": 52
},
{
"file": "SKILL.md",
"line_start": 52,
"line_end": 80
},
{
"file": "SKILL.md",
"line_start": 80,
"line_end": 88
},
{
"file": "SKILL.md",
"line_start": 88,
"line_end": 98
},
{
"file": "SKILL.md",
"line_start": 98,
"line_end": 102
},
{
"file": "SKILL.md",
"line_start": 102,
"line_end": 106
},
{
"file": "SKILL.md",
"line_start": 106,
"line_end": 115
},
{
"file": "SKILL.md",
"line_start": 115,
"line_end": 123
},
{
"file": "SKILL.md",
"line_start": 123,
"line_end": 130
},
{
"file": "SKILL.md",
"line_start": 130,
"line_end": 144
}
]
}
],
"static_findings": [
{
"id": "blocker:references/query-patterns.md:35:system-reconnaissance",
"category": "blocker",
"severity": "low",
"pattern": "System reconnaissance",
"file": "references/query-patterns.md",
"line_start": 35,
"line_end": 35,
"snippet": "SELECT * FROM users WHERE id = 1;"
},
{
"id": "blocker:references/query-patterns.md:38:system-reconnaissance",
"category": "blocker",
"severity": "low",
"pattern": "System reconnaissance",
"file": "references/query-patterns.md",
"line_start": 38,
"line_end": 38,
"snippet": "SELECT id, email, name FROM users WHERE id = 1;"
},
{
"id": "blocker:references/query-patterns.md:97:system-reconnaissance",
"category": "blocker",
"severity": "low",
"pattern": "System reconnaissance",
"file": "references/query-patterns.md",
"line_start": 97,
"line_end": 97,
"snippet": "-- Include all columns needed by query to avoid table lookup"
},
{
"id": "blocker:references/query-patterns.md:162:system-reconnaissance",
"category": "blocker",
"severity": "low",
"pattern": "System reconnaissance",
"file": "references/query-patterns.md",
"line_start": 162,
"line_end": 162,
"snippet": "user = db.query(\"SELECT * FROM users WHERE id = %s\", user_id)"
},
{
"id": "external_commands:SKILL.md:40:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 40,
"line_end": 42,
"snippet": "```"
},
{
"id": "external_commands:SKILL.md:42:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 42,
"line_end": 44,
"snippet": "```"
},
{
"id": "external_commands:SKILL.md:44:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 44,
"line_end": 46,
"snippet": "```"
},
{
"id": "external_commands:SKILL.md:46:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 46,
"line_end": 50,
"snippet": "```"
},
{
"id": "external_commands:SKILL.md:50:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 50,
"line_end": 52,
"snippet": "```"
},
{
"id": "external_commands:SKILL.md:52:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 52,
"line_end": 80,
"snippet": "```"
},
{
"id": "external_commands:SKILL.md:80:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 80,
"line_end": 88,
"snippet": "```python"
},
{
"id": "external_commands:SKILL.md:88:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 88,
"line_end": 98,
"snippet": "```"
},
{
"id": "external_commands:SKILL.md:98:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 98,
"line_end": 102,
"snippet": "```markdown"
},
{
"id": "external_commands:SKILL.md:102:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 102,
"line_end": 106,
"snippet": "```sql"
},
{
"id": "external_commands:SKILL.md:106:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 106,
"line_end": 115,
"snippet": "```"
},
{
"id": "external_commands:SKILL.md:115:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 115,
"line_end": 123,
"snippet": "```sql"
},
{
"id": "external_commands:SKILL.md:123:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 123,
"line_end": 130,
"snippet": "```"
},
{
"id": "external_commands:SKILL.md:130:ruby-shell-backtick-execution",
"category": "external_commands",
"severity": "medium",
"pattern": "Ruby/shell backtick execution",
"file": "SKILL.md",
"line_start": 130,
"line_end": 144,
"snippet": "```"
},
{
"id": "blocker:SKILL.md:64:system-reconnaissance",
"category": "blocker",
"severity": "low",
"pattern": "System reconnaissance",
"file": "SKILL.md",
"line_start": 64,
"line_end": 64,
"snippet": "- Avoid SELECT *"
},
{
"id": "blocker:SKILL.md:72:system-reconnaissance",
"category": "blocker",
"severity": "low",
"pattern": "System reconnaissance",
"file": "SKILL.md",
"line_start": 72,
"line_end": 72,
"snippet": "- Avoid over-indexing"
}
],
"finding_verdicts": [
{
"id": "blocker:references/query-patterns.md:35:system-reconnaissance",
"verdict": "false_positive",
"confidence": 0.98,
"reason": "This is a sample SQL anti-pattern showing SELECT * in a database tutorial. It does not perform host, network, or account reconnaissance."
},
{
"id": "blocker:references/query-patterns.md:38:system-reconnaissance",
"verdict": "false_positive",
"confidence": 0.98,
"reason": "This line is the paired optimized SQL example for selecting specific user columns. It is instructional database content, not reconnaissance behavior."
},
{
"id": "blocker:references/query-patterns.md:97:system-reconnaissance",
"verdict": "false_positive",
"confidence": 0.99,
"reason": "The line explains a covering index pattern for reducing table lookups. It contains no discovery command or collection of system information."
},
{
"id": "blocker:references/query-patterns.md:162:system-reconnaissance",
"verdict": "false_positive",
"confidence": 0.97,
"reason": "This is a Redis caching example that reads one user row by id. It is database optimization guidance, not system reconnaissance."
},
{
"id": "external_commands:SKILL.md:40:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.99,
"reason": "The finding points to a Markdown fence around a prompt example. It is not Ruby backtick execution and does not invoke a shell."
},
{
"id": "external_commands:SKILL.md:42:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.99,
"reason": "The finding points to a Markdown fence ending one prompt example. It is formatting only and cannot execute a command."
},
{
"id": "external_commands:SKILL.md:44:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.99,
"reason": "The finding points to a Markdown fence around a N+1 prompt example. It is documentation formatting, not command execution."
},
{
"id": "external_commands:SKILL.md:46:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.99,
"reason": "The referenced line is a Markdown code fence after a plain text prompt. There is no shell command or dynamic command construction."
},
{
"id": "external_commands:SKILL.md:50:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.99,
"reason": "The line opens a Markdown fence for an analysis prompt. It is not executable code and contains no backtick shell invocation."
},
{
"id": "external_commands:SKILL.md:52:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.99,
"reason": "The line closes a Markdown fence for a prompt example. It is static documentation and does not request external command execution."
},
{
"id": "external_commands:SKILL.md:80:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.98,
"reason": "The line opens a Python code block showing ORM N+1 behavior. The surrounding example is illustrative and does not execute commands."
},
{
"id": "external_commands:SKILL.md:88:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.98,
"reason": "The line closes the Python example code fence. It is Markdown syntax, not Ruby shell backtick execution."
},
{
"id": "external_commands:SKILL.md:98:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.98,
"reason": "The line opens a Markdown output example. It documents expected analysis output and does not run external commands."
},
{
"id": "external_commands:SKILL.md:102:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.98,
"reason": "The line opens a SQL snippet within the documented output example. SQL text in documentation is not shell command execution."
},
{
"id": "external_commands:SKILL.md:106:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.98,
"reason": "The line closes a SQL documentation block. It is markup and has no execution path."
},
{
"id": "external_commands:SKILL.md:115:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.98,
"reason": "The line opens another SQL snippet for index creation guidance. It is static instructional text, not a shell command."
},
{
"id": "external_commands:SKILL.md:123:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.98,
"reason": "The line closes a SQL documentation block in an example. It does not invoke Ruby or any command interpreter."
},
{
"id": "external_commands:SKILL.md:130:ruby-shell-backtick-execution",
"verdict": "false_positive",
"confidence": 0.98,
"reason": "The line closes the larger Markdown output example. It is a code fence delimiter and is not executable."
},
{
"id": "blocker:SKILL.md:64:system-reconnaissance",
"verdict": "false_positive",
"confidence": 0.99,
"reason": "The line advises avoiding SELECT * as a database performance practice. It does not request discovery of system information."
},
{
"id": "blocker:SKILL.md:72:system-reconnaissance",
"verdict": "false_positive",
"confidence": 0.99,
"reason": "The line warns against over-indexing during index design. It is performance guidance, not system reconnaissance."
}
],
"semantic_findings": [],
"critical_findings": [],
"high_findings": [],
"medium_findings": [],
"low_findings": [],
"dangerous_patterns": [],
"files_scanned": 2,
"total_lines": 325,
"audit_model": "codex",
"audited_at": "2026-07-05T11:39:56.488Z",
"agent_auto_install_policy": "allowed",
"manual_install_policy": "allowed"
},
"content": {
"user_title": "Optimize Database Performance",
"value_statement": "Slow queries and weak indexes can make applications costly and unreliable. This skill guides SQL tuning, index design, caching, and N+1 fixes.",
"seo_keywords": [
"Claude",
"Codex",
"Claude Code",
"database optimization",
"SQL query tuning",
"index design",
"N+1 query fix",
"PostgreSQL performance",
"MySQL optimization",
"Redis caching"
],
"actual_capabilities": [
"Reviews slow SQL queries and explains likely performance bottlenecks.",
"Suggests indexes for common WHERE, JOIN, sort, and covering index patterns.",
"Identifies N+1 access patterns in ORM code examples.",
"Recommends caching strategies using Redis or similar cache layers.",
"Guides migration and monitoring improvements for database performance."
],
"limitations": [
"It does not connect to live databases or collect runtime metrics by itself.",
"Recommendations depend on the schema, query plan, and workload details provided.",
"It cannot guarantee production speedups without testing on real data.",
"It focuses on relational databases and common cache patterns."
],
"use_cases": [
{
"title": "Tune slow customer queries",
"description": "Review a slow query, explain likely causes, and suggest safer SQL and indexes.",
"target_user": "Backend engineer"
},
{
"title": "Reduce ORM query volume",
"description": "Find N+1 patterns in application code and suggest eager loading or joins.",
"target_user": "Full stack developer"
},
{
"title": "Plan database performance work",
"description": "Create a practical optimization checklist for indexes, caching, monitoring, and migrations.",
"target_user": "Technical lead"
}
],
"prompt_templates": [
{
"title": "Review a slow query",
"scenario": "Beginner query tuning",
"prompt": "Review this slow SQL query. Explain the main bottlenecks and suggest simple improvements."
},
{
"title": "Suggest useful indexes",
"scenario": "Index design",
"prompt": "Given this schema and query workload, recommend indexes and explain why each index helps."
},
{
"title": "Fix N+1 database access",
"scenario": "Application performance",
"prompt": "Analyze this ORM code for N+1 query patterns. Suggest eager loading, joins, or batching where appropriate."
},
{
"title": "Create an optimization plan",
"scenario": "Advanced performance review",
"prompt": "Build a database optimization plan using these queries, schema details, query plans, and traffic patterns."
}
],
"output_examples": [
{
"input": "A user lookup query scans a large table and filters by email.",
"output": [
"The likely issue is a missing index on the email column.",
"Add a focused index and return only needed columns.",
"Measure the change with the database query planner before release."
]
},
{
"input": "A page loads users, then loads posts for each user inside a loop.",
"output": [
"This is an N+1 access pattern because each user triggers another query.",
"Use eager loading, prefetching, or a join based on the ORM and response shape.",
"Verify total query count and response time after the change."
]
},
{
"input": "A dashboard repeats expensive aggregate queries for every request.",
"output": [
"Cache the aggregate result with a clear expiration policy.",
"Invalidate or refresh the cache when related source data changes.",
"Track cache hit rate and stale data risk during rollout."
]
}
],
"best_practices": [
"Share query plans, table sizes, existing indexes, and workload patterns for better recommendations.",
"Measure before and after each change using production-like data and realistic traffic.",
"Balance read speed, write cost, storage growth, and operational complexity."
],
"anti_patterns": [
"Do not add indexes without checking query patterns and write impact.",
"Do not cache database results without an invalidation or freshness plan.",
"Do not optimize one query while ignoring total request query count."
],
"faq": [
{
"question": "Which databases does this skill support?",
"answer": "It is useful for PostgreSQL, MySQL, and similar relational databases with SQL query plans."
},
{
"question": "Can it read my production database directly?",
"answer": "No. Provide sanitized schema details, queries, and query plans for review."
},
{
"question": "Does it write database migrations?",
"answer": "It can suggest migration strategies and index changes, but you should test them before deployment."
},
{
"question": "Can it help with N+1 problems?",
"answer": "Yes. It can identify repeated query patterns and suggest eager loading, joins, or batching."
},
{
"question": "Does it replace database monitoring tools?",
"answer": "No. It complements monitoring by turning query plans and slow query logs into action items."
},
{
"question": "What should I include in a prompt?",
"answer": "Include the query, schema, indexes, row counts, query plan, database type, and performance goal."
}
]
},
"file_structure": [
{
"name": "references",
"type": "dir",
"path": "references",
"children": [
{
"name": "query-patterns.md",
"type": "file",
"path": "references/query-patterns.md",
"lines": 172
}
]
},
{
"name": "SKILL.md",
"type": "file",
"path": "SKILL.md",
"lines": 153
}
]
}
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 |