Part of #63.
Problem
Scan::checkAndMarkRegionBasedScanComplete() runs a DISTINCT (region, service) count over all of a scan's detail rows, once per region job (~153 times per scan). scan_details has indexes on scan_id, service and api_method, but none covering region (app/database/migrations/2026_01_11_041536_create_scan_details_table.php), so this filesorts.
Change
Add a (scan_id, service, region) index to scan_details.
Dependencies
Still wanted after #65. Q1 resolved to Bus::batch(), which does remove the DISTINCT count — but hasAlreadyCollected() (app/app/Jobs/ProcessRegionScanJob.php:168) queries exactly (scan_id, service, region) on every region job, so the index earns its place regardless. Re-check against #73, which may replace that query entirely.
Priority: P1
Part of #63.
Problem
Scan::checkAndMarkRegionBasedScanComplete()runs aDISTINCT (region, service)count over all of a scan's detail rows, once per region job (~153 times per scan).scan_detailshas indexes onscan_id,serviceandapi_method, but none coveringregion(app/database/migrations/2026_01_11_041536_create_scan_details_table.php), so this filesorts.Change
Add a
(scan_id, service, region)index toscan_details.Dependencies
Still wanted after #65. Q1 resolved to
Bus::batch(), which does remove theDISTINCTcount — buthasAlreadyCollected()(app/app/Jobs/ProcessRegionScanJob.php:168) queries exactly(scan_id, service, region)on every region job, so the index earns its place regardless. Re-check against #73, which may replace that query entirely.Priority: P1