Offset Pagination Lambat? Audit Query dan Index untuk Tabel Besar

Offset pagination lambat biasanya bukan karena LIMIT 20-nya kecil, melainkan karena database harus menemukan, menyaring, mengurutkan, lalu melewati ribuan sampai jutaan baris sebelum mengambil 20 data yang diminta. Gejalanya umum di halaman list/admin/API: page 1 cepat, page 2000 mulai berat, CPU naik, disk I/O meningkat, dan hasil EXPLAIN menunjukkan scan besar atau filesort.

Solusinya jarang cukup dengan “tambah index” secara acak. Yang perlu diaudit adalah pola query sebenarnya: kolom mana yang dipakai untuk WHERE, bagaimana ORDER BY-nya, apakah urutan index selaras dengan filter dan sort, dan apakah offset pagination masih layak dipertahankan untuk use case tersebut. Artikel ini fokus pada audit praktis untuk tabel besar.

Kenapa OFFSET/LIMIT makin lambat saat tabel membesar

Query seperti ini tampak sederhana:

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

Masalahnya, database tidak bisa langsung “loncat” ke baris ke-20001 secara gratis. Untuk mengembalikan 20 baris terakhir dari hasil terurut, mesin database sering tetap perlu:

mencari semua kandidat yang cocok dengan WHERE,
mengurutkan sesuai ORDER BY jika index tidak mendukung urutan itu,
melewati OFFSET baris lebih dulu,
baru mengambil LIMIT baris yang diminta.

Semakin besar offset, semakin besar kerja yang dibuang. Karena itu page awal cepat, sedangkan page jauh melambat walaupun ukuran respons tetap kecil.

Gejala nyata di aplikasi

Halaman admin: list data page 1 responsif, page ratusan mulai timeout.
API publik/internal: endpoint list stabil di awal, tetapi latensi melonjak untuk offset besar.
Database host: CPU meningkat karena sort/scan, disk read naik, buffer cache tertekan.
EXPLAIN: jumlah baris yang dibaca jauh lebih besar dari jumlah hasil, muncul indikasi scan penuh atau filesort.

Langkah audit query: jangan menebak, lihat query aktual

1. Tangkap query yang benar-benar dijalankan

Audit harus dimulai dari SQL nyata dari aplikasi, bukan dari asumsi. Pastikan Anda tahu:

kolom yang di-SELECT,
filter di WHERE,
urutan di ORDER BY,
nilai LIMIT dan OFFSET,
apakah ada JOIN,
apakah query dibangkitkan ORM dengan kondisi tambahan yang tidak disadari.

Kesalahan umum: developer merasa query hanya “filter status lalu urutkan tanggal”, padahal ORM menambah ORDER BY id kedua, atau menambahkan filter tenant, soft delete, dan kolom lain yang mengubah kebutuhan index.

2. Jalankan EXPLAIN

Setelah query aktual didapat, jalankan EXPLAIN atau fasilitas rencana eksekusi yang setara di database Anda. Tujuannya bukan menghafal semua kolom output, tetapi menjawab pertanyaan ini:

Apakah database memakai index yang relevan?
Berapa banyak baris yang diperkirakan atau dibaca?
Apakah ada sort tambahan karena urutan tidak ditopang index?
Apakah yang terjadi adalah index scan, range scan, atau malah full table scan?

Contoh:

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

3. Baca EXPLAIN secara ringkas

Walau format berbeda antar database, indikator ini biasanya penting:

Access type / scan type: apakah full scan atau memakai index/range.
Rows: berapa banyak baris yang diperkirakan akan dibaca. Jika hasil hanya 20 tetapi rows sangat besar, itu sinyal kuat ada pemborosan.
Extra / notes: perhatikan indikasi seperti Using filesort, Using temporary, atau operasi sort/materialisasi lain.
Key / index used: apakah index yang dipakai memang sesuai dengan filter dan urutan query.

Jika EXPLAIN menunjukkan scan besar atau filesort pada query list yang sering dipanggil, biasanya masalah utamanya ada pada desain index atau pola pagination, bukan pada ukuran LIMIT.

Contoh masalah umum dan perbaikannya

Kasus 1: hanya ada index di kolom sort, tapi filter tidak ter-cover

Misalkan query:

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

Lalu tabel hanya punya index ini:

CREATE INDEX idx_posts_created_at ON posts(created_at);

Index tersebut bisa membantu urutan berdasarkan created_at, tetapi jika proporsi status = 'published' tidak kecil atau database tetap harus memfilter banyak baris, hasilnya bisa tetap mahal. Terutama ketika offset besar, mesin perlu berjalan jauh di index sambil membuang banyak entri yang tidak lolos filter atau tidak termasuk page saat ini.

Perbaikan yang sering lebih tepat adalah index komposit yang mencerminkan pola query:

CREATE INDEX idx_posts_status_created_at ON posts(status, created_at);

Kenapa ini membantu:

database bisa lebih cepat menemukan subset status = 'published',
urutan created_at tersedia dalam subset itu,
jumlah baris yang perlu discan dan diurutkan berkurang.

Jika hasil masih ambigu karena banyak baris punya created_at yang sama, sering berguna menambahkan tie-breaker yang konsisten:

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

Lalu query diubah menjadi:

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

Tujuannya bukan sekadar performa, tetapi juga menjaga urutan stabil antar page.

Kasus 2: ORDER BY tidak selaras dengan index

Jebakan yang sangat umum adalah merasa “sudah ada index”, tetapi urutan query tidak cocok dengan susunan kolom di index.

Contoh index:

CREATE INDEX idx_orders_status_created_at ON orders(status, created_at);

Query:

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

Index di atas membantu filter status, tetapi tidak membantu ORDER BY total. Akibatnya database masih perlu menyortir hasil filter, dan ketika jumlah kandidat besar, sort menjadi mahal. Pada EXPLAIN Anda bisa melihat indikasi sort tambahan.

Jika pola query utama memang filter berdasarkan status lalu urut total, index yang relevan harus mencerminkan itu:

CREATE INDEX idx_orders_status_total ON orders(status, total);

Namun ini punya trade-off: Anda tidak bisa membuat index komposit untuk semua kombinasi filter dan sort sekaligus tanpa biaya. Setiap index tambahan menambah:

ukuran storage,
biaya insert/update/delete,
kompleksitas maintenance.

Karena itu, prioritaskan index untuk query yang paling sering dipanggil atau paling mahal.

Kasus 3: urutan kolom dalam composite index salah

Urutan kolom di index komposit penting. Jika query utama adalah:

SELECT id, customer_id, created_at
FROM invoices
WHERE tenant_id = 42 AND status = 'open'
ORDER BY created_at DESC
LIMIT 25 OFFSET 10000;

Maka index yang lazim dipertimbangkan adalah:

CREATE INDEX idx_invoices_tenant_status_created_at
ON invoices(tenant_id, status, created_at);

Kenapa bukan (created_at, tenant_id, status)? Karena database biasanya lebih efektif jika kolom filter yang membatasi himpunan data ditempatkan lebih dulu, lalu kolom sort yang dipakai di dalam subset tersebut. Susunan yang salah bisa membuat bagian penting dari index tidak optimal dipakai.

Aturan praktisnya: sesuaikan dengan pola akses aktual, bukan dengan urutan kolom di tabel.

Membaca hasil audit: kapan index Anda sudah benar?

Anda tidak harus mengejar EXPLAIN yang “sempurna”, tetapi untuk query pagination besar, tanda-tanda membaik biasanya seperti ini:

database memakai index yang sesuai dengan WHERE dan ORDER BY,
jumlah baris yang dibaca turun signifikan dibanding sebelumnya,
sort tambahan berkurang atau hilang,
waktu respons page jauh membaik, bukan hanya page awal.

Jika setelah menambah index komposit performa tetap buruk, cek hal berikut:

apakah offset terlalu besar sehingga walau memakai index, database tetap harus melewati terlalu banyak entri,
apakah query mengambil kolom besar yang memicu lookup tambahan atau I/O tinggi,
apakah ada fungsi pada kolom filter/sort yang membuat index sulit dipakai,
apakah statistik database usang sehingga optimizer salah memilih rencana,
apakah ada join yang justru menjadi bottleneck utama.

Contoh sebelum dan sesudah audit

Sebelum

SELECT id, email, created_at
FROM users
WHERE deleted_at IS NULL AND role = 'member'
ORDER BY last_login_at DESC
LIMIT 50 OFFSET 150000;

Index yang ada:

CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_last_login_at ON users(last_login_at);

Masalahnya:

filter memakai dua kondisi: deleted_at dan role,
sort memakai last_login_at,
dua index tunggal tidak otomatis berarti query ini efisien,
offset sangat besar.

Sesudah

CREATE INDEX idx_users_deleted_role_last_login
ON users(deleted_at, role, last_login_at);

Query tetap:

SELECT id, email, created_at
FROM users
WHERE deleted_at IS NULL AND role = 'member'
ORDER BY last_login_at DESC
LIMIT 50 OFFSET 150000;

Perbaikan yang diharapkan:

subset aktif + role tertentu lebih cepat ditemukan,
urutan login terbaru tersedia dalam subset itu,
sort tambahan berkurang.

Namun perlu jujur: untuk offset sebesar 150000, performa masih bisa menurun walau index sudah lebih baik. Index yang tepat mengurangi biaya, tetapi tidak menghapus sifat dasar offset pagination yang harus melewati banyak hasil.

Jebakan yang sering membuat offset pagination tetap berat

1. ORDER BY tidak deterministik

Jika Anda hanya mengurutkan berdasarkan kolom yang nilainya sering sama, hasil antar page bisa tidak stabil. Tambahkan kolom pembeda seperti id:

ORDER BY created_at DESC, id DESC

Ini penting terutama jika nanti bermigrasi ke keyset/cursor.

2. Menggunakan fungsi pada kolom yang di-index

Contoh:

WHERE DATE(created_at) = '2025-04-01'

Pola seperti ini sering mengurangi manfaat index pada created_at. Lebih aman ubah ke rentang waktu yang setara:

WHERE created_at >= '2025-04-01 00:00:00'
  AND created_at <  '2025-04-02 00:00:00'

3. Mengira index tunggal selalu cukup

Query dengan kombinasi filter dan sort sering butuh composite index, bukan hanya kumpulan index satu kolom. Optimizer tidak selalu bisa menggabungkan beberapa index dengan hasil sebaik satu index komposit yang sesuai pola query.

4. SELECT terlalu banyak kolom

Untuk endpoint list, hindari mengambil kolom besar yang tidak dipakai di tampilan atau respons API. Semakin banyak data yang harus dibaca, semakin tinggi biaya I/O. Audit query tidak berhenti di WHERE dan ORDER BY; daftar kolom yang diambil juga penting.

Checklist diagnosis offset pagination lambat

Ambil SQL aktual dari aplikasi, bukan pseudo query.
Catat WHERE, ORDER BY, LIMIT, dan OFFSET.
Jalankan EXPLAIN pada query tersebut.
Lihat apakah ada full scan, scan baris besar, atau sort tambahan.
Pastikan ada index untuk kolom filter utama.
Pastikan urutan ORDER BY selaras dengan index.
Pertimbangkan composite index jika query menggabungkan filter + sort.
Periksa apakah urutan kolom dalam index sudah sesuai pola akses.
Tambahkan tie-breaker pada ORDER BY bila urutan belum stabil.
Kurangi kolom yang diambil jika endpoint hanya butuh field tertentu.
Uji page awal dan page jauh, karena masalah sering tidak terlihat di page 1.
Jika offset sangat besar dan query tetap mahal, evaluasi migrasi ke keyset/cursor.

Trade-off offset pagination yang perlu dipahami

Offset pagination tetap berguna. Ia sederhana, mudah dipahami, dan cocok untuk banyak kebutuhan admin atau laporan ringan. Anda bisa langsung meminta “page 37” tanpa menyimpan state cursor.

Namun ada trade-off yang tidak bisa dihilangkan sepenuhnya:

Biaya bertambah seiring offset membesar: ini sifat dasar mekanismenya.
Kurang stabil pada data yang berubah: insert/delete di antara request bisa menggeser hasil antar page.
Perlu index yang sangat selaras agar tidak semakin mahal karena sort dan scan.

Artinya, index yang baik membuat offset pagination lebih sehat, tetapi tidak selalu membuatnya ideal untuk semua skala.

Kapan mulai mempertimbangkan keyset/cursor pagination

Artikel ini fokus pada audit query dan index, tetapi ada titik di mana tuning offset pagination tidak lagi cukup. Pertimbangkan keyset/cursor jika:

pengguna jarang benar-benar butuh lompat ke page arbitrer seperti page 8921,
query utama selalu mengurutkan berdasarkan kolom yang stabil dan terindeks,
offset bisa sangat besar di endpoint yang sering dipanggil,
latensi page jauh tetap tinggi meski index sudah tepat.

Contoh pola keyset:

SELECT id, title, created_at
FROM posts
WHERE status = 'published'
  AND (created_at, id) < ('2025-04-01 12:00:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Pendekatan ini menghindari biaya melewati offset besar. Namun trade-off-nya adalah navigasi page numerik menjadi kurang langsung dan implementasi API/UI perlu disesuaikan.

Penutup

Jika offset pagination lambat pada tabel besar, mulai dari hal yang paling konkret: audit query aktual, jalankan EXPLAIN, cocokkan index dengan kolom filter dan sort, lalu periksa apakah ORDER BY benar-benar selaras dengan index. Dalam banyak kasus, bottleneck utamanya bukan pada ukuran LIMIT, melainkan pada scan besar dan sort tambahan akibat index yang tidak tepat.

Setelah index dibenahi, uji lagi page jauh, bukan hanya page awal. Jika performa tetap menurun karena offset sudah terlalu besar, itu tanda bahwa masalahnya bukan lagi sekadar index, melainkan batas alami offset pagination. Di titik itu, migrasi ke keyset/cursor layak dipertimbangkan untuk endpoint yang paling kritis.

Quick Search

shopping_cart Keranjang