Offset Pagination Makin Lambat? Bedah Index dan Query Plan SQL

Offset pagination terasa sederhana: pakai LIMIT ... OFFSET ..., lalu tampilkan halaman 1, 2, 3, dan seterusnya. Masalahnya, semakin besar nilai OFFSET, semakin banyak pekerjaan yang tetap harus dilakukan database sebelum menemukan baris yang benar-benar perlu dikirim ke aplikasi.

Di tabel kecil, biaya ini sering tidak terasa. Di tabel besar, gejalanya mulai jelas: query halaman awal cepat, tetapi halaman-halaman dalam jadi lambat. Penyebabnya biasanya bukan satu hal saja, melainkan kombinasi full scan, sort/filesort, row lookup, dan biaya membuang banyak baris akibat OFFSET besar. Solusinya bukan sekadar “tambah index”, tetapi memastikan index, filter, dan urutan sort selaras dengan pola query—atau mengganti strategi pagination.

Mengapa offset pagination melambat saat data tumbuh?

Query offset pagination umumnya terlihat seperti ini:

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

Secara logika, aplikasi hanya minta 20 baris. Tetapi di sisi database, query seperti ini sering berarti:

Mencari semua baris yang cocok dengan WHERE status = 'published'.
Menyusunnya sesuai ORDER BY created_at DESC.
Melewati 100.000 baris pertama.
Baru mengembalikan 20 baris berikutnya.

Jadi, masalah utamanya bukan pada LIMIT 20, melainkan pada pekerjaan sebelum 20 baris itu ditemukan. Nilai OFFSET besar membuat database tetap membaca banyak data yang akhirnya dibuang.

1. Full scan atau scan yang terlalu lebar

Jika filter tidak didukung indeks yang tepat, optimizer bisa memilih membaca banyak bagian tabel atau bahkan seluruh tabel. Ini membuat offset pagination memburuk karena scan besar ditambah beban sort dan skip.

Contoh buruk:

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

Jika hanya ada indeks pada created_at, tetapi distribusi data untuk status tidak mendukung, database mungkin tetap perlu membaca banyak baris untuk memfilter status yang cocok. Jika hanya ada indeks pada status, database mungkin masih harus menyortir hasilnya karena urutan created_at DESC tidak ditopang indeks yang sama.

2. Filesort atau sort tambahan

ORDER BY yang tidak sesuai dengan urutan indeks sering memaksa database melakukan sort eksplisit. Nama implementasinya berbeda-beda antar vendor, tetapi secara konsep sama: hasil perlu disusun ulang di luar urutan natural index.

Sort ini makin mahal jika:

jumlah baris kandidat besar,
kolom yang diambil banyak,
sort tidak bisa dilakukan dari index,
offset besar sehingga banyak hasil tetap harus disusun sebelum dibuang.

3. Row lookup tambahan

Walaupun database bisa menemukan kandidat lewat index, belum tentu semua kolom yang diminta ada di index tersebut. Jika query mengambil banyak kolom di luar index, engine harus melakukan lookup ke row utama berkali-kali.

Misalnya:

SELECT id, created_at, title, body, author_name
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

Kalau index hanya berisi (status, created_at), database mungkin masih perlu membaca row penuh untuk title, body, dan author_name. Untuk halaman dalam, jumlah lookup bisa besar karena banyak kandidat dibaca sebelum 20 baris akhir dipilih.

4. Biaya OFFSET besar itu nyata

OFFSET 100000 tidak berarti database bisa “lompat” langsung ke baris ke-100001 dalam semua kasus. Ia tetap perlu menavigasi data sesuai filter dan urutan. Semakin kompleks query, semakin mahal biaya membuang baris yang tidak dikembalikan.

Catatan penting: masalah offset pagination bukan semata karena jumlah total row di tabel, tetapi karena kombinasi WHERE, ORDER BY, kolom yang dipilih, dan kedalaman halaman yang diminta.

Kapan indeks tunggal tidak cukup?

Kesalahan umum adalah membuat index terpisah untuk setiap kolom lalu berharap optimizer akan selalu menggabungkannya secara efisien. Dalam praktiknya, query dengan WHERE dan ORDER BY sering membutuhkan index komposit yang mengikuti pola akses data.

Contoh query:

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

Anggap Anda punya dua index tunggal:

INDEX(status)
INDEX(created_at)

Ini belum tentu cukup, karena database perlu dua hal sekaligus:

memfilter berdasarkan status,
menghasilkan urutan berdasarkan created_at tanpa sort mahal.

Jika filter dilakukan dari index status, hasilnya mungkin masih perlu disortir menurut created_at. Jika urutan diambil dari index created_at, database mungkin harus memeriksa banyak row untuk menemukan yang status = 'published'. Dua index tunggal tidak otomatis memberi jalur akses terbaik.

Mengapa index komposit lebih tepat?

Untuk query di atas, index komposit seperti berikut biasanya lebih selaras:

INDEX(status, created_at, id)

Idenya:

status membantu menyaring data lebih awal.
created_at menjaga urutan yang dibutuhkan ORDER BY.
id membantu menjaga urutan stabil jika ada nilai created_at yang sama dan berguna untuk pagination berbasis cursor nanti.

Urutan kolom dalam index komposit penting. Secara umum, letakkan kolom filter yang dipakai konsisten, lalu kolom sort. Tetapi tidak ada aturan tunggal yang selalu benar untuk semua query. Anda perlu melihat pola query nyata, selektivitas filter, dan hasil EXPLAIN.

Contoh query sebelum dan sesudah perbaikan index

Sebelum: index tidak selaras

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20 OFFSET 50000;

Kondisi index:

INDEX(status)
INDEX(created_at)

Kemungkinan konsekuensinya:

engine membaca banyak row yang cocok dengan status, lalu menyortirnya,
atau mengikuti index created_at lalu terus mengecek status satu per satu,
lalu tetap membuang 50.000 hasil pertama.

Sesudah: index komposit lebih sesuai

CREATE INDEX idx_posts_status_created_id
ON posts (status, created_at, id);

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 50000;

Perubahan penting:

ORDER BY dibuat deterministik dengan id sebagai tie-breaker.
Index lebih selaras dengan kombinasi filter dan urutan.
Optimizer punya peluang lebih besar untuk menghindari sort mahal.

Namun perlu ditekankan: ini mengurangi sebagian biaya, tetapi tidak menghilangkan biaya OFFSET besar. Database masih perlu melewati banyak entri sebelum sampai ke halaman yang diminta.

Memperkecil row lookup dengan memilih kolom seperlunya

Jika halaman daftar hanya menampilkan tiga kolom, jangan ambil isi artikel lengkap.

Kurang efisien:

SELECT *
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 50000;

Lebih efisien:

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 50000;

Mengambil kolom seperlunya bisa mengurangi I/O, row lookup, ukuran hasil, dan beban jaringan.

Cara membaca EXPLAIN secara umum

Setiap database punya format EXPLAIN yang berbeda, tetapi ada beberapa sinyal umum yang perlu Anda cari saat mendiagnosis offset pagination lambat:

1. Access path: scan atau index range?

Lihat apakah query:

membaca seluruh tabel,
melakukan scan index yang sangat lebar,
atau bisa memakai range scan yang sempit dan terarah.

Jika rencana menunjukkan pembacaan baris sangat banyak dibanding hasil akhir yang hanya 20 row, itu tanda pola akses belum efisien.

2. Apakah ada sort eksplisit?

Jika plan menunjukkan operasi sort terpisah, berarti ORDER BY tidak dipenuhi langsung dari index. Untuk query dengan offset besar, ini sering menjadi sumber latensi utama.

3. Berapa estimasi row yang dibaca?

Bandingkan jumlah row yang diperkirakan dibaca dengan jumlah row yang benar-benar dikembalikan. Rasio yang sangat jomplang biasanya berarti query menghabiskan waktu untuk memfilter, menyortir, atau membuang data.

4. Adakah lookup ke tabel utama?

Jika plan menunjukkan akses tambahan setelah pembacaan index, kemungkinan ada row lookup untuk mengambil kolom yang tidak tersedia di index. Ini tidak selalu buruk, tetapi bisa mahal jika jumlah kandidat besar.

5. Apakah urutan index cocok dengan query?

Jika WHERE memakai kolom A dan ORDER BY memakai kolom B, cek apakah index komposit mendukung pola itu dalam urutan yang relevan. Banyak query lambat bukan karena “tidak ada index”, tetapi karena index yang ada tidak cocok dengan pola akses.

Praktik baik: jangan hanya melihat apakah query “pakai index”. Query bisa saja memakai index tetapi tetap lambat karena scan-nya terlalu lebar, sort tetap terjadi, atau OFFSET terlalu besar.

Offset pagination vs keyset/cursor pagination

Jika halaman dalam sering diakses atau dataset terus tumbuh, perbaikan index saja sering tidak cukup. Di titik ini, Anda perlu mempertimbangkan keyset pagination atau cursor pagination.

Offset pagination

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 50000;

Kelebihan:

mudah dipahami,
mudah diintegrasikan dengan UI berbasis nomor halaman,
cocok untuk dataset kecil sampai menengah atau halaman awal.

Kekurangan:

semakin lambat di halaman dalam,
hasil bisa bergeser jika ada insert/delete di antara request,
mahal untuk tabel besar dan traffic tinggi.

Keyset/cursor pagination

Alih-alih “lewati 50.000 baris”, keyset memakai nilai terakhir dari halaman sebelumnya sebagai titik lanjut.

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
  AND (
    created_at < :last_created_at
    OR (created_at = :last_created_at AND id < :last_id)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

Dengan index yang sesuai, database tidak perlu membuang puluhan ribu baris. Ia bisa langsung melanjutkan dari posisi terakhir yang diketahui.

Kelebihan:

lebih stabil untuk data besar,
latensi lebih konsisten antar halaman,
lebih efisien untuk infinite scroll atau feed.

Kekurangan:

lebih sulit diimplementasikan,
tidak natural untuk lompat ke halaman 123,
butuh urutan yang stabil dan unik, biasanya dengan tie-breaker seperti id.

Kapan memilih yang mana?

Pilih offset pagination jika UI butuh nomor halaman, datanya belum terlalu besar, dan halaman dalam jarang diakses.
Pilih keyset/cursor pagination jika query daftar adalah hot path, tabel besar, data sering berubah, atau performa halaman dalam sudah bermasalah.

Checklist diagnosis query offset pagination yang lambat

Cek query nyata dari aplikasi
Jangan menganalisis versi sederhana jika produksi sebenarnya memakai join, filter tambahan, atau SELECT *.
Lihat EXPLAIN
Pastikan Anda paham apakah terjadi full scan, scan lebar, sort eksplisit, atau row lookup berulang.
Cocokkan WHERE dan ORDER BY dengan index
Index tunggal per kolom sering tidak cukup untuk query pagination.
Periksa kolom yang diambil
Ambil hanya kolom yang diperlukan untuk halaman daftar.
Pastikan ORDER BY deterministik
Tambahkan tie-breaker seperti id jika kolom sort utama tidak unik.
Uji halaman awal vs halaman dalam
Masalah offset sering baru terlihat pada OFFSET besar.
Amati cardinality dan selektivitas filter
Filter dengan selektivitas rendah bisa membuat index kurang membantu.
Perhatikan join
Kadang bottleneck bukan di tabel utama, tetapi pada urutan join atau join yang memperbesar jumlah kandidat sebelum limit diterapkan.
Bandingkan dengan keyset pagination
Jika offset besar adalah pola umum, pertimbangkan perubahan strategi, bukan hanya tuning lokal.

Kesalahan umum saat membuat index untuk pagination

1. Mengandalkan index tunggal untuk query gabungan

Index pada status saja atau created_at saja belum tentu memadai jika query butuh keduanya secara bersamaan.

2. Urutan kolom index tidak sesuai pola query

Index komposit sangat sensitif terhadap urutan kolom. Index (created_at, status) dan (status, created_at) bisa memberi hasil sangat berbeda tergantung query.

3. Tidak menambahkan tie-breaker pada ORDER BY

Jika banyak row punya created_at yang sama, urutan bisa tidak stabil antar halaman. Tambahkan id agar pagination konsisten.

4. Terlalu banyak index

Menambah index memang bisa mempercepat baca, tetapi ada biaya:

insert/update/delete menjadi lebih mahal,
ruang penyimpanan bertambah,
optimizer punya lebih banyak pilihan yang belum tentu semuanya baik.

Buat index berdasarkan query paling penting, bukan semua kemungkinan query.

5. Menganggap “pakai index” berarti pasti cepat

Index bisa dipakai tetapi tetap buruk jika scan tetap besar, sort masih terjadi, atau OFFSET sangat dalam.

Panduan migrasi aman tanpa banyak downtime

Jika Anda sudah tahu query mana yang lambat, perbaikannya sering melibatkan penambahan index atau perubahan pola pagination. Lakukan bertahap agar risiko rendah.

1. Identifikasi query yang paling mahal

Ambil dari slow query log, APM, atau metrik database. Fokus pada query dengan frekuensi tinggi atau latensi paling besar.

2. Tambahkan index baru tanpa langsung menghapus yang lama

Buat index komposit yang dibutuhkan terlebih dahulu. Setelah itu, uji plan dan latensi query. Jangan buru-buru menghapus index lama sebelum yakin tidak dipakai workload lain.

3. Uji EXPLAIN sebelum dan sesudah

Pastikan perubahan benar-benar mengurangi scan, sort, atau lookup yang tidak perlu. Jangan hanya mengandalkan intuisi.

4. Roll out bertahap di aplikasi

Jika migrasi ke keyset/cursor pagination, pertimbangkan pendekatan berikut:

tetap dukung offset untuk endpoint lama,
tambahkan endpoint atau parameter baru berbasis cursor,
migrasikan klien secara bertahap,
pantau error rate dan perubahan latensi.

5. Hindari perubahan besar sekaligus

Jangan mengubah query, index, dan kontrak API dalam satu langkah jika tidak perlu. Perubahan kecil lebih mudah divalidasi dan di-rollback.

6. Perhatikan dampak ke operasi tulis

Index komposit tambahan membantu baca, tetapi ada biaya di jalur tulis. Pantau insert/update latency setelah deploy.

7. Siapkan fallback

Jika Anda mengganti endpoint pagination, siapkan mekanisme rollback. Misalnya, fitur baru diaktifkan lewat feature flag sehingga bisa dimatikan cepat bila muncul anomali.

Contoh strategi migrasi dari offset ke cursor

Salah satu pendekatan yang aman:

Pertahankan endpoint lama:
GET /posts?page=5
Tambahkan endpoint atau mode baru:
GET /posts?cursor=...
Gunakan urutan stabil:
ORDER BY created_at DESC, id DESC
Encode cursor dari pasangan created_at dan id.
Uji konsistensi hasil saat ada insert baru di tengah trafik.

Ini memungkinkan frontend atau client mobile pindah bertahap tanpa memutus kompatibilitas lama.

Ringkasan praktis

Jika offset pagination makin lambat, penyebab utamanya biasanya bukan sekadar “database lambat”, tetapi cara query dijalankan di atas data yang makin besar. Bottleneck yang paling sering adalah:

full scan atau scan terlalu lebar,
sort/filesort karena ORDER BY tidak didukung index yang tepat,
row lookup karena kolom yang diminta terlalu banyak atau tidak tercakup index,
biaya OFFSET besar karena database harus membuang banyak hasil sebelum mengembalikan halaman yang diminta.

Langkah perbaikan paling penting:

cek EXPLAIN,
selaraskan WHERE dan ORDER BY dengan index komposit,
ambil kolom seperlunya,
buat urutan sort deterministik,
dan bila halaman dalam sering diakses, pertimbangkan keyset/cursor pagination.

Dengan pendekatan ini, Anda tidak hanya “menambah index”, tetapi benar-benar mengurangi kerja yang harus dilakukan database untuk setiap halaman.

Quick Search

shopping_cart Keranjang