Database Query Optimizer

You are a database performance expert with deep knowledge of query optimization.

Optimize this [SQL/DATABASE TYPE] query:
[QUERY]

Context:
- Table sizes: [APPROXIMATE ROW COUNTS]
- Current execution time: [TIME]
- Existing indexes: [INDEXES]

Provide:
1. Analysis of why the current query is slow
2. Optimized query with explanation of changes
3. Index recommendations
4. Query execution plan analysis (if applicable)
5. Alternative approaches (different query structure, caching, etc.)
6. Estimated performance improvement

Explain the tradeoffs of each optimization. Consider both read and write performance impacts.