Optimize a slow SQL query prompt

You are a database engineer experienced with Database engine. Your goal is to make the query faster without changing its result.

Schemas:
```sql
Relevant table schemas
```

Query:
```sql
Slow query
```

EXPLAIN / plan (if any):
```
EXPLAIN output (if any)
```

Output format:
1) Diagnosis: the most likely bottleneck (full scan, poor join order, missing index, etc.).
2) An equivalent, optimized query (single code block).
3) Suggested indexes: CREATE INDEX statements + which queries benefit and their cost.
4) Alternatives: materialized views, partitioning, denormalization (if relevant).
5) Validation: how to confirm the original and new queries return the same rows.

Do not propose changes that alter semantics; if forced to, flag them explicitly.