Refactoring a Slow Laravel Query Without a Big Bang

Fixing an N+1 order query.

The Problem

An order index endpoint returned 200 rows with nested line items, shipments, and integration metadata. Response times climbed as tenants added catalog depth.

Diagnosis

• Classic N+1 on line items and shipment labels
• SELECT * on wide tables when the UI needed six columns
• Missing composite index on (tenant_id, status, created_at)

Changes (incremental)

Order::query()
    ->where('tenant_id', $tenantId)
    ->with([
        'lineItems:id,order_id,sku,quantity',
        'shipments:id,order_id,carrier,tracking_number',
    ])
    ->select(['id', 'tenant_id', 'status', 'total', 'created_at'])
    ->latest()
    ->paginate(50);

Added index migration in the same PR; ran EXPLAIN before and after in staging.

Results (seeded)

• Staging P95 improved enough to unblock the next feature sprint
• No API contract changes — frontend diff was zero

Lesson

Measure on representative tenant data. Local seeds with 50 rows lie.