Database Optimization Analyzer

## Purpose & When-To-Use

Use this skill when:
- Slow query identified (execution time >100ms for OLTP, >5s for OLAP)
- Database performance degradation observed
- Schema design review needed before production deployment
- Query optimization required for scalability
- Index strategy needs validation
- Database migration planning (SQL to NoSQL or vice versa)
- Performance regression analysis after schema changes

Do not use for:
- Database administration tasks (backup, replication, failover)
- Database installation or configuration
- Security hardening (use security-assessment-framework)
- Data modeling or ER diagram generation

## Pre-Checks

1. **Time Normalization**: `NOW_ET = 2025-10-25T22:10:45-04:00` (NIST/time.gov semantics)
2. **Input Validation**:
   - Database type must be one of: postgresql, mysql, mongodb, redis, or specify 'other'
   - Query text must be non-empty string
   - If execution_plan provided, must match database type format
3. **Source Freshness**:
   - PostgreSQL docs accessed: 2025-10-25T22:10:45-04:00
   - MySQL docs accessed: 2025-10-25T22:10:45-04:00
   - MongoDB docs accessed: 2025-10-25T22:10:45-04:00
   - Redis docs accessed: 2025-10-25T22:10:45-04:00
   - use-the-index-luke.com accessed: 2025-10-25T22:10:45-04:00

## Procedure

### T1: Fast Path (≤2k tokens, 80% common cases)

**Scope**: Quick wins for common query anti-patterns without deep execution plan analysis.

**Steps**:
1. **Detect Database Type**: Parse query syntax or use provided database_type
2. **Quick Pattern Scan**:
   - SELECT * usage (recommend explicit columns)
   - Missing WHERE clause on large tables
   - LIKE with leading wildcard (%pattern)
   - Functions on indexed columns (e.g., WHERE YEAR(date_col) = 2025)
   - Cartesian joins (missing JOIN conditions)
   - Subqueries that could be JOINs
3. **Index Hints** (generic):
   - Columns in WHERE, JOIN ON, ORDER BY, GROUP BY clauses
   - Composite index recommendations based on query predicates
4. **Output**: 3-5 actionable quick wins with implementation code

**Abort Conditions**:
- Query syntax invalid for specified database type
- Performance metrics contradict issue (query already fast)

### T2: Extended Analysis (≤6k tokens, deep optimization)

**Scope**: Detailed execution plan analysis, schema review, and database-specific optimizations.

**Steps**:
1. **Execution Plan Analysis** (if provided):
   - **PostgreSQL**: Parse EXPLAIN ANALYZE output
     - Identify Seq Scan on large tables → recommend index
     - Nested Loop with high row estimates → consider Hash Join
     - Sort operations → evaluate index for ORDER BY
     - High cost nodes → focus optimization effort
   - **MySQL**: Parse EXPLAIN output
     - type=ALL (full table scan) → add index
     - Extra='Using filesort' → index for ORDER BY
     - Extra='Using temporary' → optimize GROUP BY
     - rows examined vs rows returned ratio
   - **MongoDB**: Parse explain() output
     - COLLSCAN (collection scan) → create index
     - IXSCAN with high nReturned/totalKeysExamined → index selectivity issue
     - SORT stage → compound index with sort fields
   - **Redis**: Analyze command patterns
     - KEYS command → use SCAN for large keyspaces
     - Large SET/LIST operations → consider pipelining
     - Missing TTL on ephemeral data

2. **Schema Design Review**:
   - **Normalization Issues**:
     - Over-normalization causing excessive JOINs → selective denormalization
     - Under-normalization causing data redundancy → normalize
   - **Data Types**:
     - Oversized VARCHAR → right-size
     - Using TEXT for short strings → use VARCHAR
     - Numeric precision mismatches
   - **Partitioning Opportunities**:
     - Tables >10M rows → consider partitioning by date/range
     - Time-series data → partition by month/quarter

3. **Database-Specific Optimizations**:
   - **PostgreSQL**:
     - Partial indexes for filtered queries
     - GIN/GiST indexes for full-text search, JSON
     - Covering indexes to avoid table lookups
     - Materialized views for complex aggregations
     - (Source: https://www.postgresql.org/docs/current/indexes-types.html, accessed 2025-10-25T22:10:45-04:00)
   - **MySQL**:
     - InnoDB buffer pool sizing
     - Query cache considerations (deprecated in 8.0)
     - Index merge optimization
     - (Source: https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html, accessed 2025-10-25T22:10:45-04:00)
   - **MongoDB**:
     - ESR rule (Equality, Sort, Range) for compound indexes
     - Index intersection vs compound index trade-offs
     - Aggregation pipeline optimization ($match early, $project to reduce data)
     - (Source: https://www.mongodb.com/docs/manual/core/index-compound/, accessed 2025-10-25T22:10:45-04:00)
   - **Redis**:
     - Hash vs String for structured data
     - Sorted Sets for ranking/leaderboards
     - Pub/Sub vs Streams for messaging
     - (Source: https://redis.io/docs/latest/develop/data-types/, accessed 2025-10-25T22:10:45-04:00)

4. **N+1 Query Detection**:
   - Identify repeated queries with different parameters in application logs
   - Recommend batch loading (IN clause, JOIN, or eager loading)

5. **Output**: Detailed optimization report with:
   - Prioritized recommendations (high/medium/low impact)
   - Specific DDL statements for indexes
   - Query rewrites with before/after comparison
   - Estimated performance improvement (based on row reduction, index usage)

**Decision Rules**:
- If execution plan shows >80% time in Seq Scan → HIGH priority index recommendation
- If query returns <10% of scanned rows → index selectivity issue or missing predicate
- If schema has >5 levels of normalization → evaluate denormalization candidates
- If MongoDB query scans >10k documents to return <100 → create compound index

### T3: Deep Dive (≤12k tokens, comprehensive analysis)

**Scope**: Multi-query optimization, workload analysis, and migration recommendations.

**Steps**:
1. **Workload Pattern Analysis**:
   - Analyze multiple queries as a workload
   - Identify overlapping index opportunities
   - Detect conflicting index requirements (OLTP vs OLAP)
   - Recommend read replicas or caching for read-heavy workloads

2. **Advanced Index Strategies**:
   - Covering indexes to eliminate table lookups
   - Partial/filtered indexes to reduce index size
   - Index-only scans (PostgreSQL) or loose index scans (MySQL)
   - Multi-column index order optimization

3. **Migration Guidance**:
   - SQL to NoSQL candidates (high read/write ratio, flexible schema)
   - NoSQL to SQL candidates (complex transactions, strong consistency)
   - Polyglot persistence recommendations (right database for right use case)

4. **Benchmarking Recommendations**:
   - Generate pgbench (PostgreSQL), sysbench (MySQL), or YCSB (NoSQL) test scenarios
   - Define success metrics (latency p95, throughput)

5. **Output**: Comprehensive optimization strategy with implementation roadmap

**Abort Conditions**:
- Workload too diverse to analyze coherently (>10 distinct query patterns)
- Insufficient data to make reliable recommendations

## Decision Rules

1. **Index Creation Threshold**:
   - Recommend index if query scans >1000 rows to return <100 rows
   - Skip index if table <10k rows (full scan acceptable)
   - Avoid index if column cardinality <5% (low selectivity)

2. **Denormalization Threshold**:
   - Consider if query requires >3 JOINs
   - Evaluate if read:write ratio >10:1
   - Reject if data consistency critical (financial transactions)

3. **Database Type Recommendations**:
   - PostgreSQL: Complex queries, JSONB, full-text search, geospatial
   - MySQL: Simple OLTP, strong ecosystem, MariaDB compatibility
   - MongoDB: Flexible schema, horizontal scaling, document model
   - Redis: Caching, session store, real-time analytics, pub/sub

4. **Ambiguity Handling**:
   - If execution plan unclear → request EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL)
   - If schema unknown → request schema dump or information_schema query
   - If performance acceptable despite anti-patterns → note improvement potential but low priority

## Output Contract

**Schema** (JSON):
```json
{
  "analysis_timestamp": "ISO-8601 datetime",
  "database_type": "string (postgresql|mysql|mongodb|redis|other)",
  "query_analyzed": "string",
  "findings": [
    {
      "type": "string (index|query_rewrite|schema_change|configuration)",
      "severity": "string (high|medium|low)",
      "description": "string",
      "recommendation": "string",
      "implementation_code": "string (DDL or config)",
      "estimated_impact": "string (e.g., '80% reduction in rows scanned')"
    }
  ],
  "index_recommendations": [
    {
      "table_or_collection": "string",
      "index_definition": "string (CREATE INDEX ... or db.collection.createIndex(...))",
      "rationale": "string",
      "estimated_size_mb": "number (optional)"
    }
  ],
  "query_rewrites": [
    {
      "original_query": "string",
      "optimized_query": "string",
      "improvement_rationale": "string"
    }
  ],
  "next_steps": ["string array of actionable items"],
  "sources_consulted": ["string array of URLs with access dates"]
}
```

**Required Fields**:
- `analysis_timestamp`, `database_type`, `findings` (must have ≥1 finding or explain why no issues found)
- If T2/T3: `sources_consulted` must include 2-4 authoritative sources

## Examples

**Example: PostgreSQL Slow Query Optimization**

Input:
```json
{
  "database_type": "postgresql",
  "query": "SELECT * FROM orders WHERE customer_id = 12345 AND status = 'pending' ORDER BY created_at DESC",
  "performance_metrics": {
    "execution_time_ms": 2300,
    "rows_scanned": 450000,
    "rows_returned": 15
  }
}
```

Output (T1):
```json
{
  "findings": [
    {"type": "query_rewrite", "severity": "medium", "description": "SELECT * loads unnecessary columns", "recommendation": "Use explicit column list"},
    {"type": "index", "severity": "high", "description": "Seq Scan on orders (450k rows scanned for 15 returned)", "recommendation": "Create composite index on (customer_id, status, created_at)"}
  ],
  "index_recommendations": [
    {"table_or_collection": "orders", "index_definition": "CREATE INDEX idx_orders_customer_status_created ON orders(customer_id, status, created_at DESC);", "rationale": "Covers WHERE predicates and ORDER BY"}
  ]
}
```

## Quality Gates

1. **Token Budget**:
   - T1: ≤2000 tokens (quick pattern scan, generic index hints)
   - T2: ≤6000 tokens (execution plan analysis, database-specific optimizations)
   - T3: ≤12000 tokens (workload analysis, migration guidance)
   - Abort if approaching limit without actionable output

2. **Safety**:
   - Never recommend DROP INDEX without analyzing query workload impact
   - Warn about locking implications of CREATE INDEX on large tables (suggest CONCURRENTLY for PostgreSQL)
   - Flag irreversible operations (denormalization, data type changes)

3. **Auditability**:
   - Every index recommendation must cite specific query pattern
   - Execution plan findings must reference specific nodes/stages
   - Estimated impact must show calculation method

4. **Determinism**:
   - Same query + execution plan → same recommendations
   - Randomness only in example data generation, not analysis logic

5. **Source Quality**:
   - T2+ must cite 2-4 official database documentation URLs with access dates
   - use-the-index-luke.com for general indexing principles (accessed 2025-10-25T22:10:45-04:00): https://use-the-index-luke.com/

## Resources

**PostgreSQL**:
- Official Performance Tips: https://www.postgresql.org/docs/current/performance-tips.html
- Index Types: https://www.postgresql.org/docs/current/indexes-types.html
- EXPLAIN Documentation: https://www.postgresql.org/docs/current/sql-explain.html

**MySQL**:
- Optimization Guide: https://dev.mysql.com/doc/refman/8.0/en/optimization.html
- Index Optimization: https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html
- EXPLAIN Format: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

**MongoDB**:
- Query Optimization: https://www.mongodb.com/docs/manual/core/query-optimization/
- Compound Indexes: https://www.mongodb.com/docs/manual/core/index-compound/
- Explain Plans: https://www.mongodb.com/docs/manual/reference/method/cursor.explain/

**Redis**:
- Data Types: https://redis.io/docs/latest/develop/data-types/
- Patterns: https://redis.io/docs/latest/develop/use/patterns/
- Best Practices: https://redis.io/docs/latest/develop/use/optimization/

**General**:
- use-the-index-luke.com: SQL indexing fundamentals (database-agnostic)
- See `resources/` directory for query templates and index strategy guides