Laravel: Atasi Query JOIN Lambat dengan Index dan EXPLAIN

Jika halaman admin atau laporan di Laravel mulai lambat setelah jumlah data bertambah, penyebabnya sering bukan pada Laravel itu sendiri, melainkan pada query JOIN yang tidak didukung index dengan benar. Gejalanya biasanya muncul saat ada filter antar tabel, pengurutan kolom tertentu, dan pagination yang membuat database harus membaca jauh lebih banyak baris daripada yang dibutuhkan.

Solusi yang paling praktis adalah memeriksa query nyata yang dijalankan, lalu membaca hasil EXPLAIN untuk melihat apakah database melakukan full table scan, memakai temporary table, atau filesort. Setelah itu, tambahkan atau perbaiki index sesuai pola WHERE, JOIN, dan ORDER BY, bukan sekadar menambahkan index ke semua kolom.

Kasus Nyata: Halaman Admin Lambat Saat Data Membesar

Misalkan Anda punya halaman daftar pesanan pada admin. Halaman ini menampilkan data order, nama customer, status pembayaran, bisa difilter berdasarkan status customer, diurutkan berdasarkan tanggal order, lalu dipaginasi.

Skema sederhananya seperti ini:

customers
- id (PK)
- name
- status
- created_at

orders
- id (PK)
- customer_id (FK ke customers.id)
- order_number
- total_amount
- status
- created_at

order_items
- id (PK)
- order_id (FK ke orders.id)
- product_id
- qty
- price

Query laporan/admin yang umum:

SELECT o.id, o.order_number, o.total_amount, o.created_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.status = 'active'
  AND o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20 OFFSET 0;

Pada dataset kecil, query ini mungkin tetap cepat walau tanpa index yang tepat. Namun setelah data bertambah, database bisa terpaksa membaca banyak baris dari orders dan customers, lalu melakukan sort tambahan sebelum mengembalikan 20 baris pertama. Inilah titik ketika waktu respons naik drastis.

Versi Laravel: Eloquent dan Query Builder

Di Laravel, query di atas sering ditulis dengan Query Builder atau Eloquent. Contoh Query Builder:

$orders = DB::table('orders as o')
    ->join('customers as c', 'c.id', '=', 'o.customer_id')
    ->select('o.id', 'o.order_number', 'o.total_amount', 'o.created_at', 'c.name')
    ->where('c.status', 'active')
    ->where('o.status', 'paid')
    ->orderByDesc('o.created_at')
    ->paginate(20);

Kalau ingin melihat SQL mentahnya, Anda bisa memakai toSql() untuk struktur query, atau query log / Laravel Debugbar / Telescope untuk melihat query yang benar-benar dieksekusi. Untuk analisis performa, yang penting adalah SQL final dan pola akses datanya.

Catatan: masalah performa JOIN sering tertutup oleh gejala lain seperti N+1 query. Pastikan Anda membedakan keduanya. Jika hanya satu query tetapi lambat, fokuslah ke rencana eksekusi database. Jika banyak query kecil yang menumpuk, masalahnya bisa berbeda.

Cara Mendiagnosis Query JOIN Lambat dengan EXPLAIN

Setelah mendapatkan SQL yang lambat, jalankan EXPLAIN di database. Bentuk sederhananya:

EXPLAIN
SELECT o.id, o.order_number, o.total_amount, o.created_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.status = 'active'
  AND o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20 OFFSET 0;

Nama kolom hasil EXPLAIN bisa sedikit berbeda tergantung mesin database, tetapi untuk MySQL/MariaDB, beberapa bagian yang paling penting biasanya adalah:

table: tabel yang sedang diakses.
type: jenis akses data. Ini petunjuk kasar seberapa efisien pencarian dilakukan.
possible_keys: index yang mungkin dipakai.
key: index yang benar-benar dipakai.
rows: perkiraan jumlah baris yang harus dibaca.
Extra: informasi tambahan seperti Using where, Using temporary, atau Using filesort.

Apa yang Perlu Dicurigai

Beberapa tanda yang sering muncul saat query JOIN lambat:

Full table scan: biasanya terlihat dari type = ALL. Artinya database membaca hampir seluruh tabel.
Using filesort: pengurutan tidak bisa dipenuhi langsung dari index, sehingga database perlu langkah sort tambahan.
Using temporary: database membuat temporary table untuk menyusun hasil. Ini tidak selalu buruk, tetapi sering menjadi sinyal query bisa diperbaiki.
rows sangat besar: walau hasil akhir hanya 20 baris, database bisa membaca ratusan ribu atau jutaan baris.
key kosong atau tidak sesuai: index yang ada tidak dipilih, atau index yang dipilih tidak cocok dengan pola query.

Contoh Gejala Sebelum Perbaikan

Misalkan hasil EXPLAIN menunjukkan bahwa tabel orders dibaca dengan type = ALL dan Extra = Using where; Using filesort. Ini biasanya berarti:

Filter o.status = 'paid' belum terbantu index yang tepat.
Pengurutan ORDER BY o.created_at DESC tidak bisa langsung menggunakan urutan index.
Database mungkin menyaring data dulu, lalu mengurutkannya secara terpisah.

Jika tabel customers juga tidak punya index pada status, database bisa membaca banyak customer aktif dulu, lalu mencocokkannya ke orders, atau sebaliknya, tergantung optimizer.

Memilih Index yang Tepat untuk JOIN, Filter, dan Sort

Kesalahan umum adalah menambahkan index satu per satu tanpa memikirkan pola query. Padahal, untuk query JOIN lambat, urutan kolom dalam index komposit sering jauh lebih penting daripada sekadar keberadaan index.

Index Dasar yang Biasanya Wajib

Untuk skema di atas, paling tidak pastikan:

customers.id sudah menjadi primary key.
orders.customer_id memiliki index karena dipakai untuk JOIN.
Kolom filter seperti customers.status dan orders.status dipertimbangkan untuk index.
Kolom sorting seperti orders.created_at dipertimbangkan dalam index komposit jika memang sering dipakai bersama filter.

Contoh Index Sebelum dan Sesudah

Misalkan awalnya hanya ada primary key dan foreign key minimal:

-- sudah ada secara default atau dari migration dasar
PRIMARY KEY customers(id)
PRIMARY KEY orders(id)
INDEX orders_customer_id_index(customer_id)

Index seperti ini belum tentu cukup untuk query:

WHERE c.status = 'active'
  AND o.status = 'paid'
ORDER BY o.created_at DESC

Pendekatan yang lebih tepat biasanya adalah menambah index sesuai pola akses. Contoh:

CREATE INDEX customers_status_id_index ON customers(status, id);
CREATE INDEX orders_status_created_at_customer_id_index
ON orders(status, created_at, customer_id);

Mengapa urutan ini masuk akal?

customers(status, id): filter dimulai dari status, lalu id membantu proses join setelah subset customer aktif ditemukan.
orders(status, created_at, customer_id): query memfilter berdasarkan status, lalu mengurutkan berdasarkan created_at, dan tetap membutuhkan customer_id untuk join.

Namun, tidak ada satu urutan index yang selalu benar untuk semua query. Jika query Anda lebih sering mulai dari pencarian customer tertentu lalu masuk ke orders, urutannya bisa berbeda. Prinsipnya: susun index mengikuti pola seleksi paling penting dan paling sering.

Salah Urutan Kolom pada Index Komposit

Ini sumber masalah yang sangat umum. Misalnya Anda membuat index:

CREATE INDEX orders_created_at_status_customer_id_index
ON orders(created_at, status, customer_id);

Index ini terlihat mirip, tetapi bisa kurang efektif untuk query yang memulai seleksi dari status. Database tidak selalu bisa memanfaatkan index itu secara optimal untuk kondisi:

WHERE status = 'paid'
ORDER BY created_at DESC

Karena kolom paling kiri pada index adalah created_at, optimizer mungkin tidak mendapatkan jalur akses terbaik untuk filter status. Inilah alasan mengapa leftmost prefix pada index komposit sangat penting.

Praktiknya: jika pola query utama adalah WHERE status = ? ORDER BY created_at, maka index (status, created_at) umumnya lebih masuk akal daripada (created_at, status).

Contoh Migration Laravel untuk Menambah Index

Di Laravel, Anda bisa menambah index lewat migration. Contoh sederhana:

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('customers', function (Blueprint $table) {
            $table->index(['status', 'id'], 'customers_status_id_index');
        });

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

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

        Schema::table('orders', function (Blueprint $table) {
            $table->dropIndex('orders_status_created_at_customer_id_index');
        });
    }
};

Setelah migration dijalankan, ulangi EXPLAIN pada query yang sama. Tujuannya bukan mengejar output tertentu secara mutlak, tetapi memastikan:

jumlah baris yang diperkirakan dibaca turun,
index yang relevan benar-benar dipakai,
dan tanda seperti Using filesort atau Using temporary berkurang jika memang bisa dihindari.

Sebelum-Sesudah: Cara Menilai Perbaikan

Sebelum

JOIN bekerja, tetapi orders dibaca sangat banyak.
Sorting terjadi setelah penyaringan, bukan lewat urutan index.
Pagination halaman awal masih terasa wajar, tetapi halaman admin mulai timeout atau melambat saat filter dipakai.

Sesudah

Database bisa memulai dari subset data yang lebih kecil berkat index pada kolom filter.
JOIN ke tabel lain dilakukan dengan jumlah baris lebih sedikit.
ORDER BY lebih mungkin mengikuti urutan index, sehingga beban sort turun.
Pagination menjadi lebih stabil karena query tidak lagi memindai tabel terlalu luas.

Perlu diingat: tidak semua query bisa sepenuhnya bebas dari filesort atau temporary table. Kadang keduanya tetap muncul karena bentuk query memang menuntutnya. Yang penting adalah memastikan database tidak melakukan pekerjaan berlebihan yang sebenarnya bisa dihindari.

Optimasi Query Laravel Selain Menambah Index

Pilih Kolom Secukupnya

Hindari select * pada query laporan/admin yang kompleks. Ambil hanya kolom yang dipakai. Ini membantu mengurangi data yang dipindahkan dari database ke aplikasi, dan pada beberapa kasus memungkinkan akses index yang lebih efisien.

$orders = DB::table('orders as o')
    ->join('customers as c', 'c.id', '=', 'o.customer_id')
    ->select('o.id', 'o.order_number', 'o.total_amount', 'o.created_at', 'c.name')
    ->where('c.status', 'active')
    ->where('o.status', 'paid')
    ->orderByDesc('o.created_at')
    ->paginate(20);

Waspadai OFFSET Besar

Pagination berbasis LIMIT ... OFFSET ... bisa tetap mahal saat offset makin besar, walaupun index sudah lebih baik. Database tetap harus melewati banyak baris sebelum sampai ke halaman yang diminta.

Jika halaman admin sering membuka halaman jauh ke belakang, pertimbangkan cursor pagination atau strategi pagination berbasis penanda seperti created_at atau id. Ini bukan pengganti index, tetapi sering membantu pada dataset besar.

Jangan Paksa JOIN Jika Kebutuhan Berbeda

Untuk beberapa laporan, query tunggal yang kompleks bukan selalu pilihan terbaik. Kadang lebih efisien memakai ringkasan data, tabel agregasi, atau proses asinkron jika laporan sangat berat. Namun untuk halaman admin harian yang butuh filter dan pagination normal, index yang tepat biasanya adalah perbaikan pertama yang harus dilakukan.

Kesalahan Umum Saat Mengoptimalkan JOIN

Menambah index ke semua kolom tanpa melihat query utama. Ini justru menambah overhead write dan ukuran storage.
Mengabaikan urutan kolom pada index komposit. Index yang kolomnya benar tetapi urutannya salah sering tetap tidak membantu.
Hanya mengindex foreign key tetapi lupa kolom filter dan sort.
Mengoptimalkan berdasarkan asumsi tanpa menjalankan EXPLAIN.
Fokus pada query lokal yang datanya kecil, padahal masalah muncul di production saat distribusi datanya jauh berbeda.
Mengira filesort selalu buruk. Yang perlu dilihat adalah konteksnya: berapa banyak baris yang disortir dan apakah itu masih masuk akal.

Trade-off: Index Bukan Gratis

Menambah index memang sering mempercepat pembacaan, tetapi ada konsekuensinya:

Write overhead: operasi INSERT, UPDATE, dan DELETE bisa menjadi lebih mahal karena index juga harus diperbarui.
Ukuran storage: index tambahan memakan ruang disk dan memori cache.
Kompleksitas maintenance: terlalu banyak index menyulitkan audit dan bisa membuat optimizer memilih jalur yang tidak ideal.

Karena itu, targetkan index pada query yang benar-benar sering, kritis, dan mahal. Jangan menambah index hanya karena satu dugaan tanpa verifikasi.

Langkah Audit Query JOIN Lambat di Production

Berikut checklist praktis untuk audit query lambat di production tanpa bergantung pada klaim benchmark absolut:

Identifikasi query nyata: ambil dari slow query log, APM, Telescope, Debugbar di staging, atau log aplikasi.
Kelompokkan berdasarkan use case: halaman admin, laporan, export, dashboard.
Salin SQL final beserta parameter yang mewakili kasus lambat.
Jalankan EXPLAIN dan catat tabel mana yang dibaca paling besar.
Periksa type, key, rows, dan Extra: cari tanda ALL, Using filesort, Using temporary, atau index yang tidak terpakai.
Cocokkan dengan pola query: JOIN, WHERE, ORDER BY, dan pagination.
Tinjau index yang ada: apakah hanya foreign key, atau sudah mendukung filter dan sort juga.
Perbaiki urutan index komposit sesuai kolom yang paling menentukan seleksi query.
Uji ulang dengan EXPLAIN setelah migration index diterapkan.
Monitor dampaknya: lihat apakah latensi membaik dan apakah ada efek samping pada write-heavy workload.
Audit offset besar: jika pagination tetap mahal, evaluasi strategi pagination.
Hapus index yang tidak berguna jika setelah audit terbukti tidak dipakai dan hanya menambah beban.

Penutup

Untuk mengatasi query JOIN lambat dengan index dan EXPLAIN di Laravel, fokuslah pada pola query yang benar-benar berjalan di halaman admin atau laporan: filter lintas tabel, sorting, dan pagination. EXPLAIN membantu Anda melihat apakah database sedang melakukan pekerjaan yang mahal seperti full table scan, filesort, atau temporary table.

Perbaikan yang paling sering berdampak adalah menyusun index komposit dengan urutan kolom yang tepat, bukan sekadar menambahkan index secara acak. Mulailah dari query yang paling lambat dan paling sering dipakai, verifikasi dengan EXPLAIN, lalu ukur hasilnya di lingkungan yang datanya mendekati production.

Quick Search

shopping_cart Keranjang