Laravel: Mempercepat Filter dan Sort dengan Index Komposit yang Tepat

Masalah yang paling sering muncul pada halaman list admin Laravel bukan ada di Eloquent-nya, melainkan di pola SQL yang dihasilkan: WHERE multi-kolom + ORDER BY + pagination pada tabel besar tanpa index yang sesuai. Akibatnya, database melakukan scan terlalu banyak baris, sorting di luar index, lalu semakin lambat saat data tumbuh.

Solusi yang biasanya efektif bukan menambah cache lebih dulu, tetapi merancang index komposit berdasarkan query nyata. Jika urutan kolom pada index selaras dengan filter dan sorting yang paling sering dipakai, MySQL atau MariaDB dapat mengurangi jumlah baris yang dibaca, menghindari filesort pada beberapa kasus, dan membuat halaman list admin jauh lebih stabil performanya.

Gejala umum pada list admin Laravel yang mulai lambat

Misalkan Anda punya tabel orders dengan jutaan baris. Di halaman admin, pengguna bisa memfilter berdasarkan status, tenant, rentang tanggal, lalu mengurutkan berdasarkan created_at terbaru. Query seperti ini terlihat wajar, tetapi sering menjadi bottleneck:

Order::query()
    ->where('tenant_id', $tenantId)
    ->where('status', $status)
    ->whereBetween('created_at', [$from, $to])
    ->orderByDesc('created_at')
    ->paginate(50);

Di dataset kecil, query ini tampak aman. Namun saat data membesar, beberapa masalah mulai terlihat:

WHERE multi-kolom membuat database sulit memilih jalur akses jika hanya ada index tunggal yang terpisah-pisah.
ORDER BY bisa memaksa sorting tambahan jika urutannya tidak didukung index.
SELECT * mengambil kolom berlebih, menambah I/O dan transfer data.
OFFSET pagination makin mahal di halaman besar karena database tetap harus melewati banyak baris sebelum mengembalikan hasil.

Kesalahan umum adalah menganggap index tunggal pada setiap kolom sudah cukup, misalnya index untuk tenant_id, status, dan created_at secara terpisah. Pada banyak query gabungan, pendekatan itu tidak memberi hasil optimal.

Kenapa index tunggal sering gagal membantu

Index tunggal bekerja baik jika query Anda sederhana, misalnya hanya filter satu kolom. Masalah muncul ketika query menggabungkan beberapa kondisi dan sorting.

Contoh: Anda memiliki index berikut:

INDEX (tenant_id)
INDEX (status)
INDEX (created_at)

Secara teori semua kolom penting sudah ter-index. Namun database sering tetap harus memilih satu index utama, lalu melakukan filter tambahan setelah membaca banyak baris. Dalam beberapa engine dan skenario, optimizer bisa menggabungkan index, tetapi hasilnya belum tentu efisien untuk query list yang butuh filter dan sort sekaligus.

Masalah lainnya, index tunggal pada kolom sort tidak otomatis membantu jika filter utama ada di kolom lain. Misalnya Anda memfilter tenant_id dan status, lalu sort created_at DESC. Jika index yang dipakai hanya created_at, database mungkin tetap membaca banyak baris yang tidak relevan.

Prinsip penting: index yang efektif untuk query list biasanya mengikuti pola akses query, bukan sekadar menambahkan index di setiap kolom yang terlihat penting.

Cara membaca EXPLAIN secara dasar

Sebelum menambah index, lihat dulu bagaimana database mengeksekusi query. Jalankan EXPLAIN pada SQL yang benar-benar dipakai aplikasi.

Contoh SQL yang kira-kira dihasilkan:

SELECT id, order_number, customer_name, status, created_at
FROM orders
WHERE tenant_id = 10
  AND status = 'paid'
  AND created_at BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59'
ORDER BY created_at DESC
LIMIT 50 OFFSET 0;

Lalu jalankan:

EXPLAIN
SELECT id, order_number, customer_name, status, created_at
FROM orders
WHERE tenant_id = 10
  AND status = 'paid'
  AND created_at BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59'
ORDER BY created_at DESC
LIMIT 50 OFFSET 0;

Beberapa kolom EXPLAIN yang paling berguna untuk dibaca secara praktis:

type: indikasi cara akses data. Semakin selektif biasanya semakin baik. Jika sering melihat ALL, itu tanda full table scan.
key: index yang dipilih optimizer. Jika kosong, query berjalan tanpa index yang dipakai.
rows: estimasi jumlah baris yang perlu dibaca. Jika nilainya besar untuk halaman list kecil, biasanya ada ruang optimasi.
Extra: perhatikan hal seperti Using where, Using filesort, atau Using temporary. Ini bukan selalu buruk, tetapi sering menjadi sinyal query belum ideal.

Yang perlu dicari bukan “harus sempurna”, tetapi apakah index yang dipilih benar-benar selaras dengan pola query Anda. Jika rows besar dan ada Using filesort pada list yang sering diakses, itu kandidat kuat untuk perbaikan index.

Merancang index komposit untuk filter dan sort

Urutan kolom pada index itu penting

Index komposit bukan sekadar gabungan beberapa kolom. Urutan kolom di dalam index menentukan kapan index itu efektif.

Untuk query seperti ini:

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

Index yang sering masuk akal adalah:

(tenant_id, status, created_at)

Kenapa urutan ini masuk akal?

tenant_id dan status adalah filter equality yang membatasi ruang pencarian lebih dulu.
created_at dipakai untuk range dan sorting.
Database bisa menelusuri bagian index yang relevan untuk tenant dan status tertentu, lalu membaca created_at dalam urutan yang dibutuhkan.

Jika justru Anda membuat index (created_at, tenant_id, status), biasanya manfaatnya lebih kecil untuk query tersebut karena pencarian dimulai dari kolom waktu, padahal filter utama ada di tenant dan status.

Kapan urutan index perlu berbeda

Tidak ada satu urutan index yang cocok untuk semua query. Pilih berdasarkan pola akses yang paling sering dan paling mahal. Contohnya:

Jika hampir semua query selalu difilter tenant_id lalu diurutkan created_at, index (tenant_id, created_at) bisa lebih relevan.
Jika status opsional dan jarang dipakai, menaruhnya di tengah bisa justru mengurangi fleksibilitas pada query lain.
Jika filter tanggal sangat lebar dan kurang selektif, index tetap berguna, tetapi jangan berharap semua kasus jadi cepat secara drastis.

Karena itu, rancang index berdasarkan query dominan, bukan semua kemungkinan kombinasi filter yang ada di UI.

Contoh migration Laravel untuk menambah index komposit

Di Laravel, index komposit bisa ditambahkan lewat migration. Contoh:

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_at_idx'
            );
        });
    }

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

Beberapa catatan praktis:

Gunakan nama index yang jelas agar mudah dikenali saat membaca EXPLAIN atau saat rollback.
Tambahkan index hanya setelah melihat query nyata, bukan menebak-nebak dari struktur tabel.
Jika tabel sangat besar, penambahan index perlu dijadwalkan dengan hati-hati karena bisa memakan waktu dan resource.

Optimasi query builder atau Eloquent: sebelum dan sesudah

Sebelum: query berat tanpa seleksi kolom yang ketat

$orders = Order::query()
    ->where('tenant_id', $tenantId)
    ->when($status, fn ($q) => $q->where('status', $status))
    ->when($from && $to, fn ($q) => $q->whereBetween('created_at', [$from, $to]))
    ->orderByDesc('created_at')
    ->paginate(50);

Query di atas tidak salah, tetapi ada dua masalah umum:

Jika model memiliki banyak kolom, Eloquent akan mengambil semuanya secara default.
Tanpa index komposit yang cocok, filter dan sorting tetap mahal.

Sesudah: kolom dipersempit dan pola query diselaraskan dengan index

$orders = Order::query()
    ->select([
        'id',
        'order_number',
        'customer_name',
        'status',
        'created_at',
    ])
    ->where('tenant_id', $tenantId)
    ->when($status !== null, fn ($q) => $q->where('status', $status))
    ->when($from && $to, fn ($q) => $q->whereBetween('created_at', [$from, $to]))
    ->orderByDesc('created_at')
    ->paginate(50);

Perubahan ini sederhana, tetapi penting:

Select kolom seperlunya mengurangi data yang dibaca dan dikirim.
Pola filter dan sort konsisten memudahkan index komposit bekerja optimal.

Jika Anda perlu relasi, hindari eager loading berlebihan untuk halaman list. Ambil hanya relasi yang benar-benar ditampilkan, dan pertimbangkan kolom spesifik pada relasi juga.

Pagination biasa ikut melambat saat data tumbuh

paginate() nyaman karena memberi total count dan navigasi halaman. Namun pada data besar, ada dua biaya yang sering terasa:

Query utama dengan LIMIT ... OFFSET ... makin mahal di halaman jauh.
Perhitungan total baris untuk pagination juga bisa menambah beban.

Jika use case admin tidak selalu membutuhkan total halaman yang presisi, Anda bisa mempertimbangkan simplePaginate() untuk mengurangi overhead count.

$orders = Order::query()
    ->select(['id', 'order_number', 'customer_name', 'status', 'created_at'])
    ->where('tenant_id', $tenantId)
    ->when($status !== null, fn ($q) => $q->where('status', $status))
    ->orderByDesc('created_at')
    ->simplePaginate(50);

Kalau pengguna sering membuka halaman sangat jauh, pertimbangkan cursor pagination atau pendekatan berbasis posisi data, terutama untuk sorting yang stabil seperti created_at ditambah id. Ini sering lebih efisien dibanding OFFSET besar.

Catatan: cursor pagination cocok jika urutan data jelas dan konsisten. Jika UI admin sangat bergantung pada nomor halaman absolut, Anda perlu menimbang komprominya.

Contoh desain index untuk skenario nyata

Anggap query admin yang paling sering adalah:

Selalu filter tenant_id
Sering filter status
Sering sort created_at DESC

Maka kandidat index yang wajar:

(tenant_id, status, created_at)

Jika ada skenario kedua yang sangat sering:

Filter hanya tenant_id
Sort created_at DESC

Maka Anda mungkin juga butuh:

(tenant_id, created_at)

Namun jangan langsung menambah semua kombinasi. Setiap index tambahan ada biayanya:

INSERT dan UPDATE menjadi lebih mahal karena semua index harus ikut diperbarui.
Ukuran penyimpanan naik.
Optimizer punya lebih banyak pilihan, yang tidak selalu berarti lebih baik.

Karena itu, prioritaskan index untuk query yang paling sering, paling lambat, atau paling berdampak ke pengguna.

Trade-off dan kesalahan yang sering terjadi

1. Terlalu banyak index

Menambah index tanpa evaluasi bisa memperlambat operasi tulis. Pada tabel transaksi yang aktif ditulis, ini sangat terasa. Fokus pada beberapa index yang benar-benar mendukung beban baca utama.

2. Mengabaikan urutan kolom

(status, tenant_id, created_at) dan (tenant_id, status, created_at) bukan hal yang sama. Jika query dominan selalu dimulai dari tenant_id, urutan yang salah bisa membuat index jauh kurang berguna.

3. Menganggap semua ORDER BY akan memakai index

Jika ada fungsi, ekspresi, atau kombinasi filter yang tidak sesuai dengan urutan index, database tetap bisa melakukan filesort. Ini normal. Tujuannya adalah mengurangi biaya query nyata, bukan memaksa semua EXPLAIN terlihat sempurna.

4. Tetap memakai SELECT *

Pada tabel lebar, mengambil semua kolom untuk halaman list adalah pemborosan. Ini sering luput karena query tetap “berjalan”, padahal I/O-nya besar.

5. Tidak memeriksa query count pagination

Developer sering fokus pada query data utama, tetapi lupa bahwa pagination standar biasanya menjalankan query tambahan untuk menghitung total. Pada tabel besar, query count juga perlu diperiksa.

Checklist verifikasi setelah index ditambahkan

Setelah migration dijalankan, jangan berhenti di “sudah ada index”. Verifikasi hasilnya secara terukur:

Bandingkan EXPLAIN sebelum dan sesudah
Lihat apakah key berubah ke index komposit yang Anda tambahkan, dan apakah estimasi rows menurun.
Catat durasi query dari aplikasi
Gunakan logging query atau alat observabilitas yang sudah tersedia untuk melihat durasi query list admin sebelum dan sesudah perubahan.
Periksa slow query log
Jika database Anda mengaktifkan slow query log, cek apakah query list admin masih muncul secara konsisten setelah optimasi.
Uji beberapa kombinasi filter populer
Index yang bagus untuk satu kombinasi belum tentu optimal untuk kombinasi lain. Uji skenario yang benar-benar dipakai user.
Periksa dampak ke operasi tulis
Jika tabel juga sibuk menerima insert atau update, amati apakah ada kenaikan latensi pada operasi tulis setelah index baru ditambahkan.

Tips debugging yang praktis

Log SQL yang benar-benar dijalankan

Jangan menebak dari kode Eloquent saja. Ambil SQL aktual beserta binding-nya saat perlu, lalu analisis query yang benar-benar dikirim ke database.

Optimalkan query dominan, bukan semua kemungkinan UI

Jika halaman admin punya 10 kombinasi filter tetapi hanya 2 yang paling sering dipakai, fokus di sana dulu. Index untuk semua kombinasi biasanya berlebihan.

Pastikan sort stabil untuk pagination

Jika banyak baris punya created_at yang sama, pertimbangkan menambah tie-breaker seperti id pada sorting agar hasil pagination lebih konsisten. Misalnya:

$orders = Order::query()
    ->select(['id', 'order_number', 'customer_name', 'status', 'created_at'])
    ->where('tenant_id', $tenantId)
    ->orderByDesc('created_at')
    ->orderByDesc('id')
    ->simplePaginate(50);

Jika pola ini menjadi standar, desain index juga perlu mempertimbangkan kolom tambahan pada sort tersebut.

Kesimpulan

Untuk mempercepat filter dan sort di Laravel pada tabel besar, fokus utamanya adalah mencocokkan pola query dengan index komposit yang tepat. Index tunggal per kolom sering tidak cukup saat query menggabungkan WHERE multi-kolom, ORDER BY, dan pagination.

Langkah yang paling praktis adalah: identifikasi query admin yang lambat, baca EXPLAIN secara dasar, kurangi kolom yang di-select, rancang index komposit sesuai urutan filter dan sort, lalu verifikasi hasilnya lewat slow query log dan metrik durasi query. Dengan pendekatan ini, Anda memperbaiki akar bottleneck SQL, bukan hanya menutupi gejalanya.

Quick Search

shopping_cart Keranjang