Masalah utama yang ditangani adalah bagaimana cara mengidentifikasi dan memperbaiki bottleneck SQL saat menggunakan pagination pada tabel besar di aplikasi Laravel. Dalam dua paragraf pertama ini sudah dijawab bahwa kombinasi profiling query, analisis EXPLAIN, serta strategi indexing dan cursor pagination menjadi kunci agar pagination tetap responsif saat data tumbuh.

1. Mengawali Diagnosa Bottleneck SQL

Langkah pertama adalah mengetahui query mana yang paling lambat. Laravel menyediakan DB::listen untuk meng-capture semua query selama request. Di environment pengembangan atau staging, tambahkan listener di AppServiceProvider atau middleware khusus:

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

Catat query yang berulang dan lambat, terutama yang dieksekusi saat pagination. Gunakan profiling tambahan seperti Laravel Debugbar atau Clockwork agar melihat jumlah query per halaman.

2. Menelaah Eksekusi Menggunakan EXPLAIN

Setelah menemukan query bermasalah (misalnya mencari teks tertentu dengan LIKE dan memanggil orderBy pada kolom non-indexed), jalankan EXPLAIN via database client atau DB::select('EXPLAIN ...') untuk memahami rencana eksekusi.

Perhatikan:

  • type: hindari ALL (full table scan), idealnya ref atau range pada filter indeks.
  • possible_keys: daftarkan index yang tersedia, pastikan query menggunakan key yang diharapkan.
  • rows: nilai rendah menandakan filtering efektif, tinggi berarti evaluasi row banyak.

Contoh query:

$posts = Post::where('status', 'published')
    ->where('title', 'like', "%{$keyword}%")
    ->orderBy('published_at', 'desc')
    ->paginate(20);

Jika EXPLAIN menunjukkan rows besar dan type = ALL, maka perlu meninjau index.

3. Memilih Index yang Tepat untuk Filter dan Sorting

Untuk query di atas, index tunggal pada status saja tidak cukup karena penambahan title LIKE dan orderBy published_at. Idealnya:

  • Single index pada kolom dengan filter equality yang sering dipakai (status).
  • Composite index ketika filter dan sorting sering digunakan bersama: contoh (status, published_at), karena query memfilter status lalu mengurutkan published_at.

Tambahkan index menggunakan migration:

Schema::table('posts', function (Blueprint $table) {
    $table->index(['status', 'published_at']);
});

Jika query mengandung pencarian teks (fulltext), bila perlu manfaatkan index FULLTEXT atau driver pencarian eksternal (Elasticsearch) untuk kolom besar. Pastikan juga menyesuaikan query builder agar memanfaatkan index, contohnya orderBy pada kolom indeks.

Perhatikan trade-off:

  • Index mempercepat baca tetapi menambah overhead saat insert/update/delete.
  • Composite index harus disusun sesuai urutan filter/sorting yang paling sering dipakai.

4. Cursor Pagination untuk Konsistensi Performansi

Paginasi standar menggunakan OFFSET menjadi lambat pada halaman tinggi karena harus melewati banyak row. Cursor pagination (menggunakan pointer terakhir) menghindari overhead OFFSET dan lebih konsisten pada dataset besar.

Laravel menyediakan cursorPaginate(). Contoh penerapan:

$posts = Post::where('status', 'published')
    ->orderBy('published_at', 'desc')
    ->cursorPaginate(20);

Dengan cara ini, query hanya membatasi row berdasarkan nilai terakhir (misalnya published_at dan id sebagai tie breaker), sehingga tetap cepat meski data bertambah. Pastikan index mendukung kolom yang dipakai sebagai cursor.

5. Tips Pemeliharaan Index dan Peringatan Umum

Tips pemeliharaan:

  • Jalankan ANALYZE TABLE atau ekivalennya setelah perubahan data besar agar statistik index akurat.
  • Hapus index yang jarang digunakan; terlalu banyak index memperlambat operasi tulis.
  • Monitor lock dan blocking saat index di-rebuild. Lakukan pemeliharaan di waktu low traffic.

Peringatan umum:

  • Query builder lambda yang membuat kondisi dinamis dapat membuat perencanaan index sulit; pastikan kondisi tetap konsisten.
  • Cursor pagination tidak cocok jika pengguna perlu melompat ke halaman tertentu secara acak.
  • Pastikan statistik database tidak kadaluarsa karena bisa menyebabkan planner memilih index yang salah.

Dengan kombinasi profiling, EXPLAIN, index yang pas, dan cursor pagination saat perlu, bottleneck SQL pada pagination Laravel dapat diidentifikasi dan diatasi secara bertahap.