Laravel: Dari Slow Query Log ke Index Komposit untuk Halaman Daftar Besar

Ketika halaman daftar di Laravel mulai lambat setelah tabel membesar, penyebabnya sering bukan pada Blade atau Eloquent itu sendiri, melainkan pada query yang tidak lagi cocok dengan pola akses data. Gejala paling umum adalah endpoint list yang tadinya cepat menjadi berat saat baris data mencapai ratusan ribu atau jutaan, terutama jika ada filter status, rentang waktu, tenant, dan urutan berdasarkan tanggal atau ID terbaru.

Pendekatan yang paling efektif adalah mulai dari slow query log, lalu cari pola query yang berulang: kombinasi WHERE + ORDER BY pada halaman daftar. Dari sana, pilih index komposit yang mengikuti cara data difilter dan diurutkan, bukan sekadar menambahkan index satu kolom secara acak. Di Laravel, ini sering memberi dampak jauh lebih besar daripada mengutak-atik kode aplikasi tanpa bukti dari query yang benar-benar lambat.

Mengenali gejala halaman daftar yang melambat

Pada kasus nyata, gejala yang muncul biasanya seperti ini:

Endpoint /orders atau /transactions makin lama saat data tumbuh.
CPU database meningkat saat ada banyak request list, meski query detail by primary key tetap cepat.
Pagination halaman awal masih lumayan, tetapi filter tertentu sangat lambat.
Profiling menunjukkan waktu habis di query database, bukan di rendering view.
Log database atau APM memperlihatkan query dengan WHERE ... ORDER BY ... LIMIT ... OFFSET ... yang konsisten lambat.

Contoh query daftar yang sering muncul di aplikasi Laravel:

SELECT id, order_number, customer_id, status, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 0;

Secara bisnis, query ini masuk akal: hanya ambil order milik tenant tertentu, status tertentu, tampilkan yang terbaru. Masalahnya, jika tabel besar dan index tidak mengikuti pola ini, database bisa melakukan scan jauh lebih banyak daripada yang dibutuhkan.

Mulai dari slow query log, bukan tebak-tebakan

Jangan langsung menambah index tanpa melihat query nyata yang lambat. Audit yang baik dimulai dari slow query log database atau observability tool yang setara. Tujuannya adalah mengumpulkan query yang benar-benar mahal berdasarkan waktu eksekusi dan frekuensi.

Apa yang perlu dicari di log

Query list yang sering dipanggil oleh endpoint yang sama.
Nilai parameter yang realistis, misalnya tenant_id, status, atau rentang tanggal tertentu.
Pola urutan, misalnya ORDER BY created_at DESC atau ORDER BY id DESC.
Adanya OFFSET besar pada pagination.
Query yang memakai SELECT * padahal daftar hanya menampilkan sedikit kolom.

Jika Anda juga ingin melihat dari sisi Laravel, logging query aplikasi bisa membantu validasi endpoint mana yang memicu SQL tersebut. Namun, sumber utama untuk tuning tetap query aktual yang dieksekusi database.

Contoh query dari Eloquent / Query Builder

Berikut contoh implementasi Laravel yang umum pada halaman daftar:

$orders = Order::query()
    ->where('tenant_id', $tenantId)
    ->where('status', 'paid')
    ->orderByDesc('created_at')
    ->orderByDesc('id')
    ->paginate(50);

Atau dengan Query Builder:

$orders = DB::table('orders')
    ->select(['id', 'order_number', 'customer_id', 'status', 'created_at', 'total_amount'])
    ->where('tenant_id', $tenantId)
    ->where('status', 'paid')
    ->orderByDesc('created_at')
    ->orderByDesc('id')
    ->paginate(50);

Di tahap ini, fokusnya bukan pada sintaks Laravel, melainkan SQL yang dihasilkan. Query di atas punya pola yang sangat jelas: filter dulu, lalu urutkan. Itu sinyal kuat bahwa index komposit kemungkinan dibutuhkan.

Identifikasi pola WHERE + ORDER BY yang menentukan index

Kesalahan umum adalah membuat index terpisah seperti:

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

Secara teori terlihat berguna, tetapi untuk query daftar besar, index terpisah sering tidak cukup. Database masih bisa kesulitan menggabungkan filtering dan sorting secara efisien, lalu berakhir melakukan scan besar atau filesort.

Untuk query ini:

SELECT id, order_number, customer_id, status, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

pola akses datanya adalah:

Filter kesetaraan pada tenant_id.
Filter kesetaraan pada status.
Urutan pada created_at lalu id.

Dalam banyak kasus, index komposit yang lebih cocok adalah:

(tenant_id, status, created_at, id)

Mengapa urutannya demikian? Karena bagian depan index dipakai untuk menyempitkan data berdasarkan kondisi WHERE, lalu sisa kolom membantu mengambil baris dalam urutan yang sudah sesuai. Dengan begitu, database lebih mungkin membaca data dari index dalam urutan yang dibutuhkan, bukan memfilter banyak baris lalu menyortirnya setelah itu.

Urutan kolom dalam index komposit bukan kosmetik. Index (tenant_id, status, created_at, id) berbeda makna dengan (created_at, status, tenant_id, id). Susun berdasarkan pola query yang dominan, terutama equality filter lalu kolom sorting.

Membaca EXPLAIN: apa yang perlu diperhatikan

Setelah menemukan query lambat, jalankan EXPLAIN terhadap SQL aktualnya. Tujuannya bukan menghafal semua kolom output, tetapi melihat apakah database memakai index yang relevan dan berapa banyak baris yang kira-kira diproses.

Contoh sebelum perbaikan

EXPLAIN
SELECT id, order_number, customer_id, status, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 50;

Hal-hal yang perlu dicurigai pada hasil EXPLAIN:

type buruk, misalnya scan luas pada tabel atau index yang tidak selektif.
key kosong atau memakai index yang tidak sesuai pola query.
rows sangat besar untuk mengambil hanya 50 baris.
Indikasi sorting tambahan seperti filesort.
Indikasi harus membaca baris tabel terlalu banyak karena kolom yang dipilih terlalu lebar.

Misalnya, jika EXPLAIN menunjukkan database memakai index hanya pada status, itu biasanya belum cukup. Untuk status seperti paid yang mungkin jumlahnya besar, database masih harus membaca banyak baris lalu menyaring tenant_id dan menyortir created_at.

Sesudah menambah index komposit

Jika Anda menambahkan index yang sesuai:

CREATE INDEX idx_orders_tenant_status_created_id
ON orders (tenant_id, status, created_at, id);

lalu jalankan lagi EXPLAIN, yang ingin dilihat adalah:

key mengarah ke index komposit baru.
rows yang diestimasi menurun signifikan.
Sorting tambahan berkurang atau hilang jika database bisa memanfaatkan urutan dari index.

Anda tidak perlu mengejar output EXPLAIN yang “sempurna” secara teori. Fokusnya praktis: apakah query daftar nyata sekarang membaca lebih sedikit data dan merespons lebih cepat di beban aktual.

Merevisi migration dan query Laravel

Migration untuk index komposit

Jika pola query sudah tervalidasi, tambahkan index lewat migration agar perubahan terkontrol 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', 'id'],
                'idx_orders_tenant_status_created_id'
            );
        });
    }

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

Nama index eksplisit membantu saat audit dan rollback. Untuk tabel besar, perhatikan strategi deploy karena pembuatan index bisa mahal bergantung engine database, ukuran tabel, dan traffic write.

Kurangi SELECT *

Jika halaman daftar hanya butuh beberapa kolom, jangan gunakan SELECT *. Semakin banyak kolom yang diambil, semakin besar I/O dan transfer data. Ini makin terasa jika ada kolom teks besar atau JSON.

Kurang baik:

$orders = Order::query()
    ->where('tenant_id', $tenantId)
    ->where('status', 'paid')
    ->orderByDesc('created_at')
    ->paginate(50);

Lebih baik untuk daftar:

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

Perhatikan penambahan id sebagai tie-breaker di ORDER BY. Ini penting agar urutan stabil ketika banyak baris memiliki created_at yang sama, dan juga membantu menyelaraskan pola query dengan index.

Contoh kasus transactions

Pola yang sama berlaku untuk tabel seperti transactions:

$transactions = DB::table('transactions')
    ->select(['id', 'account_id', 'type', 'amount', 'created_at'])
    ->where('account_id', $accountId)
    ->where('type', 'debit')
    ->orderByDesc('created_at')
    ->orderByDesc('id')
    ->limit(100)
    ->get();

Index yang sering lebih cocok untuk pola ini adalah:

(account_id, type, created_at, id)

Bukan sekadar (account_id) atau (created_at) saja.

Dampak dan batasan index komposit

Setelah index komposit ditambahkan, dampak yang biasanya terlihat adalah:

Jumlah baris yang dipindai turun.
Sorting tambahan berkurang.
Latency endpoint daftar menjadi lebih stabil.
Beban database saat traffic list tinggi ikut turun.

Namun, index bukan gratis.

Trade-off: terlalu banyak index memperlambat write

Setiap INSERT, UPDATE, dan kadang DELETE harus memelihara index terkait. Jika Anda menambah terlalu banyak index “jaga-jaga”, konsekuensinya:

Write menjadi lebih mahal.
Storage bertambah.
Planner punya lebih banyak opsi yang belum tentu membantu.
Maintenance schema jadi lebih rumit.

Karena itu, pilih index berdasarkan query yang benar-benar sering dan mahal. Jika ada dua index yang hampir duplikat, audit apakah salah satunya bisa dihapus.

Index komposit bukan solusi universal

Beberapa kondisi perlu perhatian tambahan:

Jika ada rentang seperti WHERE created_at BETWEEN ..., desain index perlu dievaluasi terhadap urutan filter lain.
Jika pencarian memakai LIKE '%keyword%', index biasa sering tidak membantu.
Jika query sangat dinamis dengan banyak kombinasi filter, tidak selalu mungkin membuat satu index ideal untuk semua kasus.
Jika selektivitas kolom rendah, index tertentu bisa kurang efektif meski secara teori cocok.

Intinya, index komposit harus mengikuti query dominan, bukan semua kemungkinan query.

Offset pagination: bukan fokus utama, tapi sering memperburuk performa

Meski fokus artikel ini adalah slow query log dan index komposit, perlu dicatat bahwa offset pagination dapat memperburuk performa saat halaman makin dalam. Query seperti:

... ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 50000;

tetap bisa mahal karena database sering harus melewati banyak baris sebelum mengambil 50 baris berikutnya. Index yang baik membantu, tetapi offset besar tetap punya biaya.

Jika daftar Anda sering diakses sampai halaman dalam, pertimbangkan cursor-based pagination atau strategi seek method. Di Laravel, ini relevan untuk list besar yang diurutkan berdasarkan kolom monoton seperti created_at dan id. Namun, lakukan setelah query dasarnya sehat; jangan memakai cursor pagination untuk menutupi index yang salah.

Langkah audit yang praktis di produksi

Berikut alur yang bisa langsung dipakai saat halaman daftar Laravel melambat:

Ambil query lambat dari slow query log atau monitoring database.
Kelompokkan per endpoint, misalnya /orders atau /transactions.
Salin SQL aktual dan parameter realistis, jangan memakai contoh dummy yang tidak representatif.
Jalankan EXPLAIN untuk melihat index yang dipakai, estimasi rows, dan indikasi sorting tambahan.
Identifikasi pola WHERE + ORDER BY, terutama equality filter diikuti sorting.
Buat index komposit yang sesuai dengan pola query dominan.
Kurangi SELECT * jika halaman daftar tidak butuh semua kolom.
Uji lagi dengan EXPLAIN dan traffic realistis.
Pantau dampak ke write jika tabel punya laju insert/update tinggi.
Audit index lama agar tidak menumpuk index yang redundan.

Kesalahan yang paling sering terjadi

Menambah banyak single-column index lalu mengira masalah selesai.
Membuat index tanpa melihat query nyata dari slow query log.
Mengabaikan urutan kolom pada index komposit.
Memakai SELECT * pada halaman daftar besar.
Tidak menambahkan tie-breaker seperti id pada ORDER BY.
Mengoptimalkan query yang jarang dipakai, sementara query paling mahal dibiarkan.
Fokus pada cache terlalu cepat, padahal query dasarnya memang buruk.

Checklist implementasi untuk produksi

Sudah ada daftar top slow queries untuk endpoint list.
SQL aktual dan parameter contoh sudah disimpan untuk reproduksi.
EXPLAIN sebelum perubahan sudah didokumentasikan.
Index komposit disusun mengikuti pola WHERE + ORDER BY.
Migration penambahan index sudah direview.
Query daftar sudah memakai kolom yang diperlukan saja.
Urutan hasil stabil, misalnya ORDER BY created_at DESC, id DESC.
Monitoring pasca deploy siap untuk melihat efek ke read dan write.
Rencana rollback tersedia jika pembuatan index mengganggu beban produksi.

Penutup

Untuk halaman daftar Laravel yang melambat saat data tumbuh, langkah paling praktis adalah jangan menebak. Mulailah dari slow query log, temukan query daftar yang dominan, baca pola WHERE + ORDER BY, lalu pilih index komposit yang sesuai. Dalam banyak kasus tabel seperti orders atau transactions, perbaikan terbesar datang dari satu keputusan schema yang tepat, bukan dari perubahan kode aplikasi yang rumit.

Jika Anda ingin hasil yang konsisten di produksi, audit juga SELECT *, stabilitas urutan, dan dampak offset pagination. Optimasi query yang benar bukan sekadar membuat EXPLAIN terlihat lebih bagus, tetapi mengurangi pekerjaan database pada jalur request yang paling sering dipakai.

Quick Search

shopping_cart Keranjang