Summary
MoonController::index() fetches every available = 1 moon with seven eager-loaded relations and renders all of them into one HTML table. Filtering and sorting then happen client-side in tablesorter, against the rendered DOM.
That holds up at a few hundred moons. For alliances with large sovereignty holdings the moon count reaches tens of thousands, and the page stops being viable.
Current logic
app/Http/Controllers/MoonController.php
$moons = Moon::with([
'region', 'system', 'renter',
'mineral_1', 'mineral_2', 'mineral_3', 'mineral_4',
])
->where('available', 1)
->orderBy('region_id')->orderBy('solar_system_id')
->orderBy('planet')->orderBy('moon')
->get();No LIMIT and no pagination. resources/views/moons/public.blade.php renders every row returned, and the tablesorter filter widget works over the resulting DOM.
Impact
Measured locally against a seeded fixture of 120 moons:
| Metric |
Measured |
At ~100k moons |
| HTML payload |
154 KB / 120 rows (~1.28 KB per row) |
~128 MB |
| Models hydrated |
120 plus relations |
100k plus relations |
| Queries |
8 (1 + 7 eager loads) |
8, unchanged |
The 128 MB figure is a linear extrapolation from the per-row payload, not a measurement. In practice PHP's memory limit would be hit while hydrating models before a response of that size was ever produced.
Eager loading is already correct, and a scan of 100k rows costs MySQL tens of milliseconds. The constraint here is response size, PHP memory and client-side rendering, not query time.
Proposed change
Move filtering, sorting and pagination to the server, driven by query parameters.
- Paginate, reusing what the codebase already does.
MinerController::showMiners() uses ->paginate(250), and AppServiceProvider already registers the custom paginator view at resources/views/common/paginator.blade.php. No new plumbing required.
- Change the default view to moons that are actually rentable:
status_flag = STATUS_AVAILABLE with no currently active renter. This is what the nav link ("Moons available to rent") already implies. ?status=all would still return the full inventory.
- Accept query parameters for region, system, mineral and status, replacing the client-side filter form with a plain
GET form that repopulates from the request.
- Sort via links on the column headers, so sorting covers the whole result set. Keeping tablesorter alongside pagination would sort only the 250 visible rows while looking like it sorted everything.
- Build the region dropdown from a grouped query over
moons rather than from the result set, so it stays stable as filters change. Per-region counts come free with the aggregate.
Explicitly not proposed:
No schema changes. The one place an index looked unavoidable was testing whether a moon has an active renter. Two flat queries handle that instead of a correlated NOT EXISTS, so no index on renters.moon_id is needed. Indexes would be a reasonable follow-up if profiling at production scale justifies them, but nothing here depends on them.
No caching. The application performs no caching today and this change does not need to introduce any.
No admin-side changes, to keep the diff reviewable.
Related
Same pattern, will hit the same ceiling. Happy to file these separately:
MoonAdminController::index(), the identical unbounded query behind /moon-admin/listMoonAdminController::export(), which loads all moons with relations into memory to build a CSVMoonAdminController::calculate(), which loads all available moons and loops
Summary
MoonController::index()fetches everyavailable = 1moon with seven eager-loaded relations and renders all of them into one HTML table. Filtering and sorting then happen client-side in tablesorter, against the rendered DOM.That holds up at a few hundred moons. For alliances with large sovereignty holdings the moon count reaches tens of thousands, and the page stops being viable.
Current logic
app/Http/Controllers/MoonController.phpNo
LIMITand no pagination.resources/views/moons/public.blade.phprenders every row returned, and the tablesorter filter widget works over the resulting DOM.Impact
Measured locally against a seeded fixture of 120 moons:
The 128 MB figure is a linear extrapolation from the per-row payload, not a measurement. In practice PHP's memory limit would be hit while hydrating models before a response of that size was ever produced.
Eager loading is already correct, and a scan of 100k rows costs MySQL tens of milliseconds. The constraint here is response size, PHP memory and client-side rendering, not query time.
Proposed change
Move filtering, sorting and pagination to the server, driven by query parameters.
MinerController::showMiners()uses->paginate(250), andAppServiceProvideralready registers the custom paginator view atresources/views/common/paginator.blade.php. No new plumbing required.status_flag = STATUS_AVAILABLEwith no currently active renter. This is what the nav link ("Moons available to rent") already implies.?status=allwould still return the full inventory.GETform that repopulates from the request.moonsrather than from the result set, so it stays stable as filters change. Per-region counts come free with the aggregate.Explicitly not proposed:
No schema changes. The one place an index looked unavoidable was testing whether a moon has an active renter. Two flat queries handle that instead of a correlated
NOT EXISTS, so no index onrenters.moon_idis needed. Indexes would be a reasonable follow-up if profiling at production scale justifies them, but nothing here depends on them.No caching. The application performs no caching today and this change does not need to introduce any.
No admin-side changes, to keep the diff reviewable.
Related
Same pattern, will hit the same ceiling. Happy to file these separately:
MoonAdminController::index(), the identical unbounded query behind/moon-admin/listMoonAdminController::export(), which loads all moons with relations into memory to build a CSVMoonAdminController::calculate(), which loads all available moons and loops