Database Query Optimization

You are a database performance expert. Optimize the following slow query.

**Database:** {database_type}
**Current Query:**
```sql
{slow_query}
```

**Performance Metrics:**
- Current execution time: {current_time}
- Rows examined: {rows_examined}
- Rows returned: {rows_returned}
- Query frequency: {query_frequency}

**Table Schemas:**
```sql
{table_schemas}
```

**Existing Indexes:**
{existing_indexes}

Optimize using this methodology:

**1. Analysis:**
Run EXPLAIN output interpretation:
- Identify full table scans
- Note missing indexes
- Spot inefficient JOINs
- Check for filesorts/temporary tables

**2. Optimization Strategy:**
Prioritize fixes by impact:
- **Quick Wins** (< 1 hour):
  - Add missing indexes
  - Rewrite subqueries as JOINs
  - Fix OR conditions
- **Medium Effort** (1-4 hours):
  - Denormalization opportunities
  - Materialized views
  - Query restructuring
- **Long-term** (> 1 day):
  - Schema redesign
  - Partitioning
  - Caching layer

**3. Optimized Query:**
```sql
-- Optimized version with comments explaining changes
{optimized_query}
```

**4. Required Index Changes:**
```sql
CREATE INDEX idx_name ON table(column1, column2)
  WHERE condition; -- Partial index if applicable
```

Explain:
- Why this index order matters
- Estimated index size
- Tradeoff with write performance

**5. Performance Comparison:**
| Metric | Before | After | Improvement |
|--------|--------|-------|-------------|
| Execution time | {current_time} | {estimated_time} | {improvement}% |
| Rows examined | {rows_examined} | {estimated_examined} | ... |

**6. Monitoring:**
- Query to add to slow query log
- Metrics to track
- When to revisit (data growth threshold)

**7. Alternative Approaches:**
If query is still slow:
- Caching strategy (Redis, Memcached)
- Read replica offloading
- Data archiving strategy

Provide actual SQL, not pseudocode.