database-optimization-analyzer_skill

Overview

This skill analyzes and optimizes SQL and NoSQL databases for schema design, query performance, and indexing strategies. I provide quick actionable fixes for common anti-patterns and deeper execution-plan driven recommendations for PostgreSQL, MySQL, MongoDB, and Redis. Outputs include prioritized findings, concrete DDL/commands, and an implementation roadmap.

How this skill works

I start with input validation and detect database type from the query or explicit parameter. For fast-path checks I scan for common anti-patterns (SELECT *, missing WHERE, leading wildcards, functions on indexed columns, cartesian joins) and produce 3–5 quick wins with code. For extended analysis I parse EXPLAIN/EXPLAIN ANALYZE formats, review schema and data types, evaluate index selectivity, and produce prioritized index, query-rewrite, and schema recommendations with estimated impact and authoritative sources.

When to use it

• Slow query identified (OLTP >100ms, OLAP >5s)
• Performance regression after schema changes or deployment
• Schema review before production or migration planning
• When index strategy needs validation or selectivity issues appear
• When multiple related queries suggest overlapping index opportunities

Best practices

• Provide execution plan output and basic schema (information_schema or collection sample) for T2/T3 analyses
• Prefer non-blocking index creation (CONCURRENTLY in PostgreSQL) on large tables
• Right-size data types and avoid SELECT * in production queries
• Use composite/partial indexes to cover WHERE + ORDER BY patterns where appropriate
• Measure before and after: capture rows examined, execution_time_ms, and p95 latency for benchmarks

Example use cases

• T1 quick fix: replace SELECT * and add composite index for WHERE+ORDER BY to reduce scanned rows
• T2 execution-plan driven: parse EXPLAIN ANALYZE to detect sequential scans and recommend CREATE INDEX with DDL and impact estimate
• T3 workload analysis: analyze multiple queries to propose shared covering indexes and partitioning for large tables
• MongoDB: recommend compound index ordering per ESR rule and adjust aggregation pipeline ($match early)
• Redis: replace KEYS with SCAN and suggest pipelining or TTLs for ephemeral data