Index Komposit untuk Query Feed yang Melambat saat Data Membesar

Masalah yang sering muncul di backend production adalah query feed atau listing yang awalnya terasa ringan, lalu makin lambat saat jumlah baris bertambah. Polanya hampir selalu mirip: ada filter di WHERE, lalu ORDER BY created_at atau id, dan akhirnya LIMIT untuk mengambil beberapa item teratas.

Pada kondisi ini, menambah index tunggal di setiap kolom sering tidak cukup. Database mungkin tetap harus membaca banyak baris, melakukan sort tambahan, atau memilih rencana eksekusi yang kurang efisien. Solusi yang sering tepat adalah index komposit yang disusun mengikuti pola query nyata, bukan sekadar menambahkan index per kolom.

Mengapa query feed melambat saat data tumbuh

Anggap ada tabel posts dengan kolom seperti status, created_at, id, author_id, dan lainnya. Query feed tipikal terlihat seperti ini:

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

Saat isi tabel masih sedikit, hampir semua pendekatan terasa cepat. Namun ketika baris bertambah besar, ada beberapa biaya yang mulai dominan:

Scan besar: database membaca terlalu banyak kandidat baris sebelum menemukan 20 hasil yang cocok.
Sort tambahan: jika urutan hasil tidak bisa dipenuhi dari index, database harus mengurutkan hasil sementara.
Akses acak ke tabel: jika index tidak cukup membantu, mesin perlu bolak-balik antara index dan data utama.

Masalahnya bukan hanya jumlah hasil akhir, tetapi berapa banyak baris yang harus disentuh sebelum LIMIT terpenuhi. LIMIT 20 tidak otomatis murah jika database tetap harus memeriksa ratusan ribu baris untuk menemukan 20 baris yang benar dan dalam urutan yang diminta.

Kenapa index tunggal sering tidak cukup

Kesalahan umum adalah membuat index terpisah seperti ini:

CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_created_at ON posts(created_at);
CREATE INDEX idx_posts_id ON posts(id);

Secara intuitif terlihat masuk akal: setiap kolom penting sudah punya index. Tetapi query feed tidak meminta kolom-kolom itu secara terpisah. Query meminta kombinasi:

filter berdasarkan status, lalu
urutkan berdasarkan created_at dan id, lalu
ambil beberapa baris teratas.

Index tunggal pada status bisa membantu menemukan semua baris berstatus published, tetapi jika jumlahnya sangat banyak, database masih perlu mengurutkannya berdasarkan created_at dan id. Sebaliknya, index tunggal pada created_at bisa membantu urutan, tetapi belum tentu efisien untuk menyaring status lebih dulu.

Karena itu, database sering tetap melakukan kombinasi kerja yang mahal: scan banyak baris + filesort/sort tambahan. Nama detailnya bisa berbeda antar vendor, tetapi gejalanya sama: rencana eksekusi menunjukkan pembacaan data besar atau pengurutan di luar index.

Kapan memakai index komposit

Index komposit layak dipertimbangkan ketika query produksi Anda berulang dengan pola tetap:

WHERE status = ?
ORDER BY created_at DESC, id DESC
LIMIT ?

Untuk pola seperti ini, index komposit yang umum cocok adalah:

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

Dengan susunan ini, database berpeluang:

menyaring baris berdasarkan status,
membaca baris dalam urutan created_at, id langsung dari index,
berhenti cepat setelah LIMIT terpenuhi.

Inilah alasan utama index komposit efektif untuk feed: ia menyelaraskan pola pencarian dengan pola pengurutan dalam satu struktur.

Contoh query buruk vs perbaikan

Query yang sering melambat:

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

Jika hanya ada index tunggal di status atau created_at, rencana eksekusi bisa tetap mahal.

Perbaikan yang umum:

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

Setelah itu, jalankan ulang EXPLAIN untuk melihat apakah database bisa menggunakan index tersebut untuk filtering dan ordering sekaligus.

Urutan kolom dalam index komposit

Urutan kolom pada index komposit bukan detail kecil. Susunan kolom sangat memengaruhi apakah index bisa dipakai efektif.

Aturan praktis untuk pola feed

Untuk query seperti:

WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20;

Susunan yang umumnya masuk akal adalah:

(status, created_at, id)

Alasannya:

status dipakai sebagai filter equality di awal.
created_at dipakai sebagai urutan utama feed.
id sering dipakai sebagai tie-breaker agar urutan stabil ketika banyak baris punya timestamp yang sama.

Jika Anda membalik menjadi (created_at, status, id), database mungkin bisa memanfaatkan urutan waktu, tetapi kemampuan untuk menyaring berdasarkan status secara efisien bisa menurun, tergantung query dan optimizer.

Mengapa kolom tie-breaker penting

Urutan hanya dengan created_at sering tidak cukup stabil karena banyak baris bisa memiliki nilai timestamp identik. Menambahkan id pada ORDER BY dan index membantu menghasilkan urutan deterministik:

ORDER BY created_at DESC, id DESC

Ini penting terutama untuk feed yang dibaca bertahap, cache halaman, atau pagination berbasis cursor.

Selectivity dan cardinality: jangan semua kolom ditaruh di depan

Dua konsep penting saat mendesain index adalah selectivity dan cardinality.

Cardinality secara sederhana menggambarkan banyaknya nilai unik pada suatu kolom.
Selectivity menggambarkan seberapa efektif suatu kondisi menyaring baris.

Kolom seperti status sering punya nilai unik sedikit, misalnya hanya draft, published, archived. Artinya cardinality-nya rendah. Apakah kolom seperti ini tetap layak ditaruh di depan index? Sering iya, jika query hampir selalu memfilter dengan equality pada kolom tersebut dan hasilnya lalu perlu diurutkan.

Namun ada nuansanya:

Jika hampir semua baris berstatus published, maka filter status = 'published' sendiri tidak terlalu selektif.
Meski begitu, menaruh status di depan index tetap bisa berguna jika query selalu membutuhkannya dan database bisa melanjutkan traversal index sesuai urutan created_at, id.
Jika filter utama Anda justru lebih selektif, misalnya tenant_id atau author_id, sering lebih tepat menaruh kolom itu lebih depan.

Contoh lain:

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

Untuk pola seperti ini, index yang lebih tepat sering menjadi:

(tenant_id, status, created_at, id)

Karena tenant_id biasanya membatasi ruang pencarian jauh lebih banyak daripada status.

Prinsip praktis: letakkan kolom filter yang stabil dan sering dipakai di depan, terutama yang mempersempit data paling banyak, lalu ikuti dengan kolom pengurutan yang dipakai query.

Dampak pada filesort dan scan besar

Tanpa index yang sesuai, database cenderung melakukan salah satu dari dua hal yang mahal:

Mengambil banyak kandidat lalu mengurutkannya.
Memindai index/heap yang tidak sesuai urutan filter hingga menemukan cukup baris.

Walaupun istilah internal tiap engine berbeda, gejala yang perlu Anda perhatikan adalah:

jumlah baris yang diperiksa jauh lebih besar daripada hasil akhir,
ada langkah sort terpisah,
waktu query naik seiring pertumbuhan tabel meskipun LIMIT tetap kecil.

Index komposit yang sesuai dapat mengurangi dua biaya sekaligus: memperkecil ruang pencarian dan menghindari sort tambahan karena baris sudah tersedia dalam urutan yang dibutuhkan.

Cara membaca EXPLAIN secara umum

Anda tidak perlu bergantung pada istilah vendor tertentu untuk mendapat gambaran awal. Saat menjalankan EXPLAIN, fokuslah pada pertanyaan berikut:

1. Index mana yang dipakai?

Pastikan query benar-benar menggunakan index komposit yang Anda buat, bukan index lain yang kurang cocok, apalagi full scan.

2. Berapa banyak baris yang diperkirakan dibaca?

Jika hasil akhir hanya 20 baris tetapi rencana menunjukkan pembacaan sangat besar, biasanya masih ada masalah pada filtering atau ordering.

3. Apakah ada langkah sort terpisah?

Jika iya, besar kemungkinan ORDER BY belum ter-cover dengan baik oleh index yang dipilih.

4. Apakah urutan kolom query cocok dengan index?

Periksa apakah kolom di WHERE dan ORDER BY sejalan dengan urutan index. Perbedaan kecil di sini sering menentukan apakah index efektif atau tidak.

5. Apakah estimasi masuk akal dibanding data nyata?

Optimizer bekerja dari statistik. Jika statistik stale atau distribusi data berubah, rencana eksekusi bisa buruk walau index tampak benar di atas kertas.

Contoh audit sederhana:

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

Tujuannya bukan menghafal output vendor tertentu, tetapi memastikan query tidak lagi melakukan scan dan sort yang tidak perlu.

Checklist audit sebelum menambah index

Menambah index itu murah dibanding outage performa, tetapi tetap ada biaya. Sebelum membuat index baru, cek hal berikut:

Apakah query lambat memang penting dan sering dipanggil?
Jangan optimasi query yang jarang dipakai.
Apakah pola query konsisten?
Index komposit efektif untuk pola query yang relatif tetap. Jika filternya berubah-ubah total, satu index mungkin tidak cukup.
Apakah sudah ada index mirip?
Hindari duplikasi seperti punya (status, created_at) dan (status, created_at, id) tanpa alasan jelas.
Apakah ORDER BY sudah deterministik?
Tambahkan kolom tie-breaker seperti id bila perlu.
Apakah kolom paling depan memang sering dipakai dalam WHERE?
Index komposit tidak selalu berguna jika query sering melewati prefix terdepannya.
Apakah SELECT mengambil kolom terlalu banyak?
Semakin banyak kolom yang diambil, semakin besar kemungkinan database tetap perlu membaca data tabel utama setelah menemukan kandidat di index.
Apakah statistik database cukup akurat?
Jika optimizer salah pilih index, masalahnya bisa ada pada statistik, bukan hanya desain index.
Apakah ada filter yang lebih selektif daripada status?
Misalnya tenant_id, user_id, atau category_id.

Trade-off: write cost dan ukuran index

Index bukan gratis. Setiap index baru membawa konsekuensi:

INSERT lebih mahal karena index harus ikut diperbarui.
UPDATE lebih mahal terutama jika mengubah kolom yang ada dalam index.
DELETE juga berdampak karena entri index perlu dibersihkan.
Ukuran penyimpanan bertambah dan cache memory bisa tertekan.

Karena itu, jangan membuat banyak index komposit yang hampir sama untuk setiap variasi query kecil. Pilih berdasarkan query production yang paling kritis dan paling sering terjadi.

Contoh keputusan yang buruk:

(status, created_at)
(status, created_at, id)
(status, id)
(created_at, id)
(status, author_id, created_at, id)

Jika semuanya dibuat tanpa audit, biaya write dan ukuran index bisa membengkak. Lebih baik pilih beberapa index yang benar-benar melayani pola query utama.

Kapan keyset pagination membantu

Untuk feed yang dibaca berurutan ke bawah, keyset pagination sering membantu menjaga biaya tetap stabil karena query melanjutkan dari posisi terakhir yang sudah diketahui, bukan selalu mulai dari awal hasil.

Contoh pola yang umum:

SELECT id, title, created_at
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;

Pola ini sangat cocok jika Anda sudah punya index komposit seperti (status, created_at, id). Database dapat melanjutkan traversal index dari posisi tertentu, sehingga tidak perlu terus-menerus menelusuri bagian awal hasil.

Namun keyset pagination bukan pengganti semua masalah index. Jika index-nya salah, performa tetap bisa buruk. Keyset bekerja baik justru karena urutan query konsisten dan selaras dengan index.

Pola desain yang biasanya berhasil di production

Untuk query feed/listing dengan pola nyata:

WHERE status = ?
ORDER BY created_at DESC, id DESC
LIMIT ?

Mulailah dari pendekatan ini:

Pastikan ORDER BY stabil dengan created_at, id.
Buat index komposit yang mengikuti pola filter lalu urutan:
(status, created_at, id)
Jika ada filter yang lebih selektif dan selalu hadir, pertimbangkan letakkan lebih depan, misalnya:
(tenant_id, status, created_at, id)
Validasi dengan EXPLAIN dan pengukuran query nyata, bukan asumsi.

Jangan menganggap satu index tunggal per kolom akan otomatis cukup. Untuk query feed, yang paling penting adalah bagaimana database menemukan baris yang benar dalam urutan yang benar dengan jumlah pembacaan minimal.

Kesimpulan

Index komposit untuk query feed biasanya diperlukan ketika tabel membesar dan query mengikuti pola WHERE status/filter + ORDER BY created_at/id + LIMIT. Index tunggal sering gagal karena hanya membantu sebagian masalah: filtering saja atau sorting saja, tetapi bukan keduanya sekaligus.

Susun index berdasarkan pola query nyata, perhatikan urutan kolom, pahami selectivity, lalu cek kembali lewat EXPLAIN. Jika dilakukan dengan benar, Anda bisa mengurangi scan besar, menghindari sort tambahan, dan membuat performa feed lebih stabil saat data terus tumbuh.

Quick Search

shopping_cart Keranjang