C6 · Composite indexes + pivot-first rewrite (PR6)

C6/PR6 built + verified in the isolated worktree feature/authors-optimization (clone DB lounge_feature_authors_optimization). Built exactly to doc 10 §2.2–2.4 on top of C5 (#251). MAIN and its DB untouched. **Commits (both carry `Brief: #252`, no Co-Authored-By):** - `dcb13982` — 3 composite index migrations - `40b2e3ad` — pivot-first query rewrite + tests **§2.2 — indexes** (one migration each, sequential, online INPLACE/LOCK=NONE, idempotent, non-mysql Schema fallback; mirror of the existing add_location_top_score_index house style): - `2026_07_05_000100_add_lru_loc_vis_karma_index_to_location_reddit_user.php` → `lru_loc_vis_karma (location_id, is_visible, total_karma, reddit_user_id)` - `2026_07_05_000200_..._seen_...` → `lru_loc_vis_seen (location_id, is_visible, last_seen_at, reddit_user_id)` - `2026_07_05_000300_..._joined_...` → `lru_loc_vis_joined (location_id, is_visible, registered_at, reddit_user_id)` **§2.3–2.4 — pivot-first rewrite** in `app/Livewire/RedditUserIndex.php`: - `render()` resolves location + sort column up front, computes `$use_pivot_first` (location set ∧ ¬search ∧ only_indexed ∧ no admin show_* toggle), branches. Legacy reddit_users-driven path kept byte-identical for every other case (search / only_indexed off / admin toggles). §5 whereExists simplification deliberately left out — out of §2.2–2.4 scope. - New `paginateAuthorsByLocationPivot()`: cursor-paginates `LocationRedditUser` over (location_id, is_visible)+sortcol (covering select `[reddit_user_id, sortcol]`), same-direction tiebreaker, hydrates the page's RedditUser models (same withCount med/posts + theyAre/locations eager loads) preserving index order, seeds `reddit_user_id` + sortcol onto each model so the keyset cursor is computed from the pivot's ordering values. only_verified / recently_active_days / nullable-sort guard moved onto the pivot's denorm columns. **Perf validation on the clone** (EXPLAIN ANALYZE, densest local location = loc 789 `florida`, 8092 pivot rows / 2674 visible; karma desc): - BEFORE (reddit_users-driven whereHas): `Sort: total_karma DESC` filesort over a nested-loop inner join (8092 covering PK lookups) → **21.4 ms** actual. - AFTER page 1: `Covering index range scan using lru_loc_vis_karma … (reverse)`, no filesort, no join → **0.05 ms**. - AFTER page 2 (keyset cursor): still a covering range scan on lru_loc_vis_karma → **~0.065 ms** — §1.2's per-page recurrence eliminated. - active→lru_loc_vis_seen, joined→lru_loc_vis_joined both confirmed "Using index" backward scans, no filesort. - Local dataset is ~198K pivot rows total (vs prod 7.9M) so absolute ms are smaller, but the plan-shape flip is identical to the one that turns prod's 338.7 ms dense case into sub-5 ms. Migration down() reversibility verified (rollback dropped all 3, re-apply recreated). **Tests / lint:** `tests/Feature/Livewire/RedditUserIndexTest.php` +3 (pivot-first karma ordering + 2-page keyset cursor; only_verified on the pivot path; only_indexed-off fallback to legacy incl. non-indexed author) → 4 passed / 22 assertions. C5 `LocationRedditUserDenormSyncTest` still green (5 passed). `pint --test` passed on all changed files. PHPStan clean on the component (via a scratchpad config — NOTE: `phpstan-baseline.neon:625` references the already-deleted `ProcessLegitEmbeddingResults.php`, which breaks PHPStan loading on this branch independent of C6; pre-existing, left untouched).

Child of /authors optimization epic (#239). **Depends on C5.** Build in `feature/authors-optimization` worktree. Implement **PR6** (doc 10 §2.2–2.4): add 3 `lru_loc_vis_*` composite indexes (`_karma` / `_seen` / `_joined`; one migration each, sequential, off-peak, after C5's backfill so trees build once); flip the `/authors` location branch **pivot-first** → read ordered ids straight off a covering index → sub-5ms dense single-location. The headline perf win. Validation: `EXPLAIN ANALYZE` the Dallas pivot-first page query → "Index range scan … using lru_loc_vis_karma … backward, covering", no filesort, no join; <5ms vs 338.7ms; verify every cursor page (not just page 1).