Pada tabel kecil, pagination dengan OFFSET/LIMIT plus COUNT(*) biasanya terasa sederhana dan cukup cepat. Masalah mulai muncul saat jumlah baris membesar: halaman daftar melambat, CPU atau I/O database naik, dan setelah diperiksa ternyata query mengambil data 20 baris cukup cepat, tetapi query COUNT(*) untuk menghitung total data justru memakan waktu paling lama.

Jika Anda mengalami kondisi itu, inti masalahnya biasanya sederhana: mengambil sedikit data untuk satu halaman sering jauh lebih murah daripada menghitung seluruh baris yang cocok dengan filter. Solusinya bukan selalu menambah index atau memperbesar server, tetapi memilih strategi pagination yang sesuai dengan kebutuhan produk dan pola query.

Mengapa COUNT(*) bisa menjadi bottleneck

Pada banyak kasus, halaman daftar melakukan dua query:

  1. Query data utama untuk mengambil baris halaman saat ini.
  2. Query COUNT(*) untuk menampilkan total item atau total halaman.

Contohnya:

SELECT id, created_at, status, customer_name
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 2000;

SELECT COUNT(*)
FROM orders
WHERE status = 'paid';

Query pertama hanya perlu mengembalikan 20 baris. Query kedua harus menghitung seluruh baris yang memenuhi kondisi. Pada dataset besar, biaya query kedua bisa dominan.

Penyebab teknis yang umum

  • COUNT(*) tetap harus memproses banyak baris. Walaupun hasil akhirnya satu angka, database sering tetap perlu membaca banyak data atau index entries untuk memastikan jumlah yang benar.
  • Filter membuat perhitungan tidak trivial. Jika ada WHERE, database tidak bisa sekadar membaca metadata total tabel; ia harus menghitung baris yang cocok dengan filter.
  • Index tidak selalu menyelesaikan masalah. Jika filter tidak selektif, atau kolom yang dibutuhkan tidak cocok dengan index yang ada, database tetap harus membaca banyak halaman index atau bahkan tabel.
  • OFFSET besar menambah beban terpisah. Selain COUNT(*), query data dengan OFFSET besar juga mahal karena database harus melewati banyak baris sebelum sampai ke halaman yang diminta.
  • ORDER BY tertentu memaksa kerja tambahan. Jika urutan tidak didukung index yang tepat, database perlu sort dalam jumlah besar sebelum menerapkan LIMIT.

Catatan penting: COUNT(*) pada tabel tanpa filter dan COUNT(*) dengan filter adalah dua masalah yang berbeda. Pada praktik pagination, yang mahal hampir selalu adalah hitung total dengan filter, pencarian, join, atau kondisi bisnis tertentu.

Gejala nyata di produksi

Bottleneck pagination biasanya muncul dengan pola seperti ini:

  • Endpoint daftar terasa lambat meskipun ukuran halaman kecil, misalnya 20 atau 50 item.
  • Profiling menunjukkan query data utama cepat, tetapi query count lambat.
  • Lonjakan traffic pada halaman admin/report/list menyebabkan CPU database tinggi.
  • I/O storage meningkat karena banyak scan pada index atau tabel.
  • Halaman pertama masih lumayan cepat, tetapi filter tertentu membuat respons jauh lebih lambat.
  • Nomor halaman besar seperti page 200 atau page 1000 menjadi sangat lambat karena kombinasi OFFSET besar dan COUNT(*).

Kalau gejalanya seperti ini, jangan langsung menganggap masalahnya ada pada ORM atau network. Mulailah dari SQL aktual yang dijalankan aplikasi.

Kapan OFFSET/LIMIT dan COUNT(*) mulai bermasalah

Tidak ada angka universal yang berlaku untuk semua sistem. Masalah mulai terasa ketika beberapa faktor bertemu:

  • Jumlah baris besar.
  • Filter sering dipakai dan hasilnya tetap banyak.
  • Pengguna membuka halaman dalam dengan OFFSET besar.
  • Urutan data memakai kolom yang tidak didukung index yang tepat.
  • Query melibatkan join, pencarian teks, atau kondisi dinamis.
  • Traffic tinggi membuat query count yang mahal terjadi terus-menerus.

Secara praktis, OFFSET/LIMIT mulai kurang efisien ketika aplikasi perlu sering mengakses halaman dalam. Database harus melewati baris-baris awal dulu sebelum mengembalikan hasil. Sementara itu, COUNT(*) mulai bermasalah ketika UI selalu menuntut angka total presisi, padahal pengguna sebenarnya hanya butuh bisa lanjut ke halaman berikutnya.

Cara membaca EXPLAIN secara dasar

Anda tidak perlu menjadi spesialis query planner untuk mendapat manfaat dari EXPLAIN. Tujuannya adalah menjawab pertanyaan sederhana: apakah database melakukan kerja lebih banyak dari yang saya kira?

Yang perlu diperhatikan

  • Apakah query melakukan scan besar? Jika planner menunjukkan scan pada banyak baris, count akan mahal.
  • Apakah index dipakai? Jika tidak, cari tahu apakah kondisi WHERE dan ORDER BY cocok dengan index yang ada.
  • Apakah ada sort mahal? Sort besar sebelum LIMIT bisa menambah latensi.
  • Berapa estimasi rows? Estimasi besar menandakan query menyentuh porsi data yang luas.
  • Apakah ada join yang memperbesar kerja? Count pada query dengan join sering lebih mahal dari yang terlihat di level aplikasi.

Contoh pembacaan sederhana

Misalnya Anda punya query:

EXPLAIN
SELECT COUNT(*)
FROM orders
WHERE status = 'paid';

Jika hasilnya menunjukkan scan pada bagian besar tabel atau index, berarti biaya query memang tinggi. Lalu bandingkan dengan:

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

Sering kali query data bisa memakai index untuk mengambil sedikit baris teratas dengan cepat, sementara query count tetap harus menghitung semua yang cocok. Inilah alasan mengapa list terlihat lambat padahal masalahnya bukan di pengambilan data halaman.

Tip diagnosis: jalankan EXPLAIN untuk query data dan query count secara terpisah. Jangan berasumsi keduanya punya biaya yang mirip hanya karena memakai filter yang sama.

Kapan index membantu COUNT(*) dan kapan tidak

Index membantu jika

  • Filter sangat selektif, sehingga hanya sebagian kecil data yang perlu dibaca.
  • Kolom pada WHERE cocok dengan urutan index.
  • Database bisa menghitung dari index yang lebih kecil daripada membaca tabel utama.
  • Query data memakai urutan yang didukung index, sehingga ORDER BY + LIMIT menjadi murah.

Index tidak banyak membantu jika

  • Filter cocok dengan sebagian besar tabel, sehingga tetap harus membaca banyak entries.
  • Query count melibatkan ekspresi, fungsi, atau kondisi yang mengurangi efektivitas index.
  • Join atau kondisi tambahan membuat planner memilih strategi scan luas.
  • Anda berharap index membuat count bersyarat menjadi instan. Pada praktiknya, database tetap harus memastikan jumlah yang benar.

Kesalahan umum adalah berpikir: “tinggal tambah index, maka COUNT(*) pasti cepat.” Kadang benar, sering juga tidak. Index adalah alat penting, tetapi bukan jaminan untuk hitung total cepat pada dataset besar.

Strategi pagination cepat selain mengandalkan COUNT(*)

1. Keyset pagination untuk halaman berikutnya/sebelumnya

Keyset pagination menggunakan penanda baris terakhir yang sudah dilihat, bukan nomor halaman berbasis offset. Ini sangat efektif untuk tabel besar karena database tidak perlu melewati ribuan baris seperti pada OFFSET.

Contoh query halaman pertama:

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

Jika baris terakhir pada halaman itu memiliki created_at = '2025-01-10 09:30:00' dan id = 4812, halaman berikutnya bisa diambil dengan:

SELECT id, created_at, status, customer_name
FROM orders
WHERE status = 'paid'
  AND (
    created_at < '2025-01-10 09:30:00'
    OR (created_at = '2025-01-10 09:30:00' AND id < 4812)
  )
ORDER BY created_at DESC, id DESC
LIMIT 20;

Mengapa ini cepat? Karena database bisa melanjutkan dari posisi tertentu dalam urutan index, bukan menghitung lalu membuang baris-baris sebelumnya.

Kapan dipilih:

  • Feed, timeline, daftar transaksi, log, notifikasi.
  • Pengguna umumnya hanya butuh next/previous, bukan lompat ke halaman 327.
  • Urutan data stabil dan punya kolom pembeda yang unik, misalnya created_at + id.

Trade-off:

  • Tidak cocok jika UI menuntut nomor halaman presisi.
  • Implementasi lebih rumit daripada OFFSET/LIMIT.
  • Perlu urutan yang deterministik. Jika hanya mengurutkan created_at tanpa tie-breaker unik, hasil bisa duplikat atau terlewat.

2. Deferred count: tampilkan data dulu, hitung total belakangan

Jika pengguna lebih peduli melihat daftar secepat mungkin daripada total presisi, deferred count adalah opsi praktis. Polanya:

  1. Respons awal hanya memuat data halaman.
  2. Total item dihitung di background atau lewat request terpisah.
  3. UI menampilkan placeholder seperti “menghitung total...” lalu memperbarui angka jika sudah tersedia.

Kapan dipilih:

  • Halaman dashboard atau admin yang harus terasa responsif.
  • Total item berguna, tetapi bukan syarat utama untuk mulai bekerja.

Trade-off:

  • UI sedikit lebih kompleks.
  • Total bisa muncul terlambat atau tidak selalu sinkron dengan list saat data sering berubah.

3. Cached count untuk query yang sering berulang

Jika filter yang dipakai relatif terbatas dan sering diulang, Anda bisa menyimpan hasil count di cache dengan TTL tertentu atau invalidasi berbasis event.

Contoh pendekatan:

  • Key cache berdasarkan kombinasi filter yang umum, misalnya status, tanggal, atau tenant.
  • Simpan hasil count untuk beberapa detik atau menit.
  • Saat request berikutnya datang, gunakan count dari cache jika masih valid.

Kapan dipilih:

  • Query count yang sama sering diminta berkali-kali.
  • Akurasi real-time tidak mutlak.
  • Beban database perlu diturunkan saat traffic tinggi.

Trade-off:

  • Data count bisa stale.
  • Kombinasi filter yang terlalu banyak bisa membuat cache tidak efisien.
  • Invalidasi cache sering lebih sulit daripada kelihatannya.

Praktik baik: cache paling efektif untuk filter yang memang populer dan berulang, bukan untuk setiap kemungkinan query ad hoc dari pengguna.

4. Approximate count saat akurasi absolut tidak diperlukan

Tidak semua produk membutuhkan jumlah total yang benar sampai digit terakhir. Untuk pencarian, analitik ringan, atau daftar besar, approximate count sering cukup.

Pendekatan yang umum:

  • Menggunakan statistik atau estimasi planner database.
  • Menyimpan agregat periodik dari proses batch.
  • Menampilkan label seperti “sekitar 12 ribu hasil” alih-alih angka presisi.

Kapan dipilih:

  • Tujuan utama adalah kecepatan respons.
  • Pengguna tidak membuat keputusan kritis dari total exact.

Trade-off:

  • Angka tidak selalu presisi.
  • Tidak cocok untuk kebutuhan audit, billing, atau pelaporan resmi.

5. Desain UI tanpa total halaman presisi

Sering kali perbaikan terbesar justru datang dari keputusan produk, bukan optimasi SQL. Jika UI tidak memaksa “Page X of Y”, kebutuhan COUNT(*) bisa hilang sama sekali.

Pola UI yang lebih ramah performa:

  • Tombol Load more.
  • Navigasi Next dan Previous tanpa total halaman.
  • Menampilkan apakah masih ada data berikutnya dengan cara mengambil LIMIT + 1.

Contoh:

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

Jika aplikasi butuh menampilkan 20 item, ambil 21. Bila baris ke-21 ada, berarti masih ada halaman berikutnya. Ini jauh lebih murah daripada menghitung semua baris.

Kapan dipilih:

  • Aplikasi operasional, feed, daftar aktivitas, atau mobile UI.
  • Pengguna jarang benar-benar membutuhkan total halaman.

Contoh keputusan implementasi yang realistis

Skenario 1: daftar transaksi terbaru

Karakteristiknya biasanya data terus bertambah, pengguna fokus pada item terbaru, dan jarang lompat ke halaman sangat dalam. Pilihan yang umumnya tepat:

  • Gunakan keyset pagination.
  • Hilangkan total halaman presisi.
  • Tampilkan tombol next/previous atau load more.

Skenario 2: halaman admin dengan filter status

Jika admin butuh melihat daftar cepat, tetapi total hanya pelengkap:

  • Gunakan OFFSET/LIMIT untuk tahap awal jika halaman dangkal.
  • Hitung total secara deferred atau cache hasil count filter populer.

Skenario 3: laporan yang memang butuh total presisi

Jika bisnis menuntut angka exact:

  • Pastikan query count terpisah dan benar-benar dioptimalkan.
  • Tinjau index untuk filter utama.
  • Pertimbangkan materialized summary atau tabel agregat yang dipelihara aplikasi/batch.
  • Jangan paksa pengalaman UI interaktif yang sama cepatnya dengan feed biasa jika memang beban hitungnya berbeda.

Checklist diagnosis bottleneck pagination

  1. Log SQL aktual: pastikan query apa saja yang benar-benar dijalankan aplikasi, termasuk query count tambahan dari ORM.
  2. Ukur terpisah: bandingkan durasi query data dan query COUNT(*).
  3. Jalankan EXPLAIN untuk keduanya.
  4. Periksa pola akses: apakah pengguna sering membuka halaman dalam?
  5. Periksa ORDER BY: apakah sesuai dengan index?
  6. Periksa selektivitas filter: apakah filter menyaring sedikit atau justru hampir semua data?
  7. Audit join dan pencarian: query count dengan join sering jauh lebih mahal dari yang diharapkan.
  8. Tanya kebutuhan produk: apakah benar perlu total halaman presisi?
  9. Uji LIMIT+1 untuk mendeteksi ada halaman berikutnya tanpa count.
  10. Evaluasi cache atau approximate count bila akurasi mutlak tidak wajib.

Kesalahan umum yang sering terjadi

  • Mengoptimalkan query data, tetapi mengabaikan query count. Padahal justru count yang paling lambat.
  • Menganggap OFFSET/LIMIT selalu cukup. Ia sederhana, tetapi tidak skala baik untuk halaman dalam.
  • Memaksa nomor halaman presisi pada semua layar. Kebutuhan UI semacam ini mahal pada dataset besar.
  • Menambah index tanpa melihat EXPLAIN. Index yang salah bisa tidak membantu sama sekali.
  • Mengurutkan dengan kolom tidak stabil saat memakai keyset pagination, sehingga hasil tidak konsisten.
  • Mencache semua variasi filter hingga cache membengkak dan hit ratio rendah.

Rekomendasi praktis

Jika tabel Anda sudah besar dan COUNT(*) mulai terasa mahal, urutan keputusan yang aman biasanya seperti ini:

  1. Pastikan bottleneck-nya benar di count dengan pengukuran dan EXPLAIN.
  2. Untuk daftar operasional atau feed, pindah ke keyset pagination dan hilangkan total halaman presisi.
  3. Jika total tetap dibutuhkan tetapi tidak harus instan, gunakan deferred count.
  4. Jika pola filternya berulang, tambahkan cached count dengan TTL atau invalidasi yang masuk akal.
  5. Jika angka perkiraan cukup, pakai approximate count dan jujur di UI bahwa nilainya estimasi.
  6. Jika bisnis menuntut total exact, optimalkan query count secara khusus dan pertimbangkan summary table/agregasi.

Intinya, strategi pagination cepat pada tabel besar jarang selesai hanya dengan satu trik SQL. Anda perlu memilih kombinasi yang sesuai antara kebutuhan UI, akurasi data, dan biaya query. Dalam banyak sistem, cara paling cepat mengatasi COUNT(*) mahal adalah berhenti memintanya di setiap request.