MySQL Query Optimization: 10 Practical Tips for Laravel Developers

// BAD: fires 1 + N queries
$orders = Order::all(); // 1 query
foreach ($orders as $order) {
    echo $order->user->name; // 1 query per order
}

// GOOD: fires exactly 2 queries
$orders = Order::with('user')->get();

// GOOD: nested eager loading
$orders = Order::with(['user', 'items.product'])->get();

// Use Laravel Debugbar or Telescope to detect N+1 in development:
composer require barryvdh/laravel-debugbar --dev

// In your migration — add indexes on columns you filter/sort by
Schema::table('orders', function (Blueprint $table) {
    $table->index('user_id');           // foreign key — always index
    $table->index('status');            // frequently filtered
    $table->index('created_at');        // frequently sorted
    $table->index(['tenant_id', 'status']); // composite: multi-tenant + filter
});

// Use EXPLAIN to verify index usage:
DB::statement('EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = "pending"');

// BAD: SELECT * transfers all columns, even ones you never use
$users = User::all();

// GOOD: only fetch what you actually render
$users = User::select('id', 'name', 'email')->get();

// For API responses, use when + select pattern:
$orders = Order::select('id', 'total', 'status', 'created_at')
    ->with(['user:id,name,email'])
    ->get();

// BAD: loads entire table into memory
$orders = Order::where('status', 'pending')->get();
foreach ($orders as $order) { /* process */ }

// GOOD: processes in batches of 500
Order::where('status', 'pending')->chunk(500, function ($orders) {
    foreach ($orders as $order) {
        // process each order
    }
});

// Even better for heavy jobs: chunkById() avoids offset performance issues
Order::where('status', 'pending')->chunkById(500, function ($orders) {
    foreach ($orders as $order) { /* process */ }
});

// Cache a query result for 10 minutes
$topProducts = Cache::remember('top_products', 600, function () {
    return Product::withCount('orders')
        ->orderByDesc('orders_count')
        ->limit(10)
        ->get();
});

// Cache with tags (requires Redis or Memcached)
$stats = Cache::tags(['stats', 'orders'])->remember('monthly_revenue', 3600, function () {
    return Order::whereMonth('created_at', now()->month)->sum('total');
});

// Flush specific tags when data changes
Cache::tags(['orders'])->flush();

// BAD: N queries
$userIds = [1, 2, 3, 4, 5];
$users = [];
foreach ($userIds as $id) {
    $users[] = User::find($id); // fires a query per ID
}

// GOOD: 1 query with WHERE id IN (1,2,3,4,5)
$users = User::whereIn('id', $userIds)->get();

// Eloquent collections are lazy by default — findMany is even cleaner:
$users = User::findMany($userIds);

// BAD: returns all rows
$orders = Order::all();

// GOOD: paginate with cursor pagination for large tables
// Standard offset pagination (gets slower as page number grows)
$orders = Order::paginate(20);

// Cursor pagination: O(1) regardless of page number — best for infinite scroll
$orders = Order::orderBy('id')->cursorPaginate(20);

// API response automatically includes pagination meta
return OrderResource::collection($orders);

// BAD: partial failure leaves data inconsistent
$order = Order::create($orderData);
$payment = Payment::create(['order_id' => $order->id, ...]);
// If Payment::create() throws, Order exists but has no payment

// GOOD: atomic — either all succeed or all roll back
DB::transaction(function () use ($orderData, $paymentData) {
    $order   = Order::create($orderData);
    $payment = Payment::create(['order_id' => $order->id, ...$paymentData]);
    $order->update(['payment_id' => $payment->id]);
});

// With retry for deadlocks (common in high-concurrency writes)
DB::transaction(function () use ($data) {
    // ...
}, attempts: 3);

// BAD: query fires inside the view on every render
// In Blade: {{ App\Models\Setting::where('key', 'site_name')->value('value') }}

// GOOD: resolve data in controller, pass to view
public function index()
{
    return view('dashboard', [
        'siteName' => Setting::where('key', 'site_name')->value('value'),
        'orders'   => Order::with('user')->latest()->paginate(20),
    ]);
}

// Log all queries slower than 100ms in AppServiceProvider
DB::listen(function ($query) {
    if ($query->time > 100) {
        Log::warning('Slow query', [
            'sql'      => $query->sql,
            'bindings' => $query->bindings,
            'time_ms'  => $query->time,
        ]);
    }
});

// Run EXPLAIN on the raw SQL to see what MySQL is doing
// In MySQL CLI:
// EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';
// Look for: type = 'ALL' means full table scan → you need an index