Audit SQL untuk Daftar Kandidat: Index dan Pagination Anti Lambat

Audit SQL untuk daftar kandidat biasanya dimulai dari satu gejala yang sama: halaman pelamar terasa normal saat data masih ribuan, lalu tiba-tiba lambat ketika recruiter menambah filter, mengurutkan berdasarkan waktu update, dan membuka halaman ke-50 atau ke-200. Masalahnya jarang ada pada satu query saja, melainkan gabungan antara pola akses data, index yang tidak cocok, dan pagination berbasis OFFSET yang makin mahal saat data tumbuh.

Untuk sistem rekrutmen internal, bottleneck ini sangat nyata. Daftar kandidat umumnya memuat kombinasi filter seperti status lamaran, stage, recruiter owner, sumber kandidat, lokasi, skor, serta pencarian nama atau email. Jika SQL tidak diaudit dengan benar, database akan menghabiskan waktu untuk memindai, menyortir, lalu membuang ribuan baris hanya untuk menampilkan 20 data berikutnya. Artikel ini fokus pada masalah operasional tersebut, bukan pada tren atau berita, meski konteks keluhan recruiter di industri memang relevan sebagai pengingat bahwa UX yang buruk sering berakar pada desain query yang buruk.

Jika Anda pernah melihat halaman daftar kandidat cepat di page 1 tetapi lambat di page 200, hampir pasti OFFSET pagination dan index yang tidak sesuai ikut berperan.

Gejala Umum Query Lambat pada Halaman Daftar Kandidat

Beberapa pola yang sering muncul pada sistem ATS, HRIS, atau portal rekrutmen internal:

Response time naik seiring nomor halaman: page 1 cepat, page 100 lambat.
Filter tertentu membuat query tiba-tiba berat: misalnya status + stage masih cepat, tetapi ditambah sort by updated_at atau search text langsung melambat.
CPU database tinggi saat jam kerja recruiter: terutama saat banyak user membuka daftar yang sama dengan filter sedikit berbeda.
Count total hasil lebih mahal dari pengambilan datanya: API menampilkan jumlah total kandidat untuk pagination, tetapi query COUNT(*) ikut memindai banyak baris.
Sort menghasilkan temporary sort: database tidak bisa memakai index untuk urutan yang diminta sehingga harus menyortir hasil di memori atau disk.

Pada level produk, gejalanya terlihat sederhana: daftar kandidat “lemot”. Pada level database, penyebabnya sering berupa kombinasi ini:

Index hanya dibuat pada kolom tunggal, padahal query memfilter beberapa kolom sekaligus.
Urutan kolom pada composite index tidak cocok dengan pola WHERE dan ORDER BY.
Penggunaan OFFSET besar.
Join ke tabel besar dilakukan terlalu awal, sebelum kandidat diseleksi.
Pencarian teks dengan LIKE '%keyword%' pada kolom besar tanpa strategi khusus.

Contoh Query yang Memburuk Saat Data Tumbuh

Misalkan ada tabel utama seperti ini:

candidates(id, full_name, email, location, created_at, updated_at, deleted_at)
applications(id, candidate_id, job_id, stage, status, score, assigned_recruiter_id, applied_at, updated_at)

Query daftar kandidat yang umum terlihat seperti berikut:

SELECT
  c.id,
  c.full_name,
  c.email,
  a.stage,
  a.status,
  a.score,
  a.updated_at
FROM applications a
JOIN candidates c ON c.id = a.candidate_id
WHERE a.status = 'active'
  AND a.stage IN ('screening', 'interview')
  AND a.assigned_recruiter_id = 42
ORDER BY a.updated_at DESC, a.id DESC
LIMIT 20 OFFSET 2000;

Query ini tampak wajar. Masalah muncul ketika jumlah applications menjadi ratusan ribu atau jutaan. Database harus:

Mencari semua baris yang cocok dengan status, stage, dan assigned_recruiter_id.
Menyusunnya menurut updated_at DESC, id DESC.
Melewati 2000 baris pertama karena OFFSET 2000.
Baru mengembalikan 20 baris.

Jika index tidak mendukung kombinasi filter dan urutan tersebut, engine bisa melakukan scan besar lalu sort mahal. Bahkan jika filter sudah cukup selektif, OFFSET tetap memaksa database membaca lebih banyak baris dari yang akhirnya dikirim ke aplikasi.

Masalah Tambahan: Query Count

Sering ada query kedua seperti ini untuk menampilkan total hasil:

SELECT COUNT(*)
FROM applications a
WHERE a.status = 'active'
  AND a.stage IN ('screening', 'interview')
  AND a.assigned_recruiter_id = 42;

Secara fungsional ini masuk akal. Namun pada tabel besar, COUNT(*) dengan filter yang kompleks tetap punya biaya nyata. Jika setiap request halaman selalu memanggil query data plus query count, beban database naik dua kali lipat.

Kenapa OFFSET Makin Mahal

OFFSET pagination mudah dipakai, tetapi biayanya naik seiring nomor halaman. Intinya, database tidak benar-benar “lompat” ke baris ke-2001 tanpa pekerjaan. Ia tetap perlu menemukan dan mengurutkan baris sebelumnya agar tahu mana 20 baris berikutnya yang valid.

Secara praktis:

LIMIT 20 OFFSET 0 berarti ambil 20 pertama.
LIMIT 20 OFFSET 2000 berarti proses setidaknya 2020 kandidat teratas untuk kemudian membuang 2000.

Kalau query butuh sort yang tidak didukung index, biaya ini bertambah karena engine harus menyusun himpunan hasil yang lebih besar dulu. Itulah sebabnya daftar kandidat sering terasa cepat di awal lalu drop drastis pada halaman yang dalam.

OFFSET tetap berguna untuk kebutuhan sederhana, dataset kecil, atau admin tool internal dengan volume rendah. Tetapi untuk daftar kandidat yang sering diakses recruiter dan terus tumbuh, keyset pagination biasanya lebih stabil.

Kapan Composite Index Diperlukan

Index tunggal seperti INDEX(status), INDEX(stage), dan INDEX(updated_at) belum tentu cukup. Optimizer database tidak selalu bisa menggabungkan banyak index tunggal menjadi rencana yang efisien, terutama jika query harus sekaligus memfilter dan mengurutkan.

Untuk query seperti:

WHERE a.status = 'active'
  AND a.assigned_recruiter_id = 42
ORDER BY a.updated_at DESC, a.id DESC

Anda sering membutuhkan composite index yang mengikuti pola akses nyata, misalnya:

(assigned_recruiter_id, status, updated_at, id)

atau pada kasus lain:

(status, assigned_recruiter_id, updated_at, id)

Pemilihan urutan kolom tidak bisa asal. Beberapa aturan praktis:

Letakkan kolom filter yang paling konsisten dipakai pada awal index.
Kolom untuk ORDER BY biasanya diletakkan setelah kolom filter yang membantu menyempitkan hasil.
Tambahkan kolom tie-breaker seperti id bila urutan harus stabil.
Jangan membuat composite index sangat panjang tanpa alasan; setiap index menambah biaya write dan storage.

Contoh yang Baik dan yang Sering Salah

Sering salah:

INDEX(updated_at)
INDEX(status)
INDEX(assigned_recruiter_id)

Ini tampak lengkap, tetapi belum tentu optimal untuk query daftar kandidat dengan filter gabungan dan sort.

Lebih tepat untuk pola query tertentu:

INDEX(assigned_recruiter_id, status, updated_at, id)

Kenapa ini membantu? Karena database bisa mencari rentang baris milik recruiter tertentu dengan status tertentu, lalu membaca hasil dalam urutan updated_at, id tanpa sort tambahan atau dengan sort yang jauh lebih kecil.

Trade-off: Write Cost vs Read Performance

Setiap index baru bukan gratis. Konsekuensinya:

INSERT lebih mahal karena index harus diperbarui.
UPDATE pada kolom yang di-index juga lebih mahal.
Storage bertambah.
Terlalu banyak index dapat membingungkan audit karena banyak yang overlap.

Pada sistem rekrutmen, trade-off ini sering tetap masuk akal karena pola kerjanya dominan read-heavy di halaman daftar. Recruiter berkali-kali membuka list, memfilter, dan mengurutkan kandidat. Namun jangan membuat semua kemungkinan kombinasi filter menjadi index. Fokus pada query yang paling sering dipakai dan paling mahal.

Cara Membaca EXPLAIN Secara Dasar

Langkah audit paling berguna adalah melihat rencana eksekusi dengan EXPLAIN. Nama kolom output berbeda antar database, tetapi prinsip bacanya mirip. Anda ingin tahu:

Apakah query memakai index atau scan besar.
Berapa banyak baris yang diperkirakan dibaca.
Apakah ada sort tambahan.
Apakah join dilakukan dengan urutan yang masuk akal.

Contoh:

EXPLAIN
SELECT
  c.id,
  c.full_name,
  a.stage,
  a.status,
  a.updated_at
FROM applications a
JOIN candidates c ON c.id = a.candidate_id
WHERE a.status = 'active'
  AND a.assigned_recruiter_id = 42
ORDER BY a.updated_at DESC, a.id DESC
LIMIT 20;

Apa yang Dicari dari EXPLAIN

Rows tinggi: tanda bahwa engine memperkirakan membaca terlalu banyak baris.
Full scan: biasanya indikasi index tidak cocok atau filter tidak selektif.
Sort/temporary: artinya urutan hasil tidak bisa langsung diambil dari index.
Join terlalu dini ke tabel lain: kadang lebih efisien jika tabel utama difilter dan dipaginate dulu, baru join detail secukupnya.

Jangan membaca EXPLAIN sebagai angka mutlak. Fokus utamanya adalah membandingkan sebelum dan sesudah perubahan index atau query. Jika setelah menambah composite index perkiraan baris turun drastis dan langkah sort hilang, itu sinyal kuat perbaikan.

Kesalahan Umum Saat Menilai EXPLAIN

Menganggap “pakai index” pasti cepat. Index yang dipakai belum tentu index yang tepat.
Hanya melihat satu query tanpa melihat pola keseluruhan endpoint, termasuk query count.
Tidak menguji filter yang paling buruk. Query bisa cepat untuk recruiter A tetapi lambat untuk recruiter B dengan distribusi data berbeda.

Langkah Migrasi ke Keyset Pagination

Jika bottleneck utama adalah halaman dalam yang lambat, migrasi dari OFFSET pagination ke keyset pagination biasanya memberi dampak besar. Ide dasarnya: alih-alih mengatakan “lewati 2000 baris”, kita mengatakan “ambil data setelah baris terakhir yang tadi ditampilkan”.

Misalnya urutan daftar adalah:

ORDER BY a.updated_at DESC, a.id DESC

Maka page berikutnya bisa memakai kursor dari baris terakhir page sebelumnya:

SELECT
  c.id,
  c.full_name,
  a.stage,
  a.status,
  a.updated_at,
  a.id AS application_id
FROM applications a
JOIN candidates c ON c.id = a.candidate_id
WHERE a.status = 'active'
  AND a.assigned_recruiter_id = 42
  AND (
    a.updated_at < :cursor_updated_at
    OR (a.updated_at = :cursor_updated_at AND a.id < :cursor_id)
  )
ORDER BY a.updated_at DESC, a.id DESC
LIMIT 20;

Dengan index yang sesuai, database tidak perlu membaca dan membuang ribuan baris sebelumnya. Ia cukup melanjutkan dari posisi kursor.

Syarat Keyset Pagination yang Baik

Urutan harus deterministik. Jangan hanya ORDER BY updated_at jika banyak baris punya nilai sama; tambahkan id sebagai tie-breaker.
Kolom urutan harus stabil dan terindeks.
API perlu mengirim cursor, bukan nomor halaman semata.

Trade-off Keyset Pagination

Kelebihan: jauh lebih efisien untuk halaman dalam, performa lebih konsisten.
Kekurangan: tidak nyaman untuk “lompat ke halaman 57” karena konsepnya berbasis posisi, bukan nomor halaman absolut.
Kekurangan lain: implementasi UI dan API sedikit lebih kompleks, terutama jika ada banyak kombinasi sort.

Untuk daftar kandidat operasional harian, trade-off ini umumnya layak. Recruiter biasanya butuh “lanjut ke hasil berikutnya”, bukan benar-benar meloncat ke page angka tertentu.

Pola Implementasi yang Lebih Aman

1. Paginate dulu, join secukupnya

Jika tabel utama besar dan join mahal, ambil ID kandidat atau aplikasi yang sudah terfilter dan terurut dulu, baru join ke detail. Contoh:

SELECT a.id, a.candidate_id, a.updated_at
FROM applications a
WHERE a.status = 'active'
  AND a.assigned_recruiter_id = 42
  AND (
    a.updated_at < :cursor_updated_at
    OR (a.updated_at = :cursor_updated_at AND a.id < :cursor_id)
  )
ORDER BY a.updated_at DESC, a.id DESC
LIMIT 20;

Lalu ambil detail kandidat berdasarkan ID hasil tersebut. Ini sering mengurangi beban join pada path pagination utama.

2. Hindari SELECT *

Ambil hanya kolom yang dibutuhkan daftar. Halaman list tidak perlu memuat semua field profil kandidat, riwayat lengkap, catatan internal, dan lampiran sekaligus. Baris yang lebih sempit berarti I/O lebih kecil dan memori lebih hemat.

3. Pisahkan pencarian teks dari filter struktural

Pencarian seperti LIKE '%andi%' pada nama atau email bisa merusak performa, terutama jika digabung dengan filter dan sort berat. Jika pencarian teks adalah kebutuhan utama, pertimbangkan strategi khusus seperti full-text search atau mesin pencarian terpisah. Jangan berharap index B-tree biasa akan optimal untuk pola wildcard di depan.

4. Audit query count

Jika total hasil tidak benar-benar wajib akurat setiap saat, Anda bisa:

menunda query count,
menampilkan “lebih dari N hasil”,
atau menghitung total secara terpisah untuk filter tertentu.

Pendekatan ini bergantung pada kebutuhan produk, tetapi penting untuk menyadari bahwa count sering menjadi biaya tersembunyi.

Kesalahan Index yang Paling Sering Terjadi

Membuat terlalu banyak index tunggal tetapi tidak punya composite index untuk query nyata.
Urutan kolom index salah, sehingga filter atau sort utama tidak terbantu.
Meng-index kolom berselektivitas rendah tanpa konteks, misalnya status yang nilainya hanya beberapa macam, lalu berharap query langsung cepat.
Index overlap, contohnya ada beberapa index yang hampir sama tetapi jarang dipakai.
Lupa index pada tie-breaker pagination, sehingga keyset pagination tetap tidak optimal.
Tidak memperhatikan soft delete. Jika tabel memakai deleted_at dan hampir semua query memfilter data aktif, desain index harus mempertimbangkan itu.

Checklist Audit SQL untuk Tabel Kandidat yang Besar

Daftar 5-10 query paling sering dan paling mahal pada endpoint daftar kandidat.
Catat kombinasi filter nyata: status, stage, recruiter, lokasi, score, updated_at, job_id, dan soft delete.
Periksa pola ORDER BY. Apakah selalu sama atau banyak variasi?
Jalankan EXPLAIN untuk query normal dan query terburuk.
Cek apakah sort menggunakan index atau membuat temporary sort.
Identifikasi query dengan OFFSET besar.
Rancang composite index berdasarkan query dominan, bukan berdasarkan intuisi umum.
Uji dampak ke write path: insert aplikasi baru, update stage, update status.
Audit query count dan pertimbangkan apakah total hasil harus selalu real-time.
Evaluasi keyset pagination untuk daftar yang sering diakses dan memiliki page dalam.
Pastikan sort deterministik dengan tie-breaker seperti id.
Ukur ulang setelah perubahan; jangan berhenti di level teori.

Penutup

Masalah performa pada daftar kandidat hampir selalu bisa ditelusuri ke pola SQL yang spesifik: filter yang berkembang, sort yang tidak didukung index, dan pagination berbasis OFFSET yang makin mahal. Solusi yang paling efektif biasanya bukan “tambah resource database”, melainkan audit query yang disiplin: pahami akses data nyata, buat composite index yang sesuai, baca EXPLAIN, lalu migrasi ke keyset pagination ketika halaman dalam mulai menyakitkan.

Jika Anda mengelola sistem rekrutmen internal dan mendengar keluhan recruiter bahwa daftar kandidat lambat, anggap itu sinyal untuk mengaudit SQL, bukan sekadar masalah UI. Pengalaman pengguna yang buruk sering berawal dari keputusan index dan pagination yang terlihat kecil, tetapi efeknya membesar seiring pertumbuhan data.

Referensi konteks industri yang relevan untuk memahami tekanan operasional recruiter dapat dilihat di tulisan: The Lion, The Witch and the Audacity of Recruiter oleh hauleth.dev. Gunakan itu sebagai konteks masalah, bukan sebagai dasar keputusan teknis database.

Quick Search

shopping_cart Keranjang