Laravel: Optimasi COUNT dan Filter Admin saat Data Melejit

Saat tabel masih kecil, halaman daftar admin di Laravel biasanya terasa baik-baik saja. Masalah baru terlihat ketika data tumbuh besar: filter makin banyak, sorting makin dinamis, lalu paginate() ikut menjalankan COUNT(*) untuk menghitung total data. Hasilnya, satu request listing bisa memicu query utama yang berat dan query count yang tidak kalah mahal.

Jika gejalanya adalah halaman daftar melambat, CPU database naik, atau respons memburuk hanya ketika filter tertentu aktif, akar masalahnya sering bukan Laravel itu sendiri, melainkan pola query dan index yang tidak cocok dengan kombinasi WHERE dan ORDER BY. Fokus optimasi yang benar adalah: temukan query yang lambat, pahami execution plan-nya, lalu sesuaikan index dan strategi pagination.

Mengapa admin listing melambat saat data membesar

Kasus yang umum adalah daftar order, user, invoice, atau log aktivitas dengan filter seperti:

status
rentang tanggal
keyword pencarian
tenant atau organisasi
sorting berdasarkan tanggal terbaru

Di Laravel, implementasi dasarnya sering terlihat sederhana:

use App\Models\Order;
use Illuminate\Http\Request;

public function index(Request $request)
{
    $query = Order::query()
        ->when($request->status, fn ($q, $status) => $q->where('status', $status))
        ->when($request->tenant_id, fn ($q, $tenantId) => $q->where('tenant_id', $tenantId))
        ->when($request->date_from, fn ($q, $dateFrom) => $q->whereDate('created_at', '>=', $dateFrom))
        ->when($request->date_to, fn ($q, $dateTo) => $q->whereDate('created_at', '<=', $dateTo))
        ->orderByDesc('created_at');

    $orders = $query->paginate(50);

    return view('orders.index', compact('orders'));
}

Di permukaan, ini normal. Namun ada beberapa sumber bottleneck:

Query listing harus memfilter dan mengurutkan data dalam jumlah besar.
Query COUNT(*) untuk paginate() bisa memindai banyak baris yang sama beratnya dengan query utama.
Kolom filter tidak terindeks, atau index ada tetapi urutannya tidak cocok dengan pola query.
Fungsi pada kolom seperti whereDate(created_at) dapat mengurangi efektivitas index pada beberapa kasus.
Pencarian dengan wildcard seperti %kata% sering tidak memanfaatkan index B-Tree biasa.

Intinya: ketika data membesar, biaya paling mahal sering bukan mengambil 50 baris untuk halaman sekarang, melainkan menemukan baris mana yang cocok dan menghitung total seluruh hasil.

Cara menemukan bottleneck query di Laravel

1. Mulai dari gejala nyata

Jangan menebak. Catat dulu perilakunya:

Halaman lambat hanya saat filter tertentu dipakai.
Halaman pertama lambat, tetapi halaman berikutnya juga tetap lambat.
Query count lebih lama dari query data.
Sorting berdasarkan kolom tertentu membuat query memburuk.

Pola ini penting untuk membedakan apakah masalah ada pada count, sorting, atau filter tertentu.

2. Lihat query aktual dengan Laravel Debugbar atau Telescope

Untuk lingkungan pengembangan, Laravel Debugbar atau Telescope sangat membantu melihat:

query SQL yang dieksekusi,
jumlah query per request,
durasi tiap query,
binding parameter.

Perhatikan bahwa paginate() biasanya menghasilkan minimal dua query penting:

query data untuk mengambil baris halaman saat ini,
query count untuk total seluruh hasil.

Kalau query count justru paling lama, optimasi harus diarahkan ke sana, bukan hanya ke query list.

3. Aktifkan dan baca slow query log database

Slow query log membantu menangkap query bermasalah di lingkungan yang lebih mirip produksi. Anda tidak perlu menebak dari kode aplikasi saja. Yang dicari adalah query yang konsisten lambat, terutama:

SELECT COUNT(*) ... dengan kombinasi filter yang sama seperti listing,
query dengan ORDER BY created_at DESC atau kolom lain yang tidak terindeks dengan benar,
query yang melakukan scan besar walau hasil halaman hanya sedikit.

Setelah query lambat ditemukan, jalankan EXPLAIN terhadap query tersebut.

4. Gunakan EXPLAIN untuk memahami rencana eksekusi

EXPLAIN menunjukkan bagaimana database memilih index, apakah terjadi scan besar, dan apakah sorting dilakukan secara mahal. Contoh bentuk analisis yang umum:

EXPLAIN SELECT *
FROM orders
WHERE tenant_id = 10
  AND status = 'paid'
  AND created_at >= '2024-01-01 00:00:00'
  AND created_at < '2024-02-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

Beberapa sinyal yang perlu diperhatikan secara umum:

database tidak memakai index yang Anda harapkan,
jumlah baris yang dipindai jauh lebih besar daripada hasil akhir,
sorting tidak bisa dipenuhi dari index sehingga perlu langkah tambahan yang mahal,
query count memakai plan yang buruk karena filter tidak selektif atau index tidak cocok.

Nama kolom output EXPLAIN bisa berbeda detailnya antar mesin database, tetapi prinsipnya sama: cek apakah query benar-benar dibantu index yang sesuai dengan pola akses.

Pola query Laravel yang sering memicu masalah

Hindari fungsi yang merusak pemanfaatan index

Contoh yang sering muncul adalah:

$query->whereDate('created_at', '>=', $request->date_from);

Secara logika benar, tetapi pada beberapa kondisi lebih aman memakai rentang waktu langsung pada kolom aslinya agar database lebih mudah memanfaatkan index:

$query->when($request->date_from, function ($q, $dateFrom) {
    $q->where('created_at', '>=', $dateFrom . ' 00:00:00');
});

$query->when($request->date_to, function ($q, $dateTo) {
    $q->where('created_at', '<', date('Y-m-d H:i:s', strtotime($dateTo . ' +1 day')));
});

Poin utamanya bukan format tanggal tertentu, melainkan gunakan range pada kolom asli, bukan membungkus kolom dengan fungsi jika Anda ingin peluang penggunaan index lebih baik.

Waspadai filter LIKE yang tidak ramah index

Contoh berikut lazim tetapi mahal pada dataset besar:

$query->when($request->q, function ($q, $keyword) {
    $q->where('customer_name', 'like', '%' . $keyword . '%');
});

Pencarian seperti ini sering tidak cocok dengan index B-Tree biasa. Jika fitur pencarian adalah kebutuhan utama, pertimbangkan:

membatasi pencarian pada prefix tertentu bila memungkinkan,
memisahkan pencarian full-text dari filter listing biasa,
menggunakan solusi search yang memang dirancang untuk keyword search jika kebutuhannya kompleks.

Kalau tidak, Anda bisa salah menyimpulkan bahwa semua index gagal, padahal masalahnya ada pada pola pencarian itu sendiri.

Jangan SELECT kolom berlebihan

Untuk admin listing, sering kali Anda tidak perlu semua kolom. Ambil hanya yang ditampilkan:

$query = Order::query()
    ->select(['id', 'tenant_id', 'status', 'total', 'created_at']);

Ini tidak selalu menyelesaikan bottleneck count, tetapi membantu mengurangi biaya I/O pada query data utama.

Strategi index: tunggal vs komposit sesuai pola WHERE dan ORDER BY

Kesalahan paling umum adalah menambahkan index ke setiap kolom filter secara terpisah, lalu berharap database selalu menggabungkannya secara optimal. Pada praktiknya, index komposit sering lebih efektif jika dibuat berdasarkan pola query yang paling sering dipakai.

Kapan index tunggal cukup

Index tunggal cocok jika:

query sering memfilter satu kolom yang sangat selektif,
sorting tidak dominan,
kombinasi filter sangat beragam dan tidak ada pola yang jelas,
Anda masih di tahap observasi dan belum tahu pola akses utama.

Contoh:

$table->index('tenant_id');
$table->index('status');
$table->index('created_at');

Ini bisa membantu, tetapi belum tentu optimal untuk query seperti WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC.

Kapan index komposit lebih tepat

Jika pola listing dominan adalah:

selalu dibatasi oleh tenant_id,
sering difilter oleh status,
hampir selalu diurutkan berdasarkan created_at DESC,

maka index komposit lebih masuk akal, misalnya:

$table->index(['tenant_id', 'status', 'created_at']);

Mengapa ini bekerja? Karena database dapat memanfaatkan urutan index untuk:

menyaring berdasarkan kolom awal index,
lalu membaca data dengan urutan yang lebih dekat ke ORDER BY,
mengurangi scan dan biaya sorting tambahan.

Urutan kolom di index penting. Jangan sekadar memasukkan semua kolom ke dalam satu index tanpa alasan.

Contoh pemetaan pola query ke index

Misalkan query paling umum Anda adalah:

WHERE tenant_id = ?
  AND status = ?
ORDER BY created_at DESC

Index kandidat yang layak diuji:

(tenant_id, status, created_at)

Kalau query lain yang dominan adalah:

WHERE tenant_id = ?
  AND created_at BETWEEN ? AND ?
ORDER BY created_at DESC

Index yang lebih relevan bisa menjadi:

(tenant_id, created_at)

Pelajaran utamanya: index harus mengikuti query paling sering dan paling mahal, bukan daftar semua kolom yang pernah dipakai filter.

Trade-off index komposit

Menambah index mempercepat baca, tetapi memperlambat INSERT/UPDATE.
Terlalu banyak index membuat ukuran tabel dan biaya maintenance naik.
Index komposit efektif untuk pola tertentu, tetapi belum tentu membantu query lain dengan urutan filter berbeda.

Jangan membuat banyak index “jaga-jaga”. Buat index berdasarkan data slow query dan validasi ulang dengan EXPLAIN.

Optimasi COUNT(*) untuk pagination di Laravel

Mengapa COUNT(*) sering lebih mahal dari yang diduga

paginate() nyaman karena memberi total halaman, total item, dan navigasi lengkap. Tetapi untuk menghasilkan itu, Laravel perlu menjalankan query count terpisah. Pada dataset besar, count bisa mahal karena:

harus mengevaluasi filter yang sama seperti query utama,
index tidak cocok dengan kondisi filter,
query mengandung join atau kondisi yang membuat count sulit dioptimalkan,
hasil cocok sangat banyak sehingga scan tetap besar.

Kapan tetap memakai paginate()

Pakai paginate() jika:

UI memang membutuhkan total data dan total halaman,
query count masih wajar setelah index diperbaiki,
listing dipakai untuk audit atau pelaporan yang membutuhkan angka total akurat.

Kapan beralih ke simplePaginate()

Jika UI admin tidak benar-benar butuh total halaman, simplePaginate() sering menjadi solusi paling murah dan paling praktis karena tidak melakukan count total yang sama beratnya.

$orders = $query->simplePaginate(50);

Cocok untuk:

daftar operasional internal,
infinite scroll atau navigasi next/previous,
halaman yang fokus ke kecepatan respons, bukan angka total akurat.

Trade-off-nya jelas: Anda kehilangan total item dan total halaman yang presisi.

Optimasi count terpisah saat total tetap dibutuhkan

Kalau total tetap penting, ada beberapa pendekatan praktis:

Perbaiki index untuk query count. Sering kali ini yang paling efektif dan paling aman.
Sederhanakan filter count jika kebutuhan bisnis mengizinkan. Misalnya, total berdasarkan filter inti saja, bukan semua variasi UI.
Cache hasil count untuk kombinasi filter yang sering dipakai dan tidak harus real-time ketat.
Pre-aggregation atau tabel ringkasan untuk kasus laporan berat, bukan listing transaksional biasa.

Yang perlu hati-hati: jangan memberi angka total yang tampak akurat jika sebenarnya hanya estimasi, kecuali UI memang menjelaskannya.

Contoh pemisahan query data dan query total

Pada kasus tertentu, Anda bisa lebih eksplisit agar mudah dikontrol dan diukur:

public function index(Request $request)
{
    $baseQuery = Order::query()
        ->when($request->tenant_id, fn ($q, $tenantId) => $q->where('tenant_id', $tenantId))
        ->when($request->status, fn ($q, $status) => $q->where('status', $status))
        ->when($request->date_from, fn ($q, $dateFrom) => $q->where('created_at', '>=', $dateFrom . ' 00:00:00'))
        ->when($request->date_to, fn ($q, $dateTo) => $q->where('created_at', '<', date('Y-m-d H:i:s', strtotime($dateTo . ' +1 day'))));

    $orders = (clone $baseQuery)
        ->select(['id', 'tenant_id', 'status', 'total', 'created_at'])
        ->orderByDesc('created_at')
        ->simplePaginate(50);

    $total = null;

    if ($request->boolean('show_total')) {
        $total = (clone $baseQuery)->count();
    }

    return view('orders.index', compact('orders', 'total'));
}

Pola ini berguna jika Anda ingin:

mengukur biaya count secara terpisah,
menunda count sampai memang diperlukan,
menerapkan cache hanya pada total tanpa mengganggu query data.

Implementasi migration index di Laravel

Setelah menemukan pola query dominan, tambahkan index lewat migration agar perubahan terkelola dan bisa direview.

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->index(['tenant_id', 'status', 'created_at'], 'orders_tenant_status_created_idx');
            $table->index(['tenant_id', 'created_at'], 'orders_tenant_created_idx');
        });
    }

    public function down(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->dropIndex('orders_tenant_status_created_idx');
            $table->dropIndex('orders_tenant_created_idx');
        });
    }
};

Beberapa catatan penting:

Jangan langsung menambah banyak index sekaligus tanpa baseline pengukuran.
Pilih nama index yang jelas agar mudah diidentifikasi.
Pastikan index baru memang tidak redundant dengan index komposit atau index lama yang sudah ada.

Validasi hasil setelah optimasi

Optimasi belum selesai hanya karena migration sudah dijalankan. Anda perlu membuktikan dampaknya.

1. Bandingkan query sebelum dan sesudah

Ulangi skenario yang sama:

filter paling umum,
filter paling berat,
sorting yang paling sering dipakai,
halaman pertama dan halaman berikutnya.

Lalu cek:

durasi query utama,
durasi query count,
jumlah baris yang dipindai menurut EXPLAIN,
apakah database benar-benar memakai index baru.

2. Validasi dari level aplikasi

Gunakan Debugbar atau Telescope untuk memastikan:

jumlah query tidak bertambah karena perubahan lain,
tidak ada N+1 baru yang menutupi hasil optimasi,
query yang terlihat di aplikasi sama dengan yang Anda analisis di database.

3. Uji dengan data yang representatif

Optimasi pada tabel kecil sering menipu. Pastikan pengujian dilakukan pada volume data yang cukup mirip kondisi nyata, terutama distribusi status, tenant, dan rentang tanggal. Index yang tampak bagus pada data kecil belum tentu memberi keuntungan saat distribusi data berubah.

Checklist anti-salah-index untuk admin listing Laravel

Apakah query lambat benar-benar sudah diidentifikasi? Jangan menambah index tanpa slow query atau pengukuran.
Apakah bottleneck ada di query data, query count, atau keduanya?
Apakah kolom paling kiri pada index komposit sesuai pola filter utama?
Apakah ORDER BY selaras dengan index yang dibuat?
Apakah ada fungsi pada kolom yang mengurangi peluang index dipakai?
Apakah pencarian LIKE %keyword% sedang menjadi sumber scan besar?
Apakah index baru redundant dengan index lama?
Apakah biaya write tambahan dari index baru masih dapat diterima?
Apakah simplePaginate sebenarnya sudah cukup untuk kebutuhan UI?
Apakah total harus akurat real-time, atau bisa ditunda/cached?

Rekomendasi praktis yang paling sering berhasil

Untuk mayoritas kasus admin listing Laravel yang melambat saat data melejit, urutan perbaikannya biasanya seperti ini:

Temukan query lambat dengan Debugbar/Telescope dan slow query log.
Analisis EXPLAIN untuk query listing dan query COUNT(*).
Rapikan query agar filter tanggal dan sorting tidak menghambat penggunaan index.
Tambahkan index komposit sesuai pola WHERE dan ORDER BY yang paling dominan.
Jika total halaman tidak wajib, ganti paginate() menjadi simplePaginate().
Kalau total wajib ada, optimalkan count secara terpisah dan pertimbangkan cache bila sesuai kebutuhan bisnis.

Poin terpentingnya: optimasi COUNT dan filter admin di Laravel bukan soal menambah index sebanyak mungkin, tetapi mencocokkan struktur index dengan query yang benar-benar dijalankan. Begitu query utama dan query count sama-sama dianalisis, bottleneck biasanya jauh lebih mudah dipetakan dan diperbaiki dengan perubahan yang terukur.

Quick Search

shopping_cart Keranjang