# DB Performance Improvement Plan

**Branch:** `adc-qa-fix`
**Date:** 2026-08-16
**Scope:** slow queries in adcenter that can saturate or stall a DB server.

## Status

Applied in the working tree (6 files, +171/−114, all `php8.2 -l` clean):

| Item | Status |
| --- | --- |
| P0 — `adcenter_log` index | **Not applied** — DDL, needs a window + prod schema check |
| P0 — `LIMIT` on 3 history endpoints | Applied (`EVENT_LOG_HISTORY_MAX_ROWS = 500`) |
| P0 — extract shared history repository | **Not applied** — structural refactor, deferred |
| P1 — `DATE()` removal (6 sites) | Applied |
| P1 — ad-format / landing-page single-pass | Applied |
| P2 — legacy top-N cap + `Others` on both paths | Applied |
| P2 — loud HeatWave fallback | Applied |
| P3 — report caps | **Not applied** — needs a product call on the date-span limit |
| P2 — statement timeouts | **Not applied** — could kill legitimate long reports |

Verified: rewritten aggregate and demographic queries return byte-identical
results to the originals (row counts, metric sums, and a `BIT_XOR(CRC32(...))`
row-content checksum all match).

---

## How these findings were verified

All findings below were confirmed with `EXPLAIN` and wall-clock timings against the
dev MySQL instance (`127.0.0.1`, MySQL 5.7.44) and the HeatWave instance
(`10.100.12.165`, MySQL 9.6.1-cloud). No prod measurements — see
[Open questions](#open-questions).

Environment facts gathered during the audit:

| Fact | Value |
| --- | --- |
| `Shorty.adcenter_log` | 17,422,075 rows / 4,628 MB / **only** `PRIMARY(id)` |
| `keywords.adv_clicks_20378_*` | 297,787,353 rows across 14 monthly shards / 79.6 GB |
| `keywords.adv_clicks_21610_new` (HeatWave) | 30,111,117 rows / 18.8 GB |
| Advertisers on legacy monthly shards | 223 total, 138 with 2026 shards |
| HeatWave `_new` tables | 138 |
| HeatWave RAPID load status | `AVAIL_RPDGSTABSTATE` for all 138 (available, **not loaded**) |
| `long_query_time` | 10s — `Slow_queries` counter at 956,164 |

---

## Severity summary

| # | Issue | Impact | Effort | Priority |
| --- | --- | --- | --- | --- |
| 1 | `adcenter_log` history: unindexed 17M-row scan, no `LIMIT`, 3 endpoints | Stalls the Shorty DB on ordinary UI clicks | S | **P0** |
| 2 | `DATE(date)` in WHERE disables the date index (6 sites) | 10–40× more rows scanned per breakdown | S | **P1** |
| 3 | Ad-format breakdown scans the full HeatWave table twice | 18.8 GB scanned per request regardless of date range | S | **P1** |
| 4 | Top-50 pre-filter is HeatWave-only; legacy path uncapped | HeatWave outage becomes a keywords-DB outage | M | **P2** |
| 5 | `ReportStatsModel` has no row cap; `GROUP_CONCAT` over shards | Huge on-disk temp tables on wide reports | M | **P3** |
| 6 | Ops: slow log unreadable, RAPID not loaded, no query timeouts | No visibility, no backstop | S | **P2** |

---

## P0 — `Shorty.adcenter_log` history queries

### Problem

Three endpoints run the same query shape against a 4.6 GB table that has no index
on the filtered columns and no row cap:

- [Campaign.php:2588-2605](html/app/Controllers/Campaign.php#L2588-L2605) — `POST api/campaign/history/(:num)`
- [Creative.php:656-673](html/app/Controllers/Creative.php#L656-L673) — `POST api/creative/history/(:num)`
- [Lists.php:163-177](html/app/Controllers/Lists.php#L163-L177) — `POST lists/history/(:segment)/(:num)`

```sql
SELECT id, old_value, new_value, user, time_stamp, action, ip
FROM adcenter_log
WHERE entity_id = ? AND entity IN ('Campaign','campaign')
ORDER BY id DESC
```

`EXPLAIN`:

```
type: index   possible_keys: NULL   key: PRIMARY   rows: 17422075   filtered: 2.00
```

Measured **9.4 s** warm, returning zero rows. Each call pulls 4.6 GB of
`old_value` / `new_value` TEXT through the InnoDB buffer pool, evicting the working
set for every other query on the instance. A few concurrent History-tab opens is
enough to make the server unresponsive.

Two independent defects compound here:

1. **No usable index.** `entity` is near-useless for selectivity — `Campaign`
   alone is 15,998,131 of the 17.4M rows. `entity_id` is the selective column.
2. **No `LIMIT`.** Worst-case single campaign has **204,581** log rows (mean 212).
   Even with an index, that campaign's History tab loads 204k rows of TEXT into
   PHP and JSON-encodes them.

The existing comments at those lines about keeping `time_stamp` "sargable" are
moot: the date filter is optional, and there is no index to be sargable against.

### Fix

**Step 1 — index (do this first; it is the bulk of the win).**

```sql
ALTER TABLE Shorty.adcenter_log
  ADD INDEX idx_entity_lookup (entity_id, entity, id),
  ALGORITHM=INPLACE, LOCK=NONE;
```

Leading with `entity_id` gives the selectivity; `entity` filters the variants in
place; trailing `id` lets `ORDER BY id DESC` be satisfied from the index instead
of a filesort. On 5.7 `ADD INDEX` is an online INPLACE operation, so concurrent
DML keeps working — but it will read the whole 4.6 GB table, so run it in a low-
traffic window. Expect a few minutes and roughly 300–500 MB of added index.

**Step 2 — cap the result set in all three controllers.**

Add a bounded `LIMIT` (proposal: 500, newest-first) to each builder, and surface a
"showing most recent N entries" note in the modal. All three already
`orderBy('id','DESC')`, so the cap keeps the useful end of the history.

```php
$builder->orderBy('id', 'DESC')->limit(self::HISTORY_MAX_ROWS);
```

If the product wants full history, the modal needs real pagination — that's a
larger change and is out of scope for this pass; the cap is the safety fix.

**Step 3 — de-duplicate.** The three controllers hold three near-identical copies
of the query, the status map, and the numeric-user resolution. Extract a single
`EventLogRepository::getHistory(array $entities, int $entityId, ?string $from, ?string $to)`
so the cap and the index hint can never drift apart again. Optional but strongly
recommended — the duplication is why the missing `LIMIT` exists in triplicate.

### Verification

- `EXPLAIN` shows `type: ref`, `key: idx_entity_lookup`, `rows` in the hundreds.
- Re-time the campaign-history query: target < 50 ms.
- Open the History tab for campaign with the 204k-row log and confirm the response
  is bounded and the page renders.

### Rollback

Drop the index (`ALTER TABLE ... DROP INDEX idx_entity_lookup`) and revert the
controller diff. No data migration, so rollback is clean.

---

## P1 — `DATE(date)` in WHERE clauses disables the date index

### Problem

Six sites wrap an already-`DATE`-typed column in `DATE()` inside the WHERE clause,
which makes the predicate non-sargable:

- [BreakdownStatsModel.php:597](html/app/Models/BreakdownStatsModel.php#L597) — landing page, distinct-creative pass
- [BreakdownStatsModel.php:639](html/app/Models/BreakdownStatsModel.php#L639) — landing page, aggregate pass
- [BreakdownStatsModel.php:818](html/app/Models/BreakdownStatsModel.php#L818) — ad format, distinct-creative pass
- [BreakdownStatsModel.php:862](html/app/Models/BreakdownStatsModel.php#L862) — ad format, aggregate pass
- [BreakdownStatsModel.php:1807](html/app/Models/BreakdownStatsModel.php#L1807)
- [DemographicDataService.php:485](html/app/Services/DemographicDataService.php#L485)

Measured on both engines:

| Engine / table | `DATE(date) >= ?` | `date >= ?` |
| --- | --- | --- |
| keywords 5.7, `adv_clicks_20378_202510` | `type: ALL`, **49,120,500 rows** | `type: range`, key `date`, 4,511,540 rows |
| HeatWave 9.6, `adv_clicks_21610_new` | 31,500,000 rows, cost 2.28e9 | 790,294 rows, cost 520e6 |

That is a full-shard scan instead of an index range, multiplied by the number of
shards in the `UNION ALL`. For advertiser 20378 a 3-month legacy breakdown scans
~130M rows where ~13M would do.

### Fix

Drop `DATE()` from the WHERE predicates only. The column is already `DATE` on both
the legacy shards and the HeatWave `_new` tables, so the wrapper is pure loss:

```diff
- WHERE DATE(acc.date) >= ? AND DATE(acc.date) <= ? AND acc.status >= 1
+ WHERE acc.date >= ? AND acc.date <= ? AND acc.status >= 1
```

`DATE(date)` in SELECT/GROUP BY lists is harmless (also redundant, but changing it
risks column-alias churn in the PHP-side result handling). Leave those; the WHERE
clauses are the whole win. Confirm the bound values are plain `Y-m-d` strings at
every call site before removing the cast — they are today, but a caller passing
`Y-m-d H:i:s` would change `<=` boundary semantics.

### Verification

`EXPLAIN` each of the six rewritten queries and confirm `type: range` with
`key: date` (legacy) / a filtered row estimate in the hundreds of thousands
(HeatWave). Spot-check that breakdown totals are unchanged for a known advertiser
and date range before/after.

---

## P1 — ad-format breakdown scans the whole HeatWave table twice

### Problem

[getTimeSeriesByAdFormat](html/app/Models/BreakdownStatsModel.php#L797) does **not**
short-circuit to the HeatWave model the way
[getTimeSeriesByLandingPage](html/app/Models/BreakdownStatsModel.php#L578-L582)
does. So on HeatWave advertisers it runs against the flat `_new` table — which
holds the advertiser's entire history in one table — and it does it twice:

1. [:816-825](html/app/Models/BreakdownStatsModel.php#L816-L825) — `SELECT DISTINCT creative_id`
2. [:852-869](html/app/Models/BreakdownStatsModel.php#L852-L869) — the aggregate

Combined with the `DATE()` issue above, an ad-format breakdown on advertiser 21610
scans 18.8 GB twice regardless of the date range the user picked.

### Fix

Two changes, both small:

1. Apply the P1 `DATE()` removal so the date range actually prunes.
2. Collapse the two passes into one. The distinct-creative pass exists only to
   build the `IN (...)` list for the Admin-DB label lookup — but the aggregate
   pass already returns `creative_id` per row. Run the aggregate first, derive the
   creative-id set from its result in PHP, then do the Admin lookup. This halves
   the scan and removes a round-trip.

The same two-pass pattern exists in
[getTimeSeriesByLandingPage](html/app/Models/BreakdownStatsModel.php#L592-L646)
(legacy path only) — apply the same collapse there.

---

## P2 — top-50 pre-filter is HeatWave-only

### Problem

[BreakdownStatsModel.php:1336](html/app/Models/BreakdownStatsModel.php#L1336)
guards the high-cardinality top-N pre-filter with:

```php
if ($this->useHeatwave($advId) && $highCardinality) {
```

On the legacy path there is no cap at all — `GROUP BY DATE(date), keyword` runs
unbounded across every monthly shard. For advertiser 20378 that is 297M rows /
79.6 GB with a temp table sized by the distinct keyword count.

This is latent today because all 138 active advertisers have HeatWave tables. It
becomes live the moment HeatWave is unreachable, because
[useHeatwave()](html/app/Models/BreakdownStatsModel.php#L87-L95) catches the
exception and silently falls back to the keywords DB. A HeatWave outage therefore
redirects all breakdown traffic onto the keywords DB **with the cap removed** —
converting one outage into two.

### Fix

- Drop the `useHeatwave()` condition so the top-N pre-filter applies on both paths.
  On the legacy path the pre-filter query must itself be a `UNION ALL` across the
  shards wrapped in an outer `GROUP BY ... ORDER BY SUM(impressions) DESC LIMIT 50`.
- Make the HeatWave→legacy fallback **loud**: today it logs at `warning` and
  proceeds. It should log at `error` and, for high-cardinality dimensions on
  large advertisers, prefer returning a "temporarily unavailable" response over
  silently issuing an 80 GB scan.
- Consider a circuit breaker on `useHeatwave()` so a HeatWave blip doesn't produce
  a thundering herd against keywords.

---

## P3 — `ReportStatsModel` has no row cap

### Problem

[ReportStatsModel.php:599-612](html/app/Models/ReportStatsModel.php#L599-L612)
builds `SELECT ... FROM (UNION ALL of N shards) u GROUP BY ...` with no `LIMIT`.
Date filters are sargable ([filterDateRange](html/app/Models/ReportStatsModel.php#L106-L125)
uses bare `` `date` ``, which is correct), but an `ip` or `search_query` report
([:470-495](html/app/Models/ReportStatsModel.php#L470-L495)) over a wide range on
a large advertiser produces a very large on-disk temp table, made worse by
`GROUP_CONCAT(id)` at [:471](html/app/Models/ReportStatsModel.php#L471).

Lower priority than the above: this is a deliberately heavy report, not an
incidental UI click, and it runs through
[ReportFileGenerator](html/app/Services/Reports/ReportFileGenerator.php).

### Fix

- Cap the date span for high-cardinality report dimensions (`ip`, `search_query`,
  `keyword`) — e.g. reject ranges over 92 days with a clear message.
- Add `MAX_EXECUTION_TIME` / a statement timeout to the report connection group so
  a runaway report kills itself rather than the server.
- Verify `group_concat_max_len` and `tmp_table_size` / `max_heap_table_size` are
  sized deliberately, and that `tmpdir` has headroom for the largest report.

---

## P2 — Operational gaps

1. **Slow log is unreadable to the app team.** `slow_query_log` is ON writing to
   `/var/lib/mysql/mysql-slow.log.000453`, `long_query_time = 10`, and
   `Slow_queries` has reached 956,164 — but the file is root-only. Get read access
   (or ship it to a log collector / enable `log_output=TABLE`) so the fixes above
   can be prioritised by real frequency instead of by code reading.
2. **Consider lowering `long_query_time`.** At 10s, everything in this document
   except the history query is invisible.
3. **HeatWave RAPID is not loaded.** All 138 `adv_clicks_*_new` tables report
   `AVAIL_RPDGSTABSTATE` — available but not loaded into the accelerator. Queries
   are running on InnoDB. Loading them is likely a large win independent of every
   code change here; worth confirming with whoever owns the HeatWave cluster
   whether this is intentional (cost) or drift.
4. **No statement timeout anywhere.** Add `MAX_EXECUTION_TIME` hints (or
   `max_execution_time` per connection group) to the analytics connections so no
   single web request can pin the server indefinitely.

---

## Suggested sequencing

| Order | Work | Why first |
| --- | --- | --- |
| 1 | P0 index on `adcenter_log` | Single DDL, biggest and most certain win, zero code risk |
| 2 | P0 `LIMIT` + repository extraction | Bounds the 204k-row worst case |
| 3 | P1 `DATE()` removal (6 sites) | Mechanical, high leverage, easy to verify with EXPLAIN |
| 4 | P1 ad-format single-pass | Halves the largest remaining scan |
| 5 | P2 ops: slow-log access, statement timeouts | Gives measurement for everything after |
| 6 | P2 legacy top-N cap + loud fallback | Removes the outage-amplification path |
| 7 | P3 report caps | Bounded blast radius, least frequent |

Items 1–4 are independently shippable and independently revertible.

---

## Verification checklist

- [ ] `EXPLAIN` on all three history endpoints shows `type: ref` on `idx_entity_lookup`
- [ ] Campaign history for the 204k-row campaign returns in < 200 ms and bounded rows
- [ ] `EXPLAIN` on all six rewritten breakdown queries shows a date range scan
- [ ] Breakdown totals unchanged before/after for a fixed advertiser + date range
      (regression-check landing page, ad format, device, and one high-cardinality dimension)
- [ ] Ad-format breakdown issues one scan, not two (verify via general log or query count)
- [ ] Simulate HeatWave unavailability and confirm the legacy path stays capped
- [ ] `Slow_queries` delta over a fixed window drops measurably post-deploy

---

## Open questions

1. **Prod schema parity.** All index observations are from dev. Confirm
   `Shorty.adcenter_log` on prod also lacks an index on `entity_id` before
   scheduling the DDL window — and confirm the prod row count, which drives the
   `ALTER` duration estimate.
2. **History cap value.** Is 500 rows acceptable to the product, or does the
   History modal need real pagination? This decides whether P0 step 2 is a
   one-line change or a UI change.
3. **Is the legacy breakdown path still expected to serve traffic?** 223
   advertisers have legacy shards but only 138 have HeatWave tables. If the other
   85 are dormant, P2 is purely about the fallback path; if they're live, the
   legacy top-N cap moves up in priority.
4. **HeatWave RAPID load** — intentional or drift? Changes the cost/benefit of
   every query-shape fix above.
5. **Write rate on `adcenter_log`** during the ALTER window — INPLACE DDL buffers
   concurrent DML in the online log; a very high write rate needs
   `innodb_online_alter_log_max_size` raised or a quieter window.