# Optimize Slow "Emails" Summary Report (ID 6)

## Context

Report #6 (data source: `emails`) takes too long to load. The root causes are:

1. **4 correlated subqueries** in the SELECT clause scan the entire `lead_donotcontact` table once per email row (DNC_COLUMNS in ReportSubscriber.php lines 47-74). For N emails and M DNC rows, this is O(N*M*4).
2. **No index on `channel_id`** in `lead_donotcontact` — the LEFT JOIN and subqueries both filter on `channel_id` but no index covers it.
3. **The count query** wraps the full complex query in `SELECT count(*) FROM (...)`, re-executing all subqueries just to count rows.
4. **Missing composite index on `page_hits`** for the click-through subquery join.

The same correlated-subquery pattern is already avoided in the graph queries (lines 576-624) and the `email.stats` context (lines 278-282), which use simple aggregates on the joined DNC table. The fix makes `DNC_COLUMNS` consistent with those existing patterns.

---

## Step 1: Enable debug mode (temporary)

**File:** `config/local.php` line 555
Change `'debug' => false` to `'debug' => true`, then clear cache.

Load report #6 in the browser to capture baseline SQL and query time from the debug output. Run `EXPLAIN` on the captured SQL to confirm full table scans.

## Step 2: Add missing database indexes

Run these two DDL statements directly in MySQL:

```sql
-- Index for DNC LEFT JOIN + subqueries (channel_id is the join column, channel for equality filter, reason for covering)
CREATE INDEX dnc_channel_id_channel_reason
ON lead_donotcontact (channel_id, channel, reason);

-- Index for click-through subquery (page_hits INNER JOIN channel_url_trackables)
CREATE INDEX page_hit_redirect_source_lead
ON page_hits (redirect_id, source_id, lead_id);
```

## Step 3: Rewrite DNC correlated subqueries to use the already-joined table

**File:** `docroot/app/bundles/EmailBundle/EventListener/ReportSubscriber.php` lines 47-74

Replace the 4 `DNC_COLUMNS` formulas that use `SELECT ... FROM lead_donotcontact dnc` correlated subqueries with simple aggregates on the already LEFT JOINed `dnc` table:

| Column | Old formula (correlated subquery) | New formula (aggregate on JOIN) |
|--------|-----------------------------------|--------------------------------|
| unsubscribed | `IFNULL((SELECT ROUND(SUM(IF(dnc.id IS NOT NULL AND dnc.channel_id=e.id AND dnc.reason=2,1,0)),1) FROM lead_donotcontact dnc),0)` | `IFNULL(SUM(IF(dnc.id IS NOT NULL AND dnc.reason=2,1,0)),0)` |
| unsubscribed_ratio | Same pattern with `/e.sent_count*100` | `IFNULL(ROUND(SUM(IF(dnc.id IS NOT NULL AND dnc.reason=2,1,0))/e.sent_count*100,1),'0.0')` |
| bounced | Same as unsubscribed but reason=1 | `IFNULL(SUM(IF(dnc.id IS NOT NULL AND dnc.reason=1,1,0)),0)` |
| bounced_ratio | Same pattern with `/e.sent_count*100` | `IFNULL(ROUND(SUM(IF(dnc.id IS NOT NULL AND dnc.reason=1,1,0))/e.sent_count*100,1),'0.0')` |

**Why this is safe:** The `addDNCTableForEmails()` method (line 817-828) already LEFT JOINs `lead_donotcontact dnc ON e.id = dnc.channel_id AND dnc.channel='email'`. Combined with `GROUP BY e.id` (line 360), the SUM aggregates produce identical results to the old subqueries. The graph queries already use this exact pattern.

## Step 4: Register indexes in ORM metadata

So the indexes survive any future `doctrine:schema:update`:

- **`docroot/app/bundles/LeadBundle/Entity/DoNotContact.php`** ~line 72: add `->addIndex(['channel_id', 'channel', 'reason'], 'dnc_channel_id_channel_reason')`
- **`docroot/app/bundles/PageBundle/Entity/Hit.php`** ~line 147: add `->addIndex(['redirect_id', 'source_id', 'lead_id'], 'page_hit_redirect_source_lead')`

## Step 5: Verify and disable debug

1. Clear cache, reload report #6 with debug still on
2. Compare query time (expect 10x-100x improvement)
3. Spot-check a few emails' unsubscribed/bounced counts against direct SQL
4. Set `'debug' => false` in `config/local.php`, clear cache

---

## Files to modify

| File | Change |
|------|--------|
| `config/local.php:555` | Toggle debug (temporary) |
| `docroot/app/bundles/EmailBundle/EventListener/ReportSubscriber.php:47-74` | Rewrite DNC_COLUMNS formulas |
| `docroot/app/bundles/LeadBundle/Entity/DoNotContact.php:~72` | Add index metadata |
| `docroot/app/bundles/PageBundle/Entity/Hit.php:~147` | Add index metadata |

## Verification

- Load report #6 — should load in <2s instead of timing out
- Debug output confirms no correlated subqueries in SQL
- DNC counts match: `SELECT COUNT(*) FROM lead_donotcontact WHERE channel_id=<email_id> AND channel='email' AND reason=1|2`
- `SHOW INDEX FROM lead_donotcontact` and `SHOW INDEX FROM page_hits` confirm new indexes