Ketika endpoint daftar di Laravel awalnya terasa cepat lalu makin lambat setelah tabel membesar, masalahnya sering bukan di controller atau Blade, melainkan di pola query dan index yang tidak lagi cocok dengan beban nyata. Gejalanya biasanya jelas: waktu respons naik, CPU database meningkat, query list dengan filter atau sort mulai timeout, dan puncaknya muncul di halaman admin, API pencarian, atau dashboard laporan.

Audit query lambat di Laravel sebaiknya dilakukan secara berurutan: rekam query yang benar-benar jalan, temukan query lambat dari database, baca EXPLAIN, lalu sesuaikan index dengan pola WHERE, JOIN, dan ORDER BY. Menambahkan index tanpa memahami query planner sering hanya memindahkan masalah, bahkan bisa memperlambat operasi write.

Mengenali gejala yang patut diaudit

Query lambat biasanya mulai terasa pada endpoint dengan karakteristik berikut:

  • Halaman daftar memakai filter status, rentang tanggal, pencarian, dan sorting.
  • API pagination tetap memuat data dengan offset besar.
  • Relasi ditarik berlebihan atau memicu N+1 query.
  • Tabel tumbuh dari ribuan menjadi ratusan ribu atau jutaan baris.
  • Database mulai sering menunjukkan beban tinggi pada jam sibuk.

Contoh kasus nyata: endpoint orders index menampilkan daftar pesanan dengan filter status, rentang waktu, dan urutan terbaru. Saat data masih sedikit, query tampak normal. Setelah data tumbuh, query yang sama melakukan full table scan dan filesort, sehingga latensi naik drastis.

Langkah 1: Rekam query dari aplikasi Laravel

Mulailah dari aplikasi agar Anda tahu query mana yang dipicu endpoint tertentu. Tujuannya bukan sekadar melihat SQL, tetapi menghubungkan query dengan request nyata.

Menggunakan query listener

Untuk audit lokal atau staging, Anda bisa mendengarkan query yang dieksekusi dan durasinya:

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

DB::listen(function ($query) {
    Log::debug('SQL', [
        'sql' => $query->sql,
        'bindings' => $query->bindings,
        'time_ms' => $query->time,
    ]);
});

Pasang di lokasi yang sesuai untuk kebutuhan audit, misalnya provider khusus untuk environment non-production. Dari sini Anda bisa melihat:

  • Query yang sama dieksekusi berulang.
  • Query yang durasinya tinggi.
  • Query yang terlihat sederhana tetapi dipanggil sangat sering.

Hindari menyalakan logging query secara agresif di production tanpa pembatasan. Logging semua query bisa menambah overhead dan membuat log sulit dianalisis.

Fokus pada endpoint bermasalah

Jangan audit seluruh aplikasi sekaligus. Pilih satu endpoint yang jelas gejalanya, lalu rekam:

  1. URL dan parameter request.
  2. Jumlah query yang berjalan.
  3. Query paling lambat.
  4. Total waktu database dibanding total waktu respons.

Kalau total waktu request tinggi tetapi waktu query rendah, bottleneck mungkin bukan di database. Namun jika satu atau dua query mendominasi, lanjutkan audit ke level database.

Langkah 2: Cocokkan dengan slow query log database

Query log Laravel membantu mengaitkan query dengan request aplikasi, tetapi slow query log database memberi bukti yang lebih objektif tentang query yang benar-benar mahal. Ini penting karena query yang tampak biasa di kode bisa menjadi mahal ketika dijalankan pada volume data produksi.

Yang perlu dicari dari slow query log:

  • Query dengan durasi tinggi dan frekuensi tinggi.
  • Query yang memindai banyak baris.
  • Query yang sering muncul dari endpoint tertentu.

Bandingkan query dari Laravel dengan yang muncul di slow query log. Jika cocok, Anda sudah punya target audit yang jelas. Jika berbeda, mungkin ada query latar belakang lain seperti job, laporan, atau relasi eager loading yang ikut membebani database.

Langkah 3: Baca EXPLAIN untuk menemukan akar masalah

Setelah menemukan query lambat, jalankan EXPLAIN pada SQL final-nya. Tujuan EXPLAIN bukan sekadar melihat apakah index dipakai, tetapi memahami bagaimana database berniat mengeksekusi query.

Misalnya ada query daftar pesanan seperti ini:

SELECT id, user_id, status, created_at, total
FROM orders
WHERE status = 'paid'
  AND created_at >= '2024-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

Tanda bahaya yang umum saat membaca hasil EXPLAIN:

  • Full table scan: database membaca hampir seluruh tabel karena tidak menemukan index yang cocok.
  • Filesort: database perlu melakukan proses sort tambahan karena urutan data tidak bisa dipenuhi dari index.
  • Index tidak terpakai: index ada, tetapi tidak sesuai dengan urutan kolom atau pola filter query.
  • Terlalu banyak rows examined: jumlah baris yang diperiksa jauh lebih besar daripada jumlah baris hasil.

Interpretasi praktis yang perlu diperhatikan

Secara umum, saat membaca EXPLAIN, perhatikan beberapa hal berikut:

  • type: jika mendekati scan penuh, itu sinyal bahwa akses data mahal.
  • possible_keys dan key: apakah ada index yang mungkin dipakai, dan index mana yang akhirnya dipilih.
  • rows: perkiraan jumlah baris yang harus dibaca.
  • Indikasi seperti Using where, Using index, atau Using filesort di kolom extra/informasi tambahan.

Anda tidak perlu menghafal semua detail EXPLAIN. Fokus pada pertanyaan sederhana: apakah database membaca terlalu banyak baris, dan apakah sorting/filtering bisa dibantu index yang lebih tepat?

Contoh query Eloquent yang buruk dan kenapa melambat

Misalkan endpoint daftar pesanan ditulis seperti ini:

$orders = Order::query()
    ->with('user')
    ->when($status, fn ($q) => $q->where('status', $status))
    ->when($fromDate, fn ($q) => $q->where('created_at', '>=', $fromDate))
    ->orderBy('created_at', 'desc')
    ->paginate(50);

Di level kode, query ini terlihat baik. Masalahnya muncul kalau tabel orders hanya punya index default pada primary key, atau hanya index tunggal yang tidak sesuai dengan pola query. Akibatnya:

  • Filter status tidak terbantu index yang tepat.
  • Sort ORDER BY created_at DESC memicu filesort.
  • Semakin besar tabel, semakin mahal biaya membaca dan mengurutkan data.

Kesalahan umum: membuat index tunggal terpisah lalu berharap query jadi cepat

Banyak developer menambahkan index seperti ini:

Schema::table('orders', function ($table) {
    $table->index('status');
    $table->index('created_at');
});

Ini kadang membantu, tetapi tidak selalu optimal untuk query yang memfilter berdasarkan status lalu mengurutkan created_at. Database mungkin tetap harus melakukan pekerjaan tambahan karena kombinasi akses datanya tidak cocok sepenuhnya dengan dua index terpisah.

Refactor: pilih index sesuai pola query nyata

Untuk query di atas, kandidat yang lebih masuk akal biasanya adalah composite index yang mengikuti pola filter dan urutan:

Schema::table('orders', function ($table) {
    $table->index(['status', 'created_at']);
});

Mengapa ini lebih baik?

  • Kolom status dipakai untuk menyaring data lebih dulu.
  • Kolom created_at membantu membaca hasil dalam urutan yang lebih sesuai.
  • Peluang filesort berkurang jika planner bisa memanfaatkan urutan index.

Setelah index dibuat, jalankan lagi EXPLAIN. Yang ingin Anda lihat adalah penurunan jumlah baris yang diperiksa dan rencana eksekusi yang lebih selektif.

Contoh refactor query yang lebih disiplin

Jika endpoint hanya butuh kolom tertentu, jangan ambil semua kolom model tanpa alasan. Batasi seleksi agar data yang dibaca dan dikirim lebih kecil:

$orders = Order::query()
    ->select(['id', 'user_id', 'status', 'created_at', 'total'])
    ->when($status, fn ($q) => $q->where('status', $status))
    ->when($fromDate, fn ($q) => $q->where('created_at', '>=', $fromDate))
    ->orderByDesc('created_at')
    ->paginate(50);

Refactor ini tidak menggantikan index, tetapi membantu mengurangi beban I/O. Audit performa yang baik hampir selalu melibatkan kombinasi query lebih efisien dan index yang sesuai.

Kapan index tunggal cukup, kapan perlu composite index

Index tunggal cocok jika

  • Query sering memfilter satu kolom saja, misalnya WHERE email = ?.
  • Kolom memang sangat selektif dan sering dipakai sendiri.
  • Tidak ada kombinasi filter/sort yang dominan.

Composite index lebih cocok jika

  • Query rutin memakai kombinasi kolom yang sama.
  • Urutan kolom pada WHERE dan ORDER BY konsisten.
  • Anda ingin mengurangi scan besar dan sorting tambahan.

Hal penting: urutan kolom pada composite index sangat menentukan. Index (status, created_at) tidak identik dengan (created_at, status). Pilih urutan berdasarkan pola query yang paling sering dan paling mahal.

Pola query yang sering membuat index tidak terpakai

1. Fungsi pada kolom yang di-filter

Contoh buruk:

$orders = Order::query()
    ->whereRaw('DATE(created_at) = ?', [$date])
    ->get();

Fungsi pada kolom sering membuat database sulit memakai index secara efektif. Lebih baik ubah menjadi rentang waktu:

$orders = Order::query()
    ->whereBetween('created_at', [
        $date . ' 00:00:00',
        $date . ' 23:59:59',
    ])
    ->get();

2. Leading wildcard pada LIKE

Contoh seperti LIKE '%keyword%' sering tidak ramah index biasa. Jika pencarian teks bebas menjadi fitur utama, pertimbangkan perubahan pendekatan, misalnya full-text search atau mesin pencarian terpisah, bukan sekadar menambah index acak.

3. Offset pagination pada data besar

LIMIT 50 OFFSET 50000 bisa mahal karena database tetap harus melewati banyak baris. Jika halaman daftar bergerak makin lambat pada offset besar, pertimbangkan cursor-based pagination atau pagination berbasis nilai kolom terurut.

Trade-off: index berlebih bukan solusi gratis

Menambah index memang bisa mempercepat read, tetapi ada konsekuensi yang perlu dihitung:

  • INSERT/UPDATE/DELETE lebih mahal karena setiap index juga harus diperbarui.
  • Penyimpanan bertambah, terutama pada tabel besar.
  • Planner bisa bingung memilih index jika terlalu banyak index mirip.
  • Migration dan maintenance lebih berat pada sistem dengan traffic tinggi.

Karena itu, targetkan index pada query yang:

  • Paling sering dipanggil.
  • Paling mahal durasinya.
  • Benar-benar penting bagi pengalaman pengguna atau SLA sistem.

Kapan harus ubah pola query, bukan hanya menambah index

Ada kondisi ketika index yang benar pun tidak cukup:

  • Query laporan menggabungkan banyak tabel besar dan agregasi berat.
  • Pencarian teks bebas dilakukan dengan %keyword% di banyak kolom.
  • Endpoint daftar mencoba memuat terlalu banyak relasi sekaligus.
  • Pagination memakai offset sangat dalam.

Pada kondisi ini, solusi yang lebih tepat bisa berupa:

  • Memecah endpoint daftar dan endpoint detail.
  • Mengurangi kolom dan relasi yang dimuat di halaman list.
  • Memakai pre-aggregation atau tabel materialisasi untuk laporan.
  • Mengganti offset pagination dengan cursor pagination.
  • Memindahkan kebutuhan search ke mekanisme pencarian yang lebih sesuai.

Prinsipnya sederhana: kalau pola akses datanya memang mahal secara alami, index hanya membantu sampai batas tertentu.

Tips debugging saat hasil EXPLAIN belum membaik

  • Pastikan query yang diuji benar-benar sama dengan query dari aplikasi, termasuk filter opsional.
  • Cek apakah urutan kolom pada composite index sudah sesuai pola query dominan.
  • Bandingkan hasil EXPLAIN sebelum dan sesudah perubahan, jangan menebak dari feeling.
  • Periksa apakah sorting masih memicu filesort.
  • Lihat apakah query mengambil terlalu banyak kolom atau relasi yang tidak diperlukan.
  • Audit query lain dalam request yang sama; kadang bottleneck bukan hanya satu query.

Jangan menganggap “index sudah dibuat” berarti masalah selesai. Yang penting adalah apakah planner benar-benar memakai index tersebut dan jumlah baris yang dibaca turun signifikan.

Checklist audit performa query Laravel sebelum production

  1. Identifikasi endpoint yang melambat saat volume data naik.
  2. Rekam query aktual dari Laravel beserta durasinya.
  3. Cocokkan query mahal dengan slow query log database.
  4. Jalankan EXPLAIN pada query yang paling mahal dan paling sering.
  5. Cari tanda full table scan, filesort, dan rows examined yang terlalu besar.
  6. Pastikan index mengikuti pola WHERE, JOIN, dan ORDER BY yang nyata.
  7. Uji apakah index tunggal cukup atau perlu composite index.
  8. Hindari fungsi pada kolom terindeks jika masih bisa ditulis sebagai range query.
  9. Kurangi kolom yang dipilih dan relasi yang dimuat di endpoint daftar.
  10. Evaluasi apakah offset pagination perlu diganti.
  11. Ukur kembali setelah perubahan, jangan berhenti di level asumsi.
  12. Tinjau dampak index tambahan terhadap operasi write.

Penutup

Audit query lambat dengan EXPLAIN dan index yang tepat di Laravel bukan pekerjaan menebak-nebak. Mulailah dari query nyata yang dijalankan endpoint bermasalah, cocokkan dengan slow query log, lalu gunakan EXPLAIN untuk melihat apakah database melakukan scan besar, filesort, atau mengabaikan index yang ada. Dari sana, pilih perbaikan yang paling relevan: merapikan query, menambah index tunggal atau composite, atau mengubah pola akses data jika query memang tidak skalabel.

Jika dilakukan disiplin, pendekatan ini biasanya jauh lebih efektif daripada menambah cache terlalu cepat atau menyalahkan framework. Pada banyak kasus, bottleneck utamanya adalah satu query list yang sejak awal tidak dirancang untuk pertumbuhan data.