Pada banyak aplikasi Laravel, ada endpoint yang hanya melakukan pengecekan sederhana: apakah sebuah relasi ada, apakah user memiliki akses ke resource tertentu, atau apakah kombinasi dua kolom memang valid. Secara kode, query seperti ini biasanya terlihat aman karena memakai exists, whereExists, atau ->exists() dari Eloquent/Query Builder. Namun di tabel besar, query yang tampaknya ringan ini tetap bisa lambat.

Masalah utamanya sering bukan pada Laravel, melainkan pada index database yang kurang tepat. Banyak developer sudah menambahkan index, tetapi urutan kolom salah, cardinality kurang baik, atau index tidak cocok dengan pola filter query. Akibatnya, database tetap harus membaca terlalu banyak baris sebelum bisa menjawab satu pertanyaan sederhana: data itu ada atau tidak.

Artikel ini membahas kasus tersebut secara praktis, dengan fokus pada endpoint validasi atau pengecekan relasi yang memakai exists/whereExists, lalu menunjukkan bagaimana composite index, urutan kolom, cardinality, dan EXPLAIN membantu menemukan akar masalahnya.

Kenapa query exists tetap bisa lambat?

Secara konsep, exists memang efisien karena database tidak perlu mengambil seluruh data. Ia hanya perlu menemukan satu baris yang cocok, lalu berhenti. Tetapi efisiensi ini bergantung pada kemampuan database menemukan kandidat baris dengan cepat. Jika pencarian awalnya buruk, keuntungan exists menjadi kecil.

Contoh umum pada endpoint validasi relasi:

$exists = DB::table('project_user')
    ->where('project_id', $projectId)
    ->where('user_id', $userId)
    ->exists();

Atau dengan whereExists:

$allowed = DB::table('projects')
    ->where('projects.id', $projectId)
    ->whereExists(function ($query) use ($userId) {
        $query->select(DB::raw(1))
            ->from('project_user')
            ->whereColumn('project_user.project_id', 'projects.id')
            ->where('project_user.user_id', $userId);
    })
    ->exists();

Di atas kertas, dua query ini sederhana. Tetapi jika tabel project_user berisi jutaan baris dan index yang tersedia hanya index tunggal pada project_id atau user_id, database mungkin tetap harus memeriksa banyak row. Pada endpoint yang dipanggil sangat sering, biaya ini cepat menjadi bottleneck.

Catatan: Query exists yang lambat sering muncul pada tabel pivot, tabel membership, tabel ACL, tabel mapping tenant-user, dan tabel validasi relasi lain yang volumenya besar tetapi pola query-nya berulang.

Masalah paling umum: index ada, tapi tidak sesuai pola query

Index tunggal belum tentu cukup

Banyak tabel besar memiliki index seperti ini:

INDEX(project_id)
INDEX(user_id)

Index tersebut berguna untuk beberapa query, tetapi belum tentu optimal untuk query:

WHERE project_id = ? AND user_id = ?

Pada kasus ini, database sering lebih diuntungkan oleh composite index:

INDEX(project_id, user_id)

Kenapa? Karena composite index menyimpan urutan data berdasarkan kombinasi kolom tersebut. Database bisa langsung mempersempit pencarian ke pasangan project_id dan user_id, bukan mencari lewat salah satu kolom dulu lalu memfilter sisanya.

Urutan kolom dalam composite index sangat penting

Composite index bukan sekadar daftar kolom. Urutan kolom menentukan kapan index itu efektif. Misalnya Anda punya query yang paling sering berbentuk:

WHERE tenant_id = ? AND email = ?

Maka index yang lazim cocok adalah:

INDEX(tenant_id, email)

Bukan:

INDEX(email, tenant_id)

Secara teori keduanya terlihat mirip, tetapi database memanfaatkan composite index dari kolom paling kiri. Ini sering disebut leftmost prefix. Jika mayoritas query selalu memfilter tenant_id terlebih dahulu, maka menaruh tenant_id di depan biasanya lebih masuk akal.

Contoh lain pada endpoint validasi:

  • WHERE user_id = ? AND role_id = ? cocok dengan INDEX(user_id, role_id)
  • WHERE company_id = ? AND status = ? AND deleted_at IS NULL bisa lebih cocok dengan INDEX(company_id, status, deleted_at)
  • WHERE order_id = ? AND product_id = ? cocok dengan INDEX(order_id, product_id)

Jika urutannya tidak sesuai pola query dominan, database bisa memakai index secara parsial atau bahkan mengabaikannya.

Memahami cardinality: kenapa kolom tertentu sebaiknya di depan?

Cardinality secara sederhana adalah seberapa beragam nilai pada sebuah kolom. Kolom dengan cardinality tinggi memiliki banyak nilai unik atau hampir unik. Kolom seperti ini biasanya lebih selektif untuk memfilter data.

Misalnya:

  • status hanya punya nilai active, inactive, pending → cardinality rendah
  • user_id punya jutaan nilai berbeda → cardinality tinggi

Dalam banyak kasus, menaruh kolom yang lebih selektif di awal index membantu database mengurangi jumlah row yang harus diperiksa. Tetapi ini bukan aturan mutlak. Anda tetap harus melihat pola query nyata.

Contoh trade-off:

  • Jika query hampir selalu diawali tenant_id lalu user_id, maka INDEX(tenant_id, user_id) sering lebih tepat, walaupun user_id mungkin lebih unik secara global.
  • Jika query kadang hanya memakai tenant_id, composite index yang menaruh tenant_id di depan memberi manfaat lebih luas.

Kesalahan umum adalah membuat index berdasarkan intuisi semata, misalnya menaruh kolom paling unik di depan tanpa mempertimbangkan query aplikasi. Hasilnya, cardinality memang bagus di atas kertas, tetapi index kurang terpakai untuk endpoint yang sebenarnya lambat.

Membaca EXPLAIN untuk memastikan dugaan

Jangan menebak. Gunakan EXPLAIN untuk melihat bagaimana database menjalankan query.

Contoh query SQL dari Laravel:

SELECT EXISTS(
    SELECT 1
    FROM project_user
    WHERE project_id = 123
      AND user_id = 456
) AS exists_result;

Lalu jalankan:

EXPLAIN SELECT EXISTS(
    SELECT 1
    FROM project_user
    WHERE project_id = 123
      AND user_id = 456
) AS exists_result;

Hal yang perlu diperhatikan saat membaca hasil EXPLAIN:

  • key: index apa yang dipakai. Jika kosong atau tidak sesuai harapan, kemungkinan index Anda tidak cocok.
  • possible_keys: kandidat index yang tersedia. Jika index yang Anda buat tidak muncul, ada kemungkinan struktur query tidak bisa memanfaatkannya.
  • type: indikator cara akses. Nilai seperti ref, const, atau eq_ref umumnya lebih baik daripada ALL yang berarti full scan.
  • rows: perkiraan jumlah baris yang akan diperiksa. Untuk endpoint validasi, angka ini idealnya kecil.
  • Extra: lihat apakah ada indikasi filter tambahan yang membuat query tidak efisien.

Jika Anda melihat type = ALL atau jumlah rows sangat besar, maka meskipun query memakai exists, database tetap bekerja berat.

Contoh interpretasi sederhana

Misalnya hasil EXPLAIN menunjukkan:

  • key = project_user_user_id_index
  • rows = 150000

Ini bisa berarti database memilih index pada user_id, lalu masih harus memeriksa banyak baris untuk menemukan project_id yang sesuai. Jika Anda mengganti menjadi composite index (project_id, user_id) atau (user_id, project_id) sesuai pola akses dominan, angka rows bisa turun jauh.

Tips debugging: ambil SQL mentah dari Laravel dengan toSql(), cek binding, lalu jalankan EXPLAIN langsung di database. Pastikan yang diuji adalah query yang benar-benar dieksekusi aplikasi.

Contoh migrasi Laravel untuk menambahkan index yang sesuai

Misalkan ada tabel pivot project_user dengan pola query paling sering:

  • cek apakah user termasuk dalam project: WHERE project_id = ? AND user_id = ?
  • ambil semua user dalam satu project: WHERE project_id = ?

Maka index yang masuk akal adalah (project_id, user_id).

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('project_user', function (Blueprint $table) {
            $table->index(['project_id', 'user_id'], 'project_user_project_id_user_id_idx');
        });
    }

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

Jika kombinasi tersebut seharusnya unik, pertimbangkan unique index:

Schema::table('project_user', function (Blueprint $table) {
    $table->unique(['project_id', 'user_id'], 'project_user_project_id_user_id_unique');
});

Ini memberi dua manfaat: menjaga integritas data dan membantu query pencarian kombinasi kolom secara sangat efisien.

Contoh lain untuk pola multi-tenant

Jika endpoint validasi sering memeriksa apakah sebuah email ada di tenant tertentu:

$exists = DB::table('users')
    ->where('tenant_id', $tenantId)
    ->where('email', $email)
    ->exists();

Maka migrasi yang relevan:

Schema::table('users', function (Blueprint $table) {
    $table->index(['tenant_id', 'email'], 'users_tenant_id_email_idx');
});

Jika aplikasinya memang mewajibkan email unik per tenant, gunakan unique agar lebih tepat secara model data.

Kesalahan yang sering terjadi pada endpoint validasi relasi

Mengandalkan index terpisah untuk query gabungan

Dua index tunggal tidak selalu setara dengan satu composite index. Untuk query yang hampir selalu memfilter dua kolom sekaligus, composite index biasanya lebih efektif.

Menaruh kolom ber-cardinality rendah di depan tanpa alasan

Misalnya membuat INDEX(status, user_id) padahal query dominan adalah WHERE user_id = ? AND status = 'active' dan nilai status sangat sedikit. Hasilnya, bagian awal index kurang selektif.

Tidak meninjau pola query dominan

Index harus mengikuti query yang paling sering dan paling mahal, bukan sekadar semua kemungkinan query. Menambahkan terlalu banyak index juga ada biayanya: insert, update, dan delete menjadi lebih mahal.

Mengabaikan soft delete atau filter tambahan

Jika query selalu menyertakan deleted_at IS NULL, pertimbangkan apakah kolom tersebut perlu masuk composite index. Ini bergantung pada distribusi data dan pola query aktual.

Langkah praktis saat menemukan exists lambat

  1. Identifikasi query persis yang dieksekusi oleh endpoint Laravel.
  2. Lihat pola WHERE yang paling sering muncul.
  3. Cek index yang ada, termasuk urutan kolomnya.
  4. Jalankan EXPLAIN dan perhatikan key, type, dan rows.
  5. Tambahkan atau ubah composite index agar sesuai pola query dominan.
  6. Uji ulang dengan EXPLAIN dan pengukuran aplikasi nyata.
  7. Evaluasi trade-off write performance jika tabel sangat sering diubah.

Poin pentingnya adalah ini: exists bukan jaminan cepat. Ia hanya memberi peluang untuk berhenti lebih awal jika database bisa menemukan kandidat row dengan efisien. Pada tabel besar, efisiensi itu sangat ditentukan oleh composite index yang tepat, urutan kolom yang sesuai, dan pemahaman terhadap cardinality.

Di Laravel, perbaikannya sering sederhana dari sisi kode karena query builder sudah cukup baik. Yang perlu diperbaiki justru desain index di database. Jadi ketika endpoint validasi relasi tetap lambat meskipun sudah memakai exists atau whereExists, jangan buru-buru menyalahkan framework. Lihat dulu apakah database benar-benar diberi jalan tercepat untuk menjawab query tersebut.