Query lambat pada tabel besar sering bukan karena datanya terlalu banyak, tetapi karena cara kita meminta data memaksa database memindai terlalu rapat. Pola paling umum adalah OFFSET pagination pada endpoint daftar data: halaman awal terasa cepat, tetapi makin dalam halamannya, makin mahal biaya scan, sort, dan discard baris yang sebenarnya tidak dipakai.

Solusi praktisnya adalah mengurangi kepadatan scan: gunakan keyset pagination agar database melompat dari posisi terakhir yang sudah diketahui, tambahkan filter yang selektif, dan sediakan index komposit yang mengikuti urutan WHERE dan ORDER BY. Dengan pendekatan ini, Anda mengurangi peluang full scan, filesort, dan pembacaan baris yang tidak perlu.

Mengapa OFFSET Makin Lambat Saat Tabel Membesar

Query seperti ini tampak sederhana:

SELECT id, status, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 100000;

Masalahnya, database tidak bisa langsung "melompat" ke baris ke-100001 tanpa konteks yang cukup. Dalam banyak kasus, engine tetap harus:

  • menemukan kandidat baris sesuai filter,
  • mengurutkan hasil jika urutan tidak cocok dengan index,
  • melewati offset yang besar,
  • baru mengambil 50 baris yang diminta.

Secara praktis, OFFSET besar berarti banyak kerja yang dibuang. Ini sering terlihat sebagai:

  • Rows examined jauh lebih besar dari rows returned,
  • Using filesort pada hasil EXPLAIN,
  • full table scan atau range scan yang terlalu lebar,
  • latensi yang naik tajam pada halaman-halaman akhir.

Pola ini sangat umum pada feed, daftar transaksi, audit log, order history, dan data time-series lain yang terus bertambah.

Masalah Nyata: Full Scan, Filesort, dan Scan yang Terlalu Padat

Contoh query yang mudah melambat

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

Jika tidak ada index yang sesuai, database bisa memilih scan yang mahal. Bahkan jika ada index pada status saja atau created_at saja, itu belum tentu cukup untuk mengoptimalkan gabungan filter dan urutan.

Secara umum, Anda ingin index yang membantu dua hal sekaligus:

  1. menyempitkan kandidat lewat filter yang selektif,
  2. menjaga urutan baca agar hasil sudah sesuai ORDER BY tanpa sort tambahan.

Contoh gejala pada EXPLAIN

Bentuk keluaran EXPLAIN berbeda antar database, tetapi gejalanya mirip. Contoh interpretasi yang perlu dicurigai:

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

Hal yang perlu diperhatikan:

  • type/access method terlalu lemah, misalnya mendekati full scan,
  • rows atau estimasi baris sangat besar,
  • indikasi Using filesort,
  • index yang dipakai tidak sesuai urutan filter dan sort.

Jika query mengembalikan 50 baris tetapi harus memeriksa puluhan ribu atau lebih, bottleneck Anda biasanya ada pada pola akses, bukan pada ukuran hasil.

Keyset Pagination: Mengurangi Scan dengan Posisi Terakhir

Keyset pagination mengganti konsep “halaman ke-N” menjadi “lanjut dari item terakhir yang sudah diterima”. Ini cocok untuk daftar yang diurutkan stabil, misalnya berdasarkan created_at DESC, id DESC.

Sebelum: OFFSET pagination

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

Sesudah: keyset pagination

Misalnya baris terakhir pada halaman sebelumnya adalah:

  • created_at = '2024-12-01 10:15:00'
  • id = 987654

Maka halaman berikutnya diambil dengan:

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

Mengapa ini lebih cepat?

  • Database tidak perlu menghitung dan membuang ribuan baris lewat OFFSET.
  • Posisi lanjut sudah jelas berdasarkan nilai kolom yang diurutkan.
  • Jika index cocok, engine bisa melakukan range scan yang jauh lebih sempit.

Mengapa perlu id sebagai tie-breaker

created_at saja sering tidak unik. Jika banyak row memiliki timestamp yang sama, pagination bisa:

  • mengulang data,
  • melewatkan data,
  • menghasilkan urutan yang tidak stabil.

Karena itu, pola yang aman adalah memakai urutan deterministik seperti ORDER BY created_at DESC, id DESC, lalu menyimpan kedua nilai itu sebagai cursor.

Memilih Index Komposit yang Tepat

Aturan praktis: index harus mengikuti pola query yang paling sering dan paling mahal, terutama urutan filter dan sort.

Kapan memakai index (created_at, id)

Pakai ini jika query Anda umumnya:

  • tidak memfilter kolom lain yang lebih selektif,
  • mengurutkan berdasarkan created_at, id,
  • mengambil data terbaru secara berurutan.

Contoh:

SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 50;

Index komposit yang relevan:

CREATE INDEX idx_orders_created_id ON orders (created_at, id);

Dengan index ini, database lebih mudah membaca data sesuai urutan yang diminta tanpa sort tambahan.

Kapan memakai index (status, created_at, id)

Pakai ini jika query sering diawali filter pada status lalu diurutkan berdasarkan waktu dan id.

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

Index yang lebih cocok:

CREATE INDEX idx_orders_status_created_id ON orders (status, created_at, id);

Alasannya:

  • status membatasi subset data lebih dulu,
  • dalam subset itu, urutan created_at, id tetap bisa dimanfaatkan,
  • query untuk status tertentu tidak perlu menyisir seluruh timeline order.

Jangan asal menambah index

Index komposit bukan sekadar menumpuk kolom. Urutan kolom sangat penting. Index (created_at, status, id) tidak selalu membantu query WHERE status = ? ORDER BY created_at, id dengan efektif, karena kolom paling depan menentukan bagaimana index ditelusuri.

Selain itu, selectivity perlu dipertimbangkan. Jika status hanya punya sedikit nilai dan distribusinya sangat timpang, efektivitasnya bergantung pada pola data nyata. Karena itu, validasi selalu dengan EXPLAIN dan metrik produksi, bukan asumsi semata.

Before/After: Perbaikan Query dan Interpretasi EXPLAIN

Before: OFFSET + index kurang tepat

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

Kemungkinan gejala:

  • banyak rows examined,
  • filesort,
  • latensi naik seiring nomor halaman.

After: keyset + index komposit

SELECT id, status, created_at, total
FROM orders
WHERE status = 'paid'
  AND (
    created_at < :last_created_at
    OR (created_at = :last_created_at AND id < :last_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;
CREATE INDEX idx_orders_status_created_id ON orders (status, created_at, id);

Target hasil EXPLAIN setelah perbaikan:

  • akses lewat index yang relevan,
  • estimasi baris yang lebih kecil,
  • tidak ada indikasi sort besar yang sebenarnya bisa dihindari,
  • latensi lebih stabil antar halaman.

Anda tidak harus mengejar “sempurna” di semua query. Fokuslah pada query yang paling sering dipanggil atau paling mahal total waktunya.

Filter Selektif untuk Mengurangi Kepadatan Scan

Inspirasi “density reduction” di SQL adalah ini: jangan paksa database membaca area data yang terlalu padat jika sebenarnya Anda hanya perlu subset kecil.

Praktiknya:

  • tambahkan filter bisnis yang memang relevan, bukan sekadar pagination,
  • batasi rentang waktu jika konteksnya memang hanya data terbaru,
  • hindari query daftar global jika UI sebenarnya menampilkan subset tertentu,
  • gunakan keyset agar scan dimulai dari posisi yang sempit, bukan dari awal.

Contoh lebih selektif:

SELECT id, status, created_at, total
FROM orders
WHERE status = 'paid'
  AND created_at >= :start_date
  AND (
    created_at < :last_created_at
    OR (created_at = :last_created_at AND id < :last_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 50;

Jika kebutuhan bisnis memang hanya 30 atau 90 hari terakhir, jangan desain query seolah-olah semua riwayat harus dipindai tiap kali.

Panduan Memilih: Offset, Keyset, atau Cursor Pagination

1. Offset pagination

Cocok jika:

  • dataset kecil,
  • halaman dangkal,
  • pengguna benar-benar butuh lompat ke halaman tertentu,
  • latensi masih terkontrol.

Kelemahan:

  • makin mahal di halaman dalam,
  • rentan inkonsistensi saat data baru masuk di antara request,
  • sering memicu scan dan sort yang membengkak.

2. Keyset pagination

Cocok jika:

  • urutan data jelas dan stabil,
  • endpoint lebih sering “next/previous” daripada “go to page 847”,
  • tabel terus bertambah,
  • fokus utama adalah performa dan konsistensi urutan.

Kelemahan:

  • lebih sulit untuk lompat ke halaman arbitrer,
  • perlu desain cursor berdasarkan kolom sort,
  • butuh perhatian pada kolom tie-breaker.

3. Cursor pagination

Secara praktik, banyak API menyebut implementasi keyset sebagai cursor pagination. Cursor biasanya berupa token terenkode yang menyimpan posisi terakhir, misalnya pasangan created_at dan id.

Cocok jika:

  • API publik atau internal ingin antarmuka yang rapi,
  • Anda ingin menyembunyikan detail keyset dari klien,
  • Anda butuh validasi dan serialisasi posisi halaman.

Kelemahan:

  • lebih kompleks daripada offset biasa,
  • perlu kompatibilitas versi jika format cursor berubah.

Jika performa daftar data menjadi masalah utama, pilihan default yang aman untuk tabel besar biasanya adalah keyset/cursor pagination, bukan OFFSET.

Anti-Pattern Umum yang Membuat Query Tetap Lambat

  • Mengurutkan tanpa index yang sesuai. Misalnya ada filter pada status tetapi index hanya pada created_at.
  • Memakai OFFSET sangat besar pada endpoint yang dipanggil terus-menerus.
  • Mengandalkan kolom non-unik untuk pagination tanpa tie-breaker seperti id.
  • Menambahkan terlalu banyak index tanpa melihat biaya write.
  • Memilih urutan kolom index yang salah. Index komposit sensitif terhadap urutan.
  • SELECT * pada daftar data yang sebenarnya hanya butuh beberapa kolom.
  • Filter opsional yang tidak selektif tetapi tetap dipaksa masuk ke satu query generik untuk semua kebutuhan.

Trade-off: Index Mempercepat Read, tetapi Menambah Biaya Write

Index tambahan hampir selalu membantu sebagian query baca, tetapi ada biaya yang perlu dipahami:

  • write amplification: setiap INSERT, UPDATE, atau DELETE juga harus memperbarui index,
  • penggunaan storage bertambah,
  • maintenance lebih kompleks,
  • beban write-heavy bisa meningkat jika index terlalu banyak atau terlalu lebar.

Karena itu, jangan menambah index hanya karena satu query lambat di staging. Pastikan query itu memang penting secara frekuensi atau total biaya di produksi.

Prinsip yang baik:

  • optimalkan query yang paling mahal atau paling sering,
  • hapus index yang redundan jika terbukti tidak terpakai,
  • evaluasi dampak ke beban tulis sebelum dan sesudah deployment.

Langkah Migrasi Aman di Produksi

1. Identifikasi query target

Kumpulkan query yang lambat dari log database, APM, slow query log, atau metrik aplikasi. Pilih satu atau dua pola query dengan dampak terbesar.

2. Ambil baseline

Sebelum mengubah apa pun, catat:

  • latensi p50/p95 jika tersedia,
  • frekuensi panggilan,
  • rows examined vs rows returned,
  • hasil EXPLAIN.

3. Tambahkan index secara hati-hati

Buat index baru dengan prosedur yang meminimalkan lock sesuai kemampuan database Anda. Pada tabel besar, penambahan index bisa memakan waktu dan I/O yang signifikan, jadi jadwalkan dengan benar dan uji pada lingkungan mirip produksi.

4. Uji query baru dengan EXPLAIN

Pastikan optimizer benar-benar memakai index yang Anda rancang. Jangan berhenti di tahap “index sudah dibuat”. Validasi akses aktualnya.

5. Rilis keyset pagination di level aplikasi

Implementasi aman biasanya bertahap:

  1. tambahkan dukungan cursor/keyset pada endpoint baru atau parameter baru,
  2. biarkan klien lama tetap memakai offset sementara waktu,
  3. pantau error, duplikasi item, dan anomali urutan,
  4. migrasikan klien utama,
  5. hapus offset dalam jika sudah tidak diperlukan.

6. Pastikan kompatibilitas hasil

Bandingkan hasil halaman awal antara offset dan keyset untuk memastikan urutan dan isi data sesuai ekspektasi. Untuk data yang berubah cepat, perbedaan kecil antar request bisa normal, tetapi urutan harus tetap deterministik.

7. Monitor dampak read dan write

Setelah deploy, pantau:

  • latensi query target,
  • pemakaian CPU dan I/O database,
  • throughput write,
  • ukuran index dan pertumbuhan storage.

Checklist Diagnosis Query Lambat

  • Apakah query memakai OFFSET besar?
  • Apakah ORDER BY cocok dengan index yang tersedia?
  • Apakah ada filesort yang seharusnya bisa dihindari?
  • Apakah query memfilter subset data yang cukup selektif?
  • Apakah urutan sort memakai tie-breaker yang stabil seperti id?
  • Apakah index komposit mengikuti pola WHERE + ORDER BY?
  • Apakah rows examined terlalu besar dibanding hasil?
  • Apakah aplikasi meminta terlalu banyak kolom?
  • Apakah query ini cukup sering atau cukup mahal untuk layak dioptimalkan?
  • Apakah penambahan index akan berdampak signifikan ke beban write?

Contoh Implementasi Cursor di API

Contoh sederhana: server mengembalikan cursor dari baris terakhir.

{
  "data": [
    { "id": 987700, "created_at": "2024-12-01T10:20:00Z", "status": "paid" },
    { "id": 987654, "created_at": "2024-12-01T10:15:00Z", "status": "paid" }
  ],
  "next_cursor": "2024-12-01T10:15:00Z|987654"
}

Request berikutnya mengirim cursor itu, lalu backend mengubahnya menjadi syarat keyset:

WHERE status = 'paid'
  AND (
    created_at < :last_created_at
    OR (created_at = :last_created_at AND id < :last_id)
  )

Di produksi, cursor biasanya di-encode agar formatnya lebih stabil dan tidak terlalu bergantung pada implementasi klien.

Kesimpulan

Mengatasi query lambat dengan keyset pagination dan index komposit berarti mengubah cara database menelusuri data, bukan sekadar berharap hardware lebih kuat.

Jika tabel terus membesar, OFFSET akan makin mahal karena banyak baris harus dilewati. Dengan keyset pagination, filter yang selektif, dan index komposit seperti (created_at, id) atau (status, created_at, id), Anda mengurangi kepadatan scan, menghindari sort yang tidak perlu, dan menjaga latensi tetap stabil.

Mulailah dari query yang paling mahal, validasi dengan EXPLAIN, ukur sebelum-sesudah, dan migrasikan bertahap di produksi. Optimasi yang benar biasanya sederhana: selaraskan pola query dengan cara database menyimpan dan membaca index.