Kalau SQL lambat, refleks yang paling umum adalah menambah index. Masalahnya, itu sering hanya mengobati gejala. Banyak query backend lambat justru berasal dari hal yang lebih mendasar: mengambil kolom terlalu banyak, memakai OFFSET besar, membungkus kolom terindeks dengan fungsi, menggunakan pola pencarian yang tidak bisa memanfaatkan index, atau menulis join/filter yang membuat optimizer memilih rencana eksekusi buruk.

Pendekatan yang lebih aman adalah berpikir why vanilla: sebelum menambah solusi yang lebih kompleks, pastikan query dasarnya sudah rapi. Perbaiki select list, filter, join, urutan operasi, dan pagination lebih dulu. Setelah itu baru putuskan apakah index tambahan benar-benar diperlukan. Dengan cara ini, Anda mengurangi risiko menambah biaya write, storage, dan kompleksitas maintenance tanpa menyelesaikan akar masalah.

Gejala vs akar masalah: jangan salah diagnosis

Query lambat adalah gejala. Akar masalahnya bisa sangat berbeda:

  • Over-fetching: mengambil semua kolom padahal yang dipakai hanya sedikit.
  • OFFSET besar: database tetap harus melewati banyak baris sebelum mengembalikan halaman berikutnya.
  • Fungsi pada kolom terindeks: misalnya LOWER(email) atau DATE(created_at), yang sering membuat index biasa tidak terpakai.
  • Wildcard prefix: LIKE '%term%' umumnya sulit memakai index B-Tree biasa.
  • Join/filter tidak selektif: urutan dan bentuk kondisi membuat optimizer membaca terlalu banyak data.
  • Index ada, tapi tidak terpakai: karena query tidak sesuai dengan bentuk index, statistik kurang baik, atau hasil scan tabel penuh memang dinilai lebih murah oleh optimizer.

Kalau langsung menambah index tanpa membedakan gejala dan akar masalah, Anda bisa berakhir dengan banyak index yang mahal saat INSERT, UPDATE, dan DELETE, tetapi query tetap lambat.

Langkah pertama: ukur dengan EXPLAIN atau EXPLAIN ANALYZE

Sebelum mengubah query, lihat dulu bagaimana database mengeksekusinya. Gunakan EXPLAIN untuk memeriksa rencana eksekusi, dan jika tersedia gunakan EXPLAIN ANALYZE untuk membandingkan estimasi dengan eksekusi nyata.

Apa yang perlu diperhatikan

  • Apakah terjadi full table scan? Ini belum tentu salah, tetapi patut dicek jika tabel besar.
  • Baris yang diperkirakan vs baris aktual: selisih besar bisa menandakan statistik atau bentuk query bermasalah.
  • Operator sort, hash, temporary: bisa mahal jika dataset besar.
  • Join order: apakah database memulai dari tabel yang paling selektif?
  • Predicate pushdown: apakah filter diterapkan sedini mungkin?
  • Index scan vs index seek/range scan: tidak semua pemakaian index sama efisiennya.

Catatan: Nama operator dan detail output berbeda antar database. Fokuslah pada konsep: berapa banyak baris dibaca, kapan filter diterapkan, apakah sort besar terjadi, dan apakah index benar-benar membantu.

Contoh investigasi sederhana

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE DATE(created_at) = '2025-01-15'
ORDER BY created_at DESC
LIMIT 50;

Query di atas terlihat sederhana, tetapi DATE(created_at) sering membuat index pada created_at tidak bisa dipakai secara efektif. Jika rencana eksekusi menunjukkan pembacaan banyak baris lalu filter di akhir, akar masalahnya bukan belum ada index baru, melainkan bentuk predikatnya.

Kasus nyata: query rewrite sering lebih efektif daripada index baru

1. Over-fetching: jangan pakai SELECT * kalau tidak perlu

Mengambil semua kolom meningkatkan I/O, penggunaan memori, dan biaya transfer data dari database ke aplikasi. Ini makin terasa jika ada kolom besar seperti JSON, TEXT, atau metadata yang sebenarnya tidak dipakai.

Sebelum:

SELECT *
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100;

Sesudah:

SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100;

Mengapa lebih baik: database membaca dan mengirim data lebih sedikit. Pada beberapa kasus, query juga bisa memanfaatkan covering index jika kolom yang dipilih sesuai dengan index yang ada.

Kesalahan umum: menambah index lebar untuk mendukung SELECT *. Ini sering tidak efisien karena index menjadi besar dan mahal dipelihara.

2. Fungsi pada kolom terindeks: ubah predikat, bukan langsung tambah index

Jika Anda punya index pada created_at, query berikut sering membuat index biasa tidak efektif:

Sebelum:

SELECT id, user_id, total
FROM orders
WHERE DATE(created_at) = '2025-01-15';

Sesudah:

SELECT id, user_id, total
FROM orders
WHERE created_at >= '2025-01-15 00:00:00'
  AND created_at <  '2025-01-16 00:00:00';

Mengapa lebih baik: bentuk rentang seperti ini memungkinkan database memakai index range scan pada created_at. Anda mengubah query agar sesuai dengan struktur data yang sudah ada.

Contoh lain:

Sebelum:

SELECT id, email
FROM users
WHERE LOWER(email) = '[email protected]';

Pertimbangan: jika kebutuhan pencarian case-insensitive memang wajib dan sering dipakai, solusi bisa berupa normalisasi data saat write, kolom turunan, atau index fungsional jika DB Anda mendukung. Tetapi jangan menganggap itu langkah pertama sebelum memastikan kebutuhan pencarian dan bentuk data sudah tepat.

3. Wildcard prefix: index B-Tree biasa punya batas

Pola berikut sering membuat index biasa sulit digunakan:

SELECT id, title
FROM articles
WHERE title LIKE '%sql%';

Mengapa lambat: wildcard di awal berarti database tidak bisa menentukan titik awal range dalam index B-Tree biasa. Akibatnya, ia cenderung memeriksa banyak baris.

Yang bisa diperbaiki tanpa solusi kompleks:

  • Jika kebutuhan sebenarnya adalah pencarian awalan, ubah ke LIKE 'sql%'.
  • Jika pencarian substring memang wajib, akui bahwa ini bukan kasus ideal untuk index biasa. Jangan memaksa dengan menambah index yang tetap tidak akan dipakai.

Sebelum:

SELECT id, title
FROM articles
WHERE title LIKE '%laporan%';

Sesudah, jika bisnisnya cukup dengan prefix:

SELECT id, title
FROM articles
WHERE title LIKE 'laporan%';

Catatan: Jika kebutuhan pencarian adalah substring bebas, typo tolerance, atau ranking relevansi, biasanya Anda sudah masuk ke domain full-text search atau search engine. Itu solusi valid, tetapi bukan langkah pertama jika masalahnya masih bisa diselesaikan dengan memperjelas kebutuhan pencarian.

4. OFFSET besar: terlihat sederhana, tetapi makin mahal di halaman belakang

Pagination dengan LIMIT ... OFFSET ... mudah dipakai, tetapi biaya bacanya meningkat seiring nomor halaman. Database tetap harus melewati baris-baris sebelumnya, walaupun tidak semuanya dikembalikan ke aplikasi.

Sebelum:

SELECT id, created_at, total
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 50000;

Index mungkin membantu urutan dan filter, tetapi OFFSET 50000 tetap berarti ada banyak baris yang harus dilewati.

Alternatif: keyset pagination

SELECT id, created_at, total
FROM orders
WHERE status = 'paid'
  AND (created_at, id) < ('2025-01-15 10:30:00', 123456)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Mengapa lebih baik: database bisa melanjutkan dari posisi terakhir, bukan menghitung ulang dari awal setiap kali. Ini biasanya jauh lebih stabil untuk dataset besar.

Kapan beralih ke keyset pagination:

  • Data besar dan halaman belakang terasa jauh lebih lambat.
  • Urutan data jelas dan stabil, misalnya berdasarkan created_at, id.
  • UI tidak harus mendukung lompat bebas ke halaman 937.

Trade-off: keyset pagination lebih efisien, tetapi tidak cocok untuk semua pengalaman pengguna. Jika produk benar-benar perlu lompat ke nomor halaman arbitrer, Anda harus mempertimbangkan kompromi performa atau desain UI yang berbeda.

5. Index ada, tapi tidak terpakai: cek bentuk join dan filter

Sering ada anggapan bahwa selama index sudah dibuat, query pasti cepat. Padahal optimizer hanya akan memakai index jika biayanya dianggap lebih murah daripada alternatif lain.

Sebelum:

SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND c.country = 'ID'
ORDER BY o.created_at DESC
LIMIT 100;

Query ini bisa lambat bukan karena belum ada index baru, tetapi karena:

  • Filter yang paling selektif tidak diterapkan lebih awal.
  • Kolom yang dipakai untuk join, filter, dan sort tidak selaras.
  • Terlalu banyak kolom diambil sebelum hasil dipersempit.

Rewrite yang mungkin lebih efektif:

SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND c.country = 'ID'
ORDER BY o.created_at DESC
LIMIT 100;

Secara teks mungkin sama, tetapi investigasinya adalah memastikan:

  • Apakah filter status dan country cukup selektif?
  • Apakah urutan ORDER BY memaksa sort besar?
  • Apakah mengambil c.name sejak awal memang perlu?

Dalam praktik, rewrite bisa berupa memecah query menjadi langkah yang lebih selektif, memakai subquery untuk membatasi kandidat lebih awal, atau mengubah bentuk predicate agar index yang ada bisa digunakan.

Contoh membatasi kandidat lebih dulu:

SELECT o.id, o.total, c.name
FROM (
  SELECT id, customer_id, total, created_at
  FROM orders
  WHERE status = 'paid'
  ORDER BY created_at DESC
  LIMIT 500
) o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'ID'
ORDER BY o.created_at DESC
LIMIT 100;

Pola ini tidak selalu lebih baik; hasilnya tergantung distribusi data dan optimizer. Namun ini contoh bahwa query rewrite kadang lebih efektif daripada sekadar menambah index baru pada semua kolom yang terlihat relevan.

Kapan query rewrite lebih efektif daripada menambah index?

Secara praktis, prioritaskan rewrite jika Anda menemukan hal-hal berikut:

  • Predikat tidak sargable: kolom dibungkus fungsi atau ekspresi sehingga index biasa sulit dipakai.
  • Select list berlebihan: query lambat karena over-fetching, bukan karena pencarian barisnya.
  • Pagination berbasis OFFSET besar: akar masalah ada pada cara membaca halaman, bukan kurangnya index.
  • Pola LIKE tidak cocok untuk B-Tree: index tambahan yang sama jenisnya tidak akan membantu.
  • Join membaca terlalu banyak baris: perlu mengurangi kandidat lebih awal, bukan menambah index secara membabi buta.

Index baru lebih masuk akal jika:

  • Predikat sudah rapi dan sesuai dengan akses data yang nyata.
  • Rencana eksekusi menunjukkan bottleneck pada pencarian/range scan yang memang bisa dibantu index.
  • Query tersebut penting, sering dieksekusi, dan dampaknya nyata pada latensi aplikasi.
  • Anda menerima biaya tambahan pada operasi write.

Biaya nyata dari index tambahan

Index bukan gratis. Sebelum membuat index baru, pertimbangkan efek sampingnya:

  • Write lebih mahal: setiap INSERT, UPDATE, dan DELETE harus memperbarui index terkait.
  • Storage bertambah: index besar pada tabel besar bisa signifikan.
  • Vacuum/rebuild/maintenance: tergantung DB, index menambah beban operasional.
  • Optimizer complexity: terlalu banyak pilihan index bisa membuat pemilihan rencana lebih rumit.
  • Index yang jarang dipakai: biaya terus ada, manfaat belum tentu.

Karena itu, index tambahan sebaiknya menjadi keputusan yang didukung data: query penting, pola akses stabil, dan rencana eksekusinya memang menunjukkan potensi perbaikan.

Checklist investigasi saat SQL lambat

  1. Ambil query aktual dari log aplikasi atau slow query log, bukan perkiraan dari ingatan.
  2. Jalankan EXPLAIN/EXPLAIN ANALYZE dan catat jumlah baris yang dibaca, operasi sort, dan join order.
  3. Cek select list: apakah ada SELECT * atau kolom besar yang tidak dipakai?
  4. Cek filter: adakah fungsi pada kolom terindeks, implicit conversion, atau kondisi yang tidak selektif?
  5. Cek pola LIKE: apakah ada wildcard di awal?
  6. Cek pagination: apakah memakai OFFSET besar?
  7. Cek join: apakah tabel dipersempit sedini mungkin?
  8. Cek apakah index yang ada sudah cukup tetapi tidak terpakai karena bentuk query.
  9. Rewrite query lebih dulu dan ukur ulang.
  10. Baru evaluasi index baru jika akar masalahnya memang akses data yang cocok dibantu index.

Contoh alur perbaikan yang realistis

Misalkan endpoint daftar pesanan lambat. Query awal:

SELECT *
FROM orders
WHERE DATE(created_at) = '2025-01-15'
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 20000;

Masalah yang terlihat:

  • SELECT * menyebabkan over-fetching.
  • DATE(created_at) berpotensi menonaktifkan pemakaian index biasa pada created_at.
  • OFFSET 20000 mahal.

Perbaikan bertahap:

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

Untuk halaman berikutnya, lanjutkan dengan keyset:

SELECT id, user_id, total, created_at
FROM orders
WHERE created_at >= '2025-01-15 00:00:00'
  AND created_at <  '2025-01-16 00:00:00'
  AND status = 'paid'
  AND (created_at, id) < ('2025-01-15 19:22:10', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Di titik ini, Anda sudah menghilangkan tiga sumber masalah besar tanpa menambah kompleksitas berlebihan. Jika setelah itu query masih lambat dan EXPLAIN ANALYZE menunjukkan bottleneck pada akses data tertentu, barulah index tambahan layak dipertimbangkan.

Penutup

Saat SQL lambat, jangan langsung menganggap jawabannya adalah index baru. Mulailah dari hal yang paling dasar dan paling sering salah: kolom yang diambil, bentuk filter, cara join, pola pencarian, dan pagination. Pendekatan ini sering memberi hasil lebih cepat, lebih murah, dan lebih mudah dipelihara dibanding menumpuk index.

Urutannya sederhana: ukur, diagnosis, rewrite, ukur lagi, lalu baru tambah index jika memang perlu. Dengan begitu, Anda memperbaiki akar masalah, bukan hanya menutupi gejalanya.