# Performance Benchmark Results

This file is updated after each reviewed optimization batch. Results are local
development measurements and do not represent staging or production.

## Batch 1A — Dashboard local analytics

Date: 2026-10-14
Environment: PHP 8.3.1, MySQL 5.7.24, Laravel file cache
Dataset: see `baseline.md`

Change:

- Replaced Dashboard's request-time `SearchConsoleService` dependency with
  `ClusterAnalyticsService`.
- Replaced per-cluster analytics calls with one local batch aggregation.
- Included the requested site in the analytics filter scope.
- Changed the Dashboard cache namespace to avoid reusing responses generated by
  the previous data source.

### Before and after

| Scenario | Before | After first request | After second request | Status |
|---|---:|---:|---:|---|
| Dashboard, 28d, no comparison | 2,650.32 ms, HTTP 500 | 1,060.17 ms, HTTP 200 | 2.61 ms, HTTP 200 | Correctness restored; target met in this sample |
| Dashboard, 180d, previous period | not successfully available from remote-backed local request | 16,895.80 ms, HTTP 200 | 7.98 ms, HTTP 200 | Functional, but cold target failed |

The 28-day first request executed seven SQL queries; 978.41 ms was spent in the
single batched GSC aggregation. The 180-day first request spent 16,791.07 ms in
the same aggregation. The second requests were Dashboard cache hits and made no
database queries.

The 180-day result proves that replacing the remote dependency fixes the 500 but
does not by itself satisfy the cold-cache target. The query is shared with the
Cluster listing and is the next SQL optimization batch.

### Verification

```text
php -d extension=pdo_sqlite vendor/bin/phpunit --configuration phpunit.xml.dist
OK (41 tests, 179 assertions)
```

`DashboardLocalAnalyticsTest` verifies:

- authenticated Dashboard response succeeds without BigQuery;
- clicks and impressions come from local GSC rows;
- CTR and weighted average position retain their expected values;
- comparison-disabled response shape is preserved;
- all Dashboard clusters are resolved with one GSC query.

## Batch 1B — Content SEO local analytics

Date: 2026-10-14
Environment: PHP 8.3.1, MySQL 5.7.24, Laravel file cache
Dataset: see `baseline.md`

Change:

- Replaced Content SEO's request-time `SearchConsoleService` dependency with
  `ClusterAnalyticsService`.
- Added the requested site to the local analytics filter scope.
- Added a hash-indexed batch query that resolves current and comparison keyword
  metrics in one database request.
- Preserved the existing position buckets, movement rules, opportunity score,
  weighted position, target gap, and JSON response fields.

### Before and after

| Scenario | Before | Initial local query | Optimized local query | Warm | Status |
|---|---:|---:|---:|---:|---|
| Content SEO, 28d, no comparison | 2,235.40 ms, HTTP 500 | 10,656.46 ms, HTTP 200 | 907.63 ms, HTTP 200 | 27.69 ms | Functional; cold target met in this sample |
| Content SEO, 180d, previous period | not successfully available from remote-backed local request | not measured | 14,020.83 ms, HTTP 200 | 30.45 ms | Functional, but cold target failed |

The 28-day optimized request executed four SQL queries. Its single local GSC
query took 752.40 ms, down from 10,466.53 ms for the raw query-text grouping.
The 180-day request also used one local GSC query, but that query took
13,865.33 ms while crossing the historical/current boundary. This remains part
of the shared derived-union optimization in Batch 2.

### Verification

```text
php -d extension=pdo_sqlite vendor/bin/phpunit --configuration phpunit.xml.dist
OK (42 tests, 192 assertions)
```

`ContentSeoLocalAnalyticsTest` verifies:

- the endpoint succeeds without BigQuery;
- current and comparison clicks, impressions, CTR, and weighted positions come
  from fixed local rows;
- ranking movement, target gap, position bucket, and opportunity selection are
  preserved;
- current and comparison periods are resolved with one local GSC query.

## Batch 1C — Content Planning local analytics

Date: 2026-10-15
Environment: PHP 8.3.1, MySQL 5.7.24, Laravel file cache
Dataset: see `baseline.md`

Change:

- Replaced Content Planning's request-time `SearchConsoleService` dependency
  with `ClusterAnalyticsService`.
- Applied the selected site to the local analytics query.
- Reused the hash-indexed current/comparison batch query introduced for Content
  SEO, so every effective plan keyword is resolved in one database request.
- Preserved task/date/access filtering, summaries, SEO fields, ranking movement,
  and target-gap behavior.

### Before and after

| Scenario | Before | After first request | After second request | Status |
|---|---:|---:|---:|---|
| Content Planning, 28d, no comparison | 2,229.02 ms, HTTP 500 | 153.20 ms, HTTP 200 | 22.76 ms, HTTP 200 | Functional; cold target met in this sample |
| Content Planning, 180d, previous period | not successfully available from remote-backed local request | 8,218.21 ms, HTTP 200 | 24.23 ms, HTTP 200 | Functional, but cold target failed |

The 28-day first request executed seven SQL queries. The one local GSC query
took 10.31 ms. The 180-day request also used one local GSC query, which took
8,070.30 ms across the historical/current boundary. That remaining cost is part
of the shared derived-union work in Batch 2.

### Verification

```text
php -d extension=pdo_sqlite vendor/bin/phpunit --configuration phpunit.xml.dist
OK (43 tests, 206 assertions)
```

`ContentPlanningLocalAnalyticsTest` verifies:

- the endpoint succeeds without BigQuery;
- task/date filtering and summary counts remain intact;
- current and comparison clicks, impressions, CTR, weighted positions,
  movement, and target gap come from fixed local rows;
- current and comparison periods are resolved with one local GSC query.

## Batch 1D — Traffic Report local analytics

Date: 2026-10-15
Environment: PHP 8.3.1, MySQL 5.7.24, Laravel file cache
Dataset: see `baseline.md`

Change:

- Replaced Traffic Report's request-time `SearchConsoleService` dependency
  with `ClusterAnalyticsService`.
- Applied the selected site to the local analytics scope.
- Preserved query/page summaries, comparisons, daily trend, dimension rows,
  and response fields.

### Before and after

| Scenario | Before | Initial local path | Daily aggregates | Final cold | Final warm | Status |
|---|---:|---:|---:|---:|---:|---|
| Traffic Report, 28d, no comparison | 2,190.48 ms, HTTP 500 | 76,859.53 ms, HTTP 200 | 2,746.51 ms, HTTP 200 | 2,751.75 ms, HTTP 200 | 18.39 ms, HTTP 200 | Functional; cold target met in this sample |
| Traffic Report, 180d, previous period | not successfully available from remote-backed local request | exceeded 150 seconds; benchmark stopped | 59,053.40 ms, HTTP 200 | 12,618.77 ms, HTTP 200 | 20.75 ms, HTTP 200 | Functional and reliable; cold target still failed |

Post-backfill rollout validation on 2026-10-16 returned HTTP 200 for both
scenarios. With Laravel's application cache cleared before each scenario, the
28-day request measured 1,026.98 ms cold and 24.04 ms warm. The 180-day request
measured 3,326.40 ms cold and 8.09 ms warm. The database buffer pool had been
warmed by the rollup build, so these measurements supplement rather than
replace the colder samples above.

The optimized endpoint performs one query-dimension scan, one page-dimension
scan, and one combined-period daily scan. Query and page summaries are derived
from the daily result instead of rescanning the same source. The daily aggregate
command now covers both historical API rows and current exports. A second
rollup keeps exact daily totals and monthly query/page dimensions. Long ranges
use monthly rows for complete calendar months and daily rows for their boundary
fragments. Both aggregate layers refresh their latest ten days on the scheduler.

For 28 days, the three analytics queries consumed 2,647.57 ms in total. The
monthly rollup reduced the 180-day cold request from 59.05 seconds to 12.62
seconds. Its daily trend query fell to 12.15 ms; query and page dimensions took
10.85 seconds and 1.62 seconds respectively. A measured
`site, dimension_hash, data_date` index experiment made the 28-day path slower
and was rolled back. The one-time full-history rollup backfill took about 11
minutes; routine refreshes are bounded to the latest affected month(s).

### Verification

```text
php -d extension=pdo_sqlite vendor/bin/phpunit --configuration phpunit.xml.dist
OK (45 tests, 230 assertions)
```

`TrafficReportLocalAnalyticsTest` verifies:

- the endpoint succeeds without BigQuery;
- query and page metrics, comparison values, and weighted positions come from
  fixed local rows;
- daily trend and dimension response shapes remain intact;
- long ranges combine monthly interior rows with exact daily boundaries;
- the request remains scoped to local synchronized analytics.