Query Lambat Saat Data Membesar: Index dan Pagination yang Waras

Query lambat saat data membesar hampir selalu punya pola yang sama: aplikasi terasa normal saat tabel masih kecil, lalu mendadak berat ketika baris sudah ratusan ribu atau jutaan. Solusinya bukan sekadar “tambah index” di semua kolom, melainkan memahami bagaimana database membaca data, kenapa sebuah query jadi mahal, dan trade-off apa yang muncul setelah optimasi.

Yang sering dilupakan: optimasi yang baik tidak hanya cepat hari ini, tetapi juga jelas alasannya, aman dipelihara tim, dan tidak membuat perilaku sistem sulit ditebak. Prinsip ini sejalan dengan gagasan umum tentang kode yang mudah dirawat: keputusan teknis harus bisa dijelaskan, diuji, dan tidak bergantung pada trik yang hanya dipahami satu orang.

Gejala nyata saat tabel mulai membesar

Sebelum bicara index dan pagination, kenali dulu gejalanya. Query yang bermasalah biasanya tidak muncul sebagai “error”, melainkan sebagai degradasi bertahap.

Endpoint list makin lambat saat membuka halaman 50, 100, atau lebih.
CPU database naik walau trafik tidak berubah drastis.
I/O meningkat karena database membaca terlalu banyak halaman data.
Timeout sporadis pada query pencarian atau laporan.
Lock dan antrean query ikut membesar karena query lambat menahan resource lebih lama.

Kasus umum: tabel orders terus bertambah, lalu query seperti ini mulai berat:

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

Sekilas query ini sederhana. Masalahnya ada di dua tempat: filter dan urutan butuh strategi index yang tepat, sementara OFFSET 50000 memaksa database melewati banyak baris sebelum mengambil 50 data yang benar-benar dikirim ke aplikasi.

Mula-mula lihat rencana eksekusi, bukan tebak-tebakan

Jangan langsung menambah index tanpa melihat rencana eksekusi. Gunakan EXPLAIN atau fasilitas setara di database Anda. Nama kolom dan output berbeda antar mesin database, tetapi prinsip bacanya sama.

Apa yang dicari saat membaca EXPLAIN

Apakah query melakukan full table scan? Jika ya, database membaca sangat banyak baris.
Index mana yang dipakai? Kadang index ada, tetapi tidak dipilih optimizer karena dianggap tidak efektif.
Berapa estimasi rows? Ini memberi gambaran seberapa banyak data yang akan disentuh.
Apakah ada sort tambahan? Jika hasil harus diurutkan di luar index, biaya query bisa naik.
Apakah terjadi lookup berulang ke tabel utama? Ini bisa mahal pada data besar.

Contoh:

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

Jika hasilnya menunjukkan pembacaan baris sangat besar, sort mahal, atau scan penuh, Anda sudah punya arah investigasi. Fokus pada fakta itu, bukan asumsi seperti “database saya kurang kuat”.

Catatan: EXPLAIN adalah alat untuk memahami perilaku query. Ia membantu tim berdiskusi dengan dasar yang sama. Ini jauh lebih maintainable daripada optimasi “karena katanya lebih cepat”.

Kapan index membantu, dan kapan tidak

Index membantu ketika ia memperkecil jumlah data yang harus dibaca atau ketika ia memungkinkan database mengambil data dalam urutan yang sudah sesuai. Tetapi index bukan peluru ajaib.

Index membantu ketika

Kolom pada WHERE cukup selektif.
Kolom pada ORDER BY cocok dengan urutan index.
Query sering dijalankan dan pola filternya stabil.
Anda ingin mengurangi scan dan sort pada tabel besar.

Index kurang membantu atau tidak efektif ketika

Query tetap harus membaca sebagian besar tabel.
Kolom yang difilter memiliki variasi rendah dan hampir semua baris cocok.
Anda membungkus kolom dengan fungsi sehingga index sulit dipakai, misalnya DATE(created_at).
Kondisi query tidak sesuai dengan susunan index.
Terlalu banyak index membuat operasi INSERT, UPDATE, dan DELETE lebih mahal.

Contoh anti-pattern:

SELECT *
FROM orders
WHERE DATE(created_at) = '2025-01-10';

Ini terlihat nyaman, tetapi fungsi pada kolom sering menghambat pemakaian index. Biasanya lebih baik memakai rentang waktu:

SELECT *
FROM orders
WHERE created_at >= '2025-01-10 00:00:00'
  AND created_at < '2025-01-11 00:00:00';

Dengan bentuk ini, database lebih mungkin memanfaatkan index pada created_at.

Composite index: urutan kolom menentukan hasil

Banyak query lambat tidak selesai hanya dengan index tunggal. Jika query memfilter berdasarkan satu kolom lalu mengurutkan berdasarkan kolom lain, composite index sering lebih tepat.

Ambil query ini:

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

Index tunggal pada status saja mungkin masih membuat database perlu menyortir hasil terpisah. Index tunggal pada created_at saja juga belum tentu efisien jika filter status penting. Dalam banyak kasus, index gabungan seperti berikut lebih masuk akal:

CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at);

Kenapa ini bekerja? Karena database bisa mencari baris dengan status = 'paid' lalu membaca data dalam urutan created_at yang sudah selaras dengan kebutuhan query.

Hal penting tentang composite index

Urutan kolom penting. (status, created_at) berbeda dengan (created_at, status).
Sesuaikan dengan query nyata. Jangan mendesain index dari teori umum saja.
Jangan duplikasi tanpa alasan. Setiap index punya biaya pemeliharaan.

Jika ada query lain seperti:

SELECT id, customer_id, status, created_at
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

Maka kebutuhan index-nya berbeda. Bisa jadi Anda memerlukan index lain yang lebih cocok dengan pola akses tersebut, misalnya:

CREATE INDEX idx_orders_customer_created_at
ON orders (customer_id, created_at);

Pelajarannya: index harus mengikuti beban kerja, bukan sebaliknya.

Masalah besar pada OFFSET: makin jauh halaman, makin mahal

Pagination dengan OFFSET mudah dipakai dan umum ditemukan di banyak aplikasi. Sayangnya, ia sering jadi sumber query lambat saat data membesar.

Contoh:

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

Database tetap harus menghitung atau melewati 100000 baris lebih dulu sebelum memberi 20 hasil terakhir. Walau index membantu urutan, biaya tetap membengkak untuk halaman yang jauh.

Kapan OFFSET masih masuk akal

Jumlah data kecil atau sedang.
Pengguna benar-benar butuh lompat ke halaman acak, misalnya halaman 7.
Beban query masih terbukti aman lewat pengukuran.

Kapan OFFSET mulai jadi masalah

Tabel sangat besar dan halaman yang diakses bisa dalam.
Urutan data sering berubah karena ada insert baru.
API atau UI lebih cocok dengan alur “load more” daripada nomor halaman absolut.

Keyset pagination: lebih stabil untuk data besar

Jika kebutuhan Anda adalah menampilkan data terbaru, feed, log, transaksi, atau daftar yang dibaca berurutan, keyset pagination biasanya lebih waras daripada OFFSET.

Alih-alih berkata “ambil halaman ke-5000”, keyset berkata “ambil 20 data setelah baris terakhir yang sudah saya lihat”.

Contoh sebelum: OFFSET pagination

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

Contoh sesudah: keyset pagination

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

Prinsipnya:

Gunakan kolom urut yang stabil.
Jika kolom urut tidak unik, tambahkan tie-breaker seperti id.
Simpan cursor dari baris terakhir yang diterima klien.

Index yang cocok biasanya mengikuti urutan query tersebut, misalnya pada (created_at, id).

Kenapa keyset pagination lebih baik

Tidak perlu melewati ribuan atau jutaan baris seperti OFFSET besar.
Lebih stabil saat ada data baru masuk di tengah navigasi.
Cocok untuk API dan UI model scroll atau “muat lebih banyak”.

Trade-off keyset pagination

Tidak nyaman untuk lompat langsung ke halaman 123.
Perlu desain cursor yang jelas di API.
Query dan logika UI sedikit lebih kompleks daripada OFFSET sederhana.

Ini contoh keputusan yang baik untuk maintainability: memang sedikit lebih rumit, tetapi alasannya kuat, perilakunya stabil, dan biasanya lebih aman pada skala besar.

Jebakan COUNT(*) pada pagination

Banyak implementasi pagination memerlukan total baris, lalu menjalankan COUNT(*) untuk setiap request. Pada tabel besar atau query kompleks, ini bisa mahal.

Contoh umum:

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

Masalahnya bukan berarti COUNT(*) selalu buruk, tetapi sering dipakai tanpa mempertimbangkan frekuensi dan biaya. Jika setiap buka halaman list memicu query data + query count berat, total bebannya bisa besar.

Kapan COUNT(*) masih wajar

Dataset relatif kecil.
Filter sederhana dan index mendukung.
Total halaman memang dibutuhkan oleh UX.

Alternatif saat COUNT(*) terlalu mahal

Hilangkan total absolut dan tampilkan pola “ada halaman berikutnya atau tidak”.
Gunakan keyset pagination sehingga UI tidak bergantung pada nomor halaman total.
Cache hasil count jika keterlambatan sedikit masih bisa diterima.
Precompute atau materialize agregat untuk kebutuhan laporan tertentu.

Yang penting, jangan memaksa total akurat real-time jika produk sebenarnya tidak membutuhkannya. Itu bentuk optimasi maintainable juga: menyederhanakan requirement, bukan hanya menyetel SQL.

Studi kasus: dari query list yang melambat ke solusi yang bisa dipelihara

Sebelum

SELECT id, customer_id, total_amount, status, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 20000;

Gejala:

Halaman awal masih cepat.
Halaman dalam mulai lambat.
CPU database naik saat jam sibuk.

Analisis

Filter menggunakan status.
Hasil diurutkan berdasarkan created_at DESC.
OFFSET besar membuat banyak baris terlewati.
Jika hanya ada index tunggal yang tidak sesuai, database tetap bekerja berat.

Perbaikan tahap 1: index sesuai pola akses

CREATE INDEX idx_orders_status_created_at
ON orders (status, created_at);

Ini membantu query halaman awal karena filter dan urutan lebih selaras.

Perbaikan tahap 2: ganti OFFSET dengan keyset

SELECT id, customer_id, total_amount, status, created_at
FROM orders
WHERE status = 'paid'
  AND (created_at, id) < ('2025-01-10 12:00:00', 55001)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Jika memakai tie-breaker id, pertimbangkan juga index yang sesuai dengan pola urut yang dipakai aplikasi.

Hasil keputusan teknis yang lebih waras

Query lebih konsisten pada data besar.
Alasan optimasi mudah dijelaskan ke tim.
UI/API harus menerima model cursor, tetapi kompleksitasnya terkontrol.

Checklist investigasi bottleneck

Sebelum mengubah schema atau query, pakai checklist ini:

Ambil query sebenarnya dari log aplikasi atau slow query log, bukan perkiraan.
Jalankan EXPLAIN dan lihat apakah terjadi scan besar, sort mahal, atau pemakaian index yang buruk.
Periksa pola WHERE, ORDER BY, LIMIT apakah cocok dengan index yang ada.
Lihat apakah ada OFFSET besar yang sebenarnya bisa diganti cursor/keyset.
Periksa SELECT list; jangan ambil kolom besar yang tidak dipakai.
Audit COUNT(*) apakah benar-benar diperlukan di setiap request.
Evaluasi biaya write; jangan menambah terlalu banyak index pada tabel yang sering berubah.
Uji pada data yang realistis; query yang bagus di localhost dengan ribuan baris belum tentu bagus di produksi.
Dokumentasikan alasan perubahan agar tim paham kenapa index atau strategi pagination dipilih.

Anti-pattern umum

Menambah index pada semua kolom tanpa memahami query utama.
Mengandalkan OFFSET besar untuk tabel yang terus tumbuh.
Memakai fungsi pada kolom terindeks di klausa WHERE.
SELECT * pada endpoint list yang sebenarnya hanya butuh beberapa kolom.
Mengoptimasi satu query tetapi merusak performa write karena index berlebihan.
Tidak memakai urutan yang stabil pada pagination, sehingga hasil bisa duplikat atau terlewat.
Memaksa total count real-time untuk UI yang sebenarnya tidak perlu.

Panduan memilih solusi yang tetap maintainable

Saat query melambat, pilih solusi yang tidak hanya cepat, tetapi juga masuk akal untuk dirawat tim.

Pilih index tambahan jika

Pola query jelas dan sering dipakai.
Index baru punya manfaat nyata pada WHERE dan/atau ORDER BY.
Biaya tambahan pada operasi tulis masih dapat diterima.

Pilih keyset pagination jika

Data besar dan akses dominan berurutan.
Anda tidak butuh lompat ke halaman acak.
Stabilitas hasil lebih penting daripada nomor halaman absolut.

Pertahankan OFFSET jika

Dataset kecil atau akses halaman dalam jarang terjadi.
UX membutuhkan nomor halaman yang eksplisit.
Pengukuran menunjukkan performa masih aman.

Kurangi atau ubah COUNT(*) jika

Query count menjadi bottleneck nyata.
Produk bisa hidup tanpa total absolut real-time.
Anda bisa mengganti UX menjadi “has next page”.

Pertanyaan yang berguna untuk tim:

Apakah optimasi ini bisa dijelaskan dengan sederhana?
Apakah perilakunya tetap benar saat data terus bertambah?
Apakah orang lain bisa memelihara solusi ini tanpa menebak-nebak?

Penutup

Query lambat saat data membesar jarang selesai dengan satu resep tunggal. Anda perlu menggabungkan pembacaan EXPLAIN, desain index yang sesuai pola akses, dan strategi pagination yang tidak boros kerja. Index membantu jika selaras dengan filter dan urutan; OFFSET besar sering jadi masalah; keyset pagination biasanya lebih sehat untuk data besar; dan COUNT(*) harus dipakai seperlunya.

Yang tak kalah penting, optimasi database harus tetap mudah dipahami tim. Solusi yang performanya baik tetapi sulit dijelaskan biasanya akan jadi beban di kemudian hari. Pilih pendekatan yang alasan teknisnya jelas, trade-off-nya diketahui, dan aman dipelihara ketika sistem terus tumbuh.

Quick Search

shopping_cart Keranjang