Index Komposit untuk Query Feed yang Melambat saat Data Membesar

Masalah yang sering muncul pada backend production adalah query feed atau listing awalnya terasa ringan, lalu makin lambat seiring pertumbuhan data. Pola yang paling umum adalah filter dengan WHERE, diikuti ORDER BY created_at atau ORDER BY id, lalu dibatasi dengan LIMIT. Dalam pola ini, index tunggal sering tidak cukup karena database tetap harus memindai banyak baris atau melakukan pengurutan tambahan sebelum menemukan sedikit hasil yang diminta.

Solusi yang sering tepat adalah index komposit yang mengikuti pola akses query: kolom filter lebih dulu, lalu kolom pengurutan. Dengan index yang selaras dengan WHERE + ORDER BY + LIMIT, database lebih mungkin mengambil baris yang relevan langsung dari urutan index, tanpa scan besar dan tanpa sort terpisah.

Mengapa query feed melambat saat data tumbuh

Feed biasanya mengambil data terbaru dengan syarat tertentu, misalnya hanya item berstatus aktif, hanya milik tenant tertentu, atau hanya kategori tertentu. Saat tabel masih kecil, scan tambahan belum terasa. Namun ketika jumlah baris membesar, biaya berikut mulai dominan:

Scan besar: database membaca jauh lebih banyak baris daripada yang akhirnya dikembalikan.
Sort tambahan: hasil filter diurutkan dulu sebelum LIMIT diterapkan.
Akses bolak-balik ke tabel: jika index tidak cukup membantu, engine perlu sering mengambil data dari baris utama.

Contoh pola query yang umum:

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

Secara sekilas query ini sederhana. Tetapi jika hanya ada index pada status atau hanya pada created_at, database sering masih harus memilih salah satu kompromi:

memakai index status lalu mengurutkan hasilnya, atau
memakai index created_at lalu menyaring banyak baris yang statusnya tidak cocok.

Keduanya bisa buruk pada skala besar.

Kenapa index tunggal sering tidak cukup

Kasus 1: hanya ada index pada kolom filter

CREATE INDEX idx_posts_status ON posts(status);

Untuk query berikut:

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

Database memang lebih cepat menemukan semua baris dengan status = 'published'. Namun setelah itu, hasil tersebut masih perlu diurutkan berdasarkan created_at. Jika jumlah baris dengan status tersebut besar, proses sort tetap mahal.

Kasus 2: hanya ada index pada kolom pengurutan

CREATE INDEX idx_posts_created_at ON posts(created_at);

Database bisa membaca data dalam urutan waktu. Tetapi karena ada filter status = 'published', engine mungkin harus memeriksa banyak entri index dan membuang banyak baris sebelum menemukan 20 hasil yang cocok. Jika distribusi datanya tidak menguntungkan, ini tetap lambat.

Masalah inti

Pola WHERE status = ... ORDER BY created_at DESC LIMIT 20 memerlukan dua hal sekaligus:

menyaring data ke subset yang relevan, dan
membaca subset itu dalam urutan yang sudah sesuai.

Index komposit dibutuhkan ketika satu index perlu melayani filter dan urutan secara berurutan dalam struktur yang sama.

Kapan memakai index komposit

Gunakan index komposit ketika query feed Anda berulang kali mengikuti pola seperti ini:

WHERE status = ? ORDER BY created_at DESC LIMIT ?
WHERE tenant_id = ? AND status = ? ORDER BY id DESC LIMIT ?
WHERE category_id = ? ORDER BY created_at DESC LIMIT ?

Contoh perbaikan yang umum:

CREATE INDEX idx_posts_status_created_at ON posts(status, created_at);

Dengan index tersebut, database dapat lebih mudah:

masuk ke bagian index untuk status = 'published',
membaca entri dalam urutan created_at,
berhenti cepat setelah menemukan LIMIT 20.

Untuk query yang menggunakan id sebagai urutan:

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

Index yang relevan biasanya:

CREATE INDEX idx_posts_status_id ON posts(status, id);

Prinsip praktis: jika query selalu memfilter dulu lalu mengambil data terbaru, index komposit umumnya mengikuti pola kolom filter, lalu kolom order.

Menentukan urutan kolom pada index komposit

Urutan kolom pada index komposit sangat penting. (status, created_at) berbeda dari (created_at, status). Untuk pola feed yang dibahas di sini, urutan biasanya ditentukan oleh cara query mempersempit ruang pencarian.

Aturan praktis yang paling sering benar

Letakkan kolom dengan kondisi = atau filter tetap di depan, lalu kolom ORDER BY di belakang.

WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20

Maka bentuk yang lazim adalah:

(status, created_at)

Bagaimana jika filternya lebih dari satu?

Misalnya:

SELECT id, title, created_at
FROM posts
WHERE tenant_id = 42 AND status = 'published'
ORDER BY created_at DESC
LIMIT 20;

Maka kandidat index:

CREATE INDEX idx_posts_tenant_status_created_at
ON posts(tenant_id, status, created_at);

Kenapa seperti itu? Karena query terlebih dahulu mempersempit data ke tenant tertentu, lalu ke status tertentu, lalu membutuhkan urutan terbaru di dalam subset itu.

Peran cardinality dan selectivity

Cardinality mengacu pada banyaknya nilai unik relatif terhadap jumlah data. Selectivity mengacu pada seberapa baik suatu kondisi mengurangi jumlah baris yang harus dibaca.

Contoh:

status sering memiliki cardinality rendah: misalnya hanya published, draft, archived.
tenant_id atau user_id biasanya lebih selektif karena variasinya lebih besar.
created_at sering dipakai untuk urutan, bukan selalu untuk menyaring jumlah baris secara drastis.

Jika ada beberapa kolom filter, kolom yang lebih selektif sering lebih berguna diletakkan lebih depan, selama tetap sesuai pola query. Misalnya untuk query multi-tenant, (tenant_id, status, created_at) sering lebih masuk akal daripada (status, tenant_id, created_at), karena tenant_id biasanya jauh lebih mempersempit data.

Namun jangan menerapkan aturan ini secara buta. Yang paling penting adalah mencocokkan index dengan pola query nyata, bukan menebak dari teori saja.

Contoh query buruk vs perbaikan

Contoh 1: hanya index tunggal, hasil tetap lambat

SELECT id, author_id, created_at
FROM feed_items
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 50;

Index yang ada:

CREATE INDEX idx_feed_items_status ON feed_items(status);
CREATE INDEX idx_feed_items_created_at ON feed_items(created_at);

Masalah yang mungkin terjadi:

database memakai index status, lalu mengurutkan banyak hasil, atau
database memakai index created_at, lalu menyaring banyak baris yang statusnya tidak cocok.

Perbaikan:

CREATE INDEX idx_feed_items_status_created_at
ON feed_items(status, created_at);

Jika query selalu membaca beberapa kolom kecil yang sama, sebagian database juga bisa mendapat manfaat dari index yang lebih “menutupi” kebutuhan baca. Tetapi jangan buru-buru menambahkan terlalu banyak kolom ke index, karena ukuran index dan biaya tulis akan meningkat.

Contoh 2: filter tambahan membuat index lama tidak lagi cukup

SELECT id, title, created_at
FROM feed_items
WHERE tenant_id = 7
  AND status = 'active'
ORDER BY created_at DESC
LIMIT 20;

Jika sebelumnya hanya ada index (status, created_at), query ini mungkin masih kurang efisien untuk data multi-tenant besar. Kandidat yang lebih tepat:

CREATE INDEX idx_feed_items_tenant_status_created_at
ON feed_items(tenant_id, status, created_at);

Ini membantu engine mengambil data terbaru khusus untuk tenant dan status yang diminta, bukan dari himpunan yang lebih luas.

Contoh 3: urutan berdasarkan id

SELECT id, payload
FROM notifications
WHERE user_id = 123
  AND is_read = false
ORDER BY id DESC
LIMIT 30;

Index yang relevan sering berupa:

CREATE INDEX idx_notifications_user_read_id
ON notifications(user_id, is_read, id);

Pola ini umum pada notifikasi, activity log, atau event list yang selalu mengambil entri terbaru dalam subset tertentu.

Dampak pada filesort dan scan besar

Istilah implementasi internal berbeda-beda antar database, tetapi secara umum ada dua gejala yang ingin dihindari:

scan besar: terlalu banyak baris atau entri index dibaca sebelum hasil ditemukan,
sort terpisah: hasil filter dikumpulkan lalu diurutkan di luar urutan index.

Ketika index komposit cocok dengan pola query, database bisa lebih dekat ke strategi ideal:

loncat ke bagian index yang sesuai dengan filter,
membaca data dalam urutan yang benar,
berhenti segera saat LIMIT terpenuhi.

Inilah alasan kenapa peningkatannya bisa terasa besar pada query feed: bukan karena query menjadi “ajaib”, tetapi karena jumlah pekerjaan yang dilakukan database berkurang drastis.

Cara membaca EXPLAIN secara umum

Sebelum dan sesudah menambah index, jalankan EXPLAIN pada query target. Nama kolom output berbeda antar vendor, jadi fokuslah pada sinyal umum berikut:

1. Index apa yang dipilih

Lihat apakah database benar-benar memakai index yang Anda harapkan. Jika tidak, bisa jadi:

urutan kolom index tidak cocok,
selectivity rendah sehingga optimizer memilih jalur lain,
query mengambil terlalu banyak kolom sehingga biaya akses tabel tetap besar.

2. Perkiraan jumlah baris yang dibaca

Semakin sedikit baris yang perlu diproses untuk menghasilkan LIMIT kecil, umumnya semakin baik. Jika LIMIT 20 tetapi estimasi pembacaan tetap sangat besar, index kemungkinan belum tepat.

3. Apakah ada operasi sort terpisah

Jika rencana eksekusi menunjukkan pengurutan tambahan, itu tanda ORDER BY belum dilayani efektif oleh index. Pada query feed, ini sering menjadi sumber latensi ketika data membesar.

4. Apakah filter diterapkan terlalu belakangan

Jika database tampak membaca banyak data dalam urutan tertentu lalu baru membuang sebagian besar baris karena filter, pertimbangkan menaruh kolom filter yang lebih tepat di depan index komposit.

Bandingkan EXPLAIN sebelum dan sesudah perubahan. Jangan hanya melihat bahwa sebuah index “dipakai”; lihat juga apakah jumlah pekerjaan benar-benar turun.

Checklist audit sebelum menambah index

Menambah index sering membantu, tetapi jangan langsung membuat banyak index tanpa audit. Gunakan checklist ini:

Identifikasi query nyata
Ambil contoh query dari log aplikasi, slow query log, APM, atau observability database. Fokus pada query yang benar-benar sering dan mahal.
Pastikan pola stabil
Jika query feed konsisten memakai WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC LIMIT ?, index komposit lebih mudah dibenarkan.
Cek index yang sudah ada
Hindari membuat index baru yang tumpang tindih tanpa manfaat jelas. Kadang index lama bisa diganti dengan satu index komposit yang lebih tepat.
Lihat distribusi data
Apakah status terlalu umum? Apakah tenant_id jauh lebih selektif? Apakah sebagian besar data berada pada satu nilai tertentu?
Periksa EXPLAIN
Bandingkan rencana eksekusi, jumlah baris yang diperkirakan, dan indikasi sort tambahan.
Uji beban tulis
Setiap index menambah biaya INSERT, UPDATE, dan kadang DELETE. Pada tabel dengan write rate tinggi, ini penting.
Evaluasi ukuran index
Index memakan storage dan cache. Terlalu banyak index dapat mengganggu efisiensi memori.
Pastikan query memilih kolom seperlunya
SELECT * sering memperburuk I/O. Ambil hanya kolom yang dibutuhkan feed.

Trade-off: biaya tulis dan ukuran index

Index komposit bukan gratis. Semakin banyak index, semakin besar biaya pemeliharaan saat data berubah.

Dampak yang perlu diperhatikan

INSERT lebih mahal: setiap baris baru harus dimasukkan ke beberapa struktur index.
UPDATE bisa lebih mahal: terutama jika kolom yang di-index ikut berubah.
Storage bertambah: index komposit dengan beberapa kolom tentu lebih besar daripada index tunggal sederhana.
Cache pressure: lebih banyak halaman index berarti lebih banyak persaingan untuk ruang memori.

Karena itu, pilih index berdasarkan query yang paling bernilai bagi sistem, bukan semua kemungkinan query. Untuk backend production, lebih baik punya sedikit index yang benar-benar tepat sasaran daripada banyak index yang hanya kadang dipakai.

Kapan keyset pagination membantu

Jika feed selalu mengambil halaman berikutnya berdasarkan item terakhir yang sudah dilihat, keyset pagination sering membantu mengurangi pekerjaan database, terutama pada dataset besar. Ide dasarnya adalah melanjutkan dari posisi terakhir, misalnya berdasarkan created_at atau id, bukan meminta database menghitung dan melewati sejumlah besar baris.

Contoh pola umum:

SELECT id, title, created_at
FROM posts
WHERE status = 'published'
  AND created_at < :last_seen_created_at
ORDER BY created_at DESC
LIMIT 20;

Atau jika memakai penanda unik yang stabil:

SELECT id, title
FROM posts
WHERE status = 'published'
  AND id < :last_seen_id
ORDER BY id DESC
LIMIT 20;

Dalam kasus seperti ini, index komposit tetap penting. Misalnya (status, created_at) atau (status, id) masih relevan karena query tetap membutuhkan filter dan urutan yang konsisten.

Keyset pagination tidak menggantikan kebutuhan index yang benar; ia hanya membuat pola akses menjadi lebih efisien untuk navigasi berurutan pada feed besar.

Kesalahan yang sering terjadi

Menganggap semua ORDER BY otomatis cepat jika kolomnya sudah di-index
Tidak cukup jika query juga punya filter yang besar dan index tidak selaras.
Membuat index komposit dengan urutan kolom terbalik
Misalnya membuat (created_at, status) untuk query yang terutama menyaring status lalu mengambil terbaru.
Menduplikasi index tanpa evaluasi
Terlalu banyak index mirip hanya menambah biaya tulis dan storage.
Tidak memeriksa query nyata
Optimasi berdasarkan asumsi sering meleset jika pola akses aplikasi berbeda dari yang dibayangkan.
Memilih kolom dengan selectivity rendah sebagai satu-satunya strategi
Kolom seperti status bisa tetap berguna, tetapi jarang cukup sendirian untuk feed besar.

Ringkasan keputusan praktis

Untuk query feed/listing yang mengikuti pola WHERE status/filter + ORDER BY created_at/id + LIMIT, gunakan pendekatan berikut:

Mulai dari query yang paling sering dan paling lambat.
Cocokkan index dengan pola akses sebenarnya, bukan sekadar menambahkan index tunggal pada tiap kolom.
Untuk filter kesetaraan lalu urutan terbaru, pertimbangkan index komposit seperti (status, created_at), (status, id), atau jika multi-tenant (tenant_id, status, created_at).
Validasi dengan EXPLAIN: lihat index yang dipakai, jumlah baris yang dibaca, dan apakah sort tambahan berkurang.
Hitung trade-off pada write cost, ukuran index, dan index yang saling tumpang tindih.

Intinya, index komposit mempercepat query feed bukan karena menambah “lebih banyak index”, tetapi karena menyusun data sesuai cara query membaca data. Saat struktur index sejalan dengan WHERE + ORDER BY + LIMIT, database dapat berhenti lebih cepat, membaca lebih sedikit, dan menghindari sort mahal ketika tabel terus membesar.

Quick Search

shopping_cart Keranjang