# Performance Audit — vehicle-catalog (product-db)

Date: 2026-08-11. Environment: WSL2, repo on CIFS mount (`/mnt/devel`), PHP 8.5 (herd-lite),
Laravel 13 + Inertia v3, MySQL 8 on Windows host `192.168.200.4:3307` (product_db, ~134k rows:
entity_features 73,370 · entity_data_values 19,950 · body_type_section_elements 23,345 ·
variants 1,080). Measured with `php -S` (server.php) + curl, in-process HTTP kernel, tinker with
`DB::enableQueryLog`, `EXPLAIN`, and `information_schema` index dumps.

## 1. Measured latencies per route (warm `php -S` + curl, back-to-back, median of 3)

| Route | Median | Best | Notes |
|---|---|---|---|
| `GET /` (dashboard) | **0.14 s** | 0.13 s | 12.2–13.3 s before the cache (see §5.1) |
| `GET /models` | 0.15 s | 0.11 s | |
| `GET /models?q=500` | 0.16 s | 0.10 s | search OK at 75 rows |
| `GET /models/1` | 0.16 s | 0.10 s | feature preview deferred |
| `GET /models/1/body-types/3` | 0.17 s | 0.14 s | sections deferred |
| `GET /models/1/body-types/3/engines/8` | 0.20 s | 0.15 s | features/data deferred |
| `GET /models/1/body-types/3/trims/8` | 0.17 s | 0.13 s | features/data deferred |
| `GET /variants` | **3.9–6.3 s (php -S)** / **0.31 s (in-process)** | — | see §6 anomaly |
| `GET /variants?sort=name` | 3.8 s (php -S) | — | same anomaly, no join involved |
| `GET /structure` | 0.25 s | 0.19 s | |
| `GET /imports` | 0.18 s | 0.12 s | |
| `GET /variants/1` | 0.15 s | 0.12 s | |
| `GET /structure/elements/1` | 0.17 s | 0.11 s | |

Cold boot (fresh PHP process, empty opcache): **4–6 s for every route** — CIFS + opcache
revalidation, environment cost, not application logic (see §7).

## 2. Query counts per page (DB::enableQueryLog, in-process; no N+1 found)

| Page / service | Queries | Total ms |
|---|---|---|
| Models index (`ModelsQuery::paginate`) | 4 | 6.4 |
| Model show hierarchy (eager loads) | 9 | 12.4 |
| Feature preview (deferred) | 5 | 124.7 |
| Body-type sections (deferred) | 3 | 7.3 |
| Body-type feature includes | 1 | 1.4 |
| Engine values (deferred) | 3 | 5.0 |
| Trim values (deferred) | 3 | 4.2 |
| Variant values | 3 | 4.2 |
| Variants index (`VariantsQuery::paginate`) | 9 | 18.2 |
| Structure index (categories) | 4 | 11.1 |
| Structure elements | 2 | 2.8 |
| Structure data units | 1 | 8.4 |
| Structure element detail | 4 | 5.6 |
| Imports index | 2 | 2.8 |
| Dashboard totals | 1 | 17.6 |
| **Dashboard dataQuality** | 6 | **980.8** |
| Status counts | 1 | 2.0 |

No N+1 anywhere; hierarchy pages are fully eager-loaded (existing tests bound queries < 15).
The single expensive query is `CatalogMetrics::dataQuality()`'s correlated `NOT EXISTS` (§3).

## 3. Missing indexes / EXPLAIN findings

`SHOW INDEX` comparison: all tables carry identity UNIQUEs on FK chains
(`model_years(model_id, year_key)`, `body_types(model_year_id, body_key)`,
`engines/trims(body_type_id, key)`, `variants(body_type_id, trim_id, engine_id)`) plus
`idx_models_brand`, `idx_models_market`, `idx_structure_categories_brand`,
`idx_category_items_element`, `idx_entity_features_code`, `idx_entity_data_code`, etc.

| Query | Plan | Verdict |
|---|---|---|
| Models index (brand/status/search) | `idx_models_brand` ref (14 rows), filter, filesort on ≤75 rows | OK — `status` unindexed but 75 rows |
| Variants join + brand sort | `idx_models_brand` → nested index lookups, temp+filesort on 14 rows | OK at 1,080 rows |
| Feature-preview subtree aggregate | range on `uq_entity_features_identity`, materialized subqueries | OK |
| **dataQuality correlated NOT EXISTS** | per-row probe: `s` via `uq_sections_identity` (1 row) then `e` via `idx_section_elements_code` (**est. 11,672 rows/probe**, key_len 1022) | **Hot spot — 69k dependent subqueries** |
| Element detail code counts | `idx_entity_features_code` / `idx_entity_data_code` | OK |

Experimental index test: adding `idx_section_elements_code_section (code, section_id)` made the
dataQuality probe **~50% slower** (1.0 s → 1.5–1.7 s; optimizer plan flip). **Not applied**
(migration deleted, index dropped). Query rewrites (derived-table LEFT JOIN, nested EXISTS,
COUNT-DISTINCT) were all slower (2.1–4.1 s vs ~1.0 s) — no safe rewrite beats the original.

## 4. Heavy payloads (curl size_download, Inertia JSON)

| Payload | Size | Deferred? | Notes |
|---|---|---|---|
| `/variants` (20 rows + full option lists) | **119,838 B** | no | 184 body types + 479 engines + 597 trims shipped on every load |
| `/structure` (10 cats + items + 10 elements + 27 units) | **120,399 B** | no | items eager-loaded per category (avg 30, max 196) |
| `/models/1` feature preview (partial) | 1,193 B | yes (deferred, 1.29 s cold) | OK |
| `/models/1/body-types/3` sections (partial) | 20,762 B | yes (deferred) | 14 sections, ≤89 elements each |
| engine features+data (partial) | 5,980 B | yes (deferred) | max ~310 feature rows per trim — bounded, no pagination needed |
| trim features+data (partial) | 7,395 B | yes (deferred) | same |
| `/` dashboard (cached) | 4,788 B | n/a | |

Entity-detail stores are already deferred and per-entity bounded (max 310 feature rows/trim);
the "69k rows for trims" is spread across 597 trims, so per-page it is small. Grids are
**server-paginated** (variants 20, models 20, structure categories/elements 10/page); no
client-side pagination issues.

## 5. Applied optimizations (safe, measured)

### 5.1 Dashboard metrics cache — `DashboardController` (before → after)
Cached the full M1–M9 metrics payload (`Cache::remember`, database store, TTL 300 s) keyed by
`PcmImport::max('id')` — a new import automatically produces a fresh key (no invalidation hook
needed; the import command is untouched). Tests are unaffected (phpunit uses sqlite :memory: +
array cache).

- Before: ~1.3 s clean (tinker), **12.2–13.3 s over HTTP during import activity** (dataQuality
  amplifies to 11.8 s under MySQL write contention on the Windows host).
- After: **0.14 s** (median), 0.13–0.14 s stable; cache key verified present.
- Rationale: catalog tables only change via `product-db:migrate`; the payload is read-only.

### 5.2 Variants option-list column constraints — `VariantsController` (before → after)
The cascading-filter option lists loaded full rows (incl. TEXT `description`, LONGTEXT
`salesforce_id_json`/`range_json`, timestamps) and shipped the mapped arrays on every `/variants`
load. Constrained to the exact columns consumed:

- `bodyTypeOptions`: `get(['id','model_year_id','body_key','name_de','name_fr','name_it','description'])`
  + `modelYear` eager `select('id','model_id')`
- `engineOptions`: `get(['id','body_type_id','engine_key','name_de','name_fr','name_it','engine_name'])`
- `trimOptions`: `get(['id','body_type_id','trim_key','trim_name'])`

- Before: body_types scan 700–900 ms / engines 667–708 ms / trims 1,456–2,531 ms (php -S).
- After: body_types scan **3.4 ms**; engines/trims unchanged in this environment (see §6 anomaly),
  but MySQL transfer per row cut ~5–10× (row bytes: engines ~2 KB → ~0.4 KB, trims ~1.5 KB → ~0.2 KB).
- Response bytes unchanged (119,838) — the mapped arrays were already lean; all 155 tests pass.

## 6. `/variants` HTTP anomaly (measured, NOT an app defect — document for production)

`/variants` is 3.9–6.3 s over `php -S` but **0.31 s through the in-process HTTP kernel** (same
controller, same serialization, same 119 KB response). DB::listen in the HTTP path showed the
full-table option scans as the cost: `select * from engines` 0.7 s, `select * from trims`
1.5–2.5 s — while identical scans run in 6 ms in tinker and via the Boost MCP client at the same
moment. Point queries (count, eager `IN`) stay 2–13 ms. Conclusion: this is a
php -S / WSL2 / remote-MySQL transfer-path quirk (large result sets, ~21 KB/s effective), not
application logic. **Verify `/variants` on the production stack (PHP-FPM/nginx)**; the page's
true cost is ~0.3 s. The remaining exposure is the 119 KB payload (§4, §8 rec. 1).

## 7. Environment findings (not code — worth knowing)

- **CIFS + opcache (`validate_timestamps=On`, `revalidate_freq=2`)**: every request landing >2 s
  after the previous one pays a 1–2 s revalidation spike; cold boots cost 4–6 s/request. Fix on
  a real server: local disk, `opcache.validate_timestamps=0` + deploy-step cache clear.
- **`php -S` served stale opcache bytecode after source edits** (CIFS mtime quirk) — restart the
  server after changes; irrelevant in production.
- **The user's `DebugOrderTest` truncates + re-imports product_db repeatedly** (every ~45–90 s
  during a pest run), wiping tables mid-request. It invalidated several measurement windows and
  intermittently fails `CatalogRealDataTest` when run concurrently (all values re-verified when
  quiet — DB full: 75 models / 73,370 features).
- SQLite-on-CIFS is unusable here (per AGENTS.md) — everything above ran on MySQL; no sqlite
  locking issue was encountered.

## 8. Recommendations (ordered by impact)

1. **Defer/split the `/variants` option lists** (highest remaining win). 119 KB of
   184+479+597 option rows is shipped on every load regardless of filters. Move to deferred
   props or fetch per cascade level (brand → models → body types → engines/trims) when the user
   expands a select. Frontend work — kept for parity with product-db-react; not applied here.
2. **Same treatment for `/structure`** (120 KB): defer the categories/items and elements tabs
   (IA §4.7 already defers the heavy sections tab on body-type detail; extend the pattern).
3. **Search `LIKE '%q%'`**: fine now (75 models, 5,156 elements), but it is a full scan of the
   name columns; if the catalog grows past ~10³–10⁴ rows, add FULLTEXT indexes on the localized
   name columns (models, structure_elements) rather than more LIKE columns.
4. **Dashboard cache is enough** — do not cache brands/status counts individually; the
   import-keyed dashboard cache already covers them.
5. **Production deployment checks**: measure `/variants` under FPM (expect ~0.3 s), set opcache
   to not revalidate on CIFS-style mounts, and use a real web server (php -S is single-threaded).
6. **`models.status` / `variants.status` indexes**: not needed at 75/1,080 rows; revisit if the
   catalog grows 10×.

## 9. Gates

```
php artisan test --compact      → passed: 155 tests, 155 passed, 2226 assertions, ~60 s
PHPSTAN_TURBO=0 phpstan analyse --memory-limit=1G → passed, 0 errors
vendor/bin/pint (--dirty unavailable: no git; ran on changed files) → passed
```

Note: an earlier full-suite run failed 6+5 tests in `CatalogRealDataTest` and one in
`ImportsIndexTest` — reproduced as **concurrent product_db truncation by the user's
DebugOrderTest** (imports logged in laravel.log during the runs); the clean rerun passed 155/155.

## 10. Changed files

- `app/Http/Controllers/Catalog/DashboardController.php` — metrics cache (§5.1)
- `app/Http/Controllers/Catalog/VariantsController.php` — option-list column constraints (§5.2)