Strategi Indeks Komposit untuk Filter dan Sort SQL Skala Besar

Jika query list di endpoint API semakin lambat saat jumlah data naik, penyebabnya sering bukan sekadar ukuran tabel, tetapi ketidakcocokan antara pola query dan indeks. Kasus yang paling umum adalah query dengan kombinasi WHERE dan ORDER BY yang masih mengandalkan indeks tunggal terpisah, sehingga database tetap melakukan scan besar, sort tambahan, atau pagination yang mahal.

Strategi indeks komposit untuk filter dan sort SQL skala besar adalah merancang satu indeks yang mengikuti pola akses query: kolom filter utama, lalu kolom pengurutan, dan bila perlu kolom tie-breaker agar hasil stabil. Pendekatan ini sering jauh lebih efektif daripada menambahkan banyak indeks tunggal yang tampak masuk akal tetapi tidak membantu query list secara end-to-end.

Masalah yang Paling Sering Muncul pada Query List

Pada tabel kecil, query seperti di bawah ini mungkin tetap terasa cepat:

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

Namun saat data tumbuh menjadi jutaan baris, gejala berikut mulai muncul:

Full scan: database membaca terlalu banyak baris sebelum menemukan hasil yang relevan.
Filesort / explicit sort: hasil filter harus diurutkan lagi karena indeks tidak mendukung urutan yang diminta.
Pagination endpoint berat: OFFSET besar membuat database tetap melewati banyak baris sebelum mengembalikan page saat ini.
Latency tidak stabil: query kadang cepat, kadang sangat lambat tergantung distribusi data dan parameter filter.

Masalahnya bukan sekadar “belum ada indeks”, tetapi seringnya indeks yang ada tidak cocok dengan urutan akses query.

Mengapa Indeks Tunggal Sering Tidak Cukup

Misalnya Anda punya dua indeks tunggal:

CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Secara intuitif, ini terlihat cukup untuk query WHERE status = ... ORDER BY created_at. Dalam praktiknya, database sering tetap harus memilih salah satu jalur utama:

Menggunakan indeks status untuk memfilter, lalu melakukan sort terhadap hasilnya.
Menggunakan indeks created_at untuk urutan, lalu memeriksa filter satu per satu.
Melakukan scan yang lebih mahal jika planner menilai dua opsi di atas sama-sama buruk.

Itulah sebabnya indeks tunggal tidak otomatis bisa digabung efektif untuk semua query. Beberapa engine dapat melakukan kombinasi indeks pada kondisi tertentu, tetapi itu tidak berarti hasilnya optimal untuk query list besar dengan sort dan pagination.

Untuk query semacam ini, indeks komposit biasanya lebih tepat:

CREATE INDEX idx_orders_tenant_status_created_at
ON orders(tenant_id, status, created_at);

Indeks ini membantu database menemukan subset data yang sesuai filter, sekaligus membacanya dalam urutan yang sudah sesuai atau mendekati ORDER BY.

Prinsip Dasar Merancang Indeks Komposit

1. Mulai dari pola query nyata, bukan dari daftar kolom populer

Jangan mendesain indeks berdasarkan tebakan seperti “kolom ini sering dipakai”. Mulailah dari query yang benar-benar berjalan di production:

Kolom apa yang ada di WHERE?
Mana yang equality, mana yang range?
Apa ORDER BY-nya?
Apakah ada LIMIT dan pagination?
Apakah query ini benar-benar hot path?

Indeks komposit harus menjawab pola akses spesifik, bukan sekadar menambah indeks sebanyak mungkin.

2. Urutan kolom indeks sangat penting

Pada indeks komposit, urutan kolom bukan kosmetik. Indeks berikut:

(tenant_id, status, created_at)

berbeda perilakunya dengan:

(created_at, tenant_id, status)

Secara umum, untuk query list dengan filter dan sort, urutan yang sering efektif adalah:

Kolom filter equality yang paling konsisten dipakai.
Kolom filter tambahan yang masih mempersempit hasil.
Kolom ORDER BY.
Kolom tie-breaker, misalnya id, bila perlu urutan stabil.

Contoh:

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

Indeks yang sering cocok:

CREATE INDEX idx_orders_tenant_status_created_id
ON orders(tenant_id, status, created_at, id);

Mengapa demikian?

tenant_id dan status mempersempit pencarian lebih dulu.
created_at mendukung pengurutan hasil.
id membantu urutan deterministik saat ada banyak baris dengan timestamp sama.

3. Pahami pengaruh selectivity

Selectivity adalah seberapa baik suatu kolom menyaring data. Kolom dengan nilai sangat beragam biasanya lebih selektif daripada kolom dengan sedikit variasi.

Contoh:

tenant_id bisa cukup selektif jika data multi-tenant.
status sering kurang selektif jika hanya memiliki beberapa nilai seperti pending, paid, cancelled.
created_at bisa membantu sort, tetapi bukan berarti harus selalu di depan indeks.

Kesalahan umum adalah menaruh kolom selektivitas rendah di paling depan hanya karena kolom itu selalu ada di filter. Hasilnya, indeks tetap harus membaca bagian besar dari tree sebelum menemukan subset yang tepat.

Aturan praktis: untuk query dengan beberapa kondisi equality, letakkan kolom yang konsisten dipakai dan cukup mempersempit data di depan. Lalu letakkan kolom pengurutan setelah filter yang dominan.

4. Hati-hati dengan kolom range

Jika query mengandung kondisi range seperti:

WHERE tenant_id = 42
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC

maka indeks seperti (tenant_id, created_at) sering relevan. Namun jika ada banyak kolom setelah kolom range, kemampuan indeks untuk membantu bagian selanjutnya bisa berkurang tergantung engine dan bentuk query.

Karena itu, desain indeks untuk query dengan range harus diuji lewat EXPLAIN, bukan hanya mengikuti aturan hafalan.

Contoh Desain Indeks dari Query Nyata

Kasus 1: Filter equality + sort timestamp

SELECT id, user_id, status, created_at
FROM invoices
WHERE account_id = 9001
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 100;

Kandidat indeks:

CREATE INDEX idx_invoices_account_status_created_at
ON invoices(account_id, status, created_at);

Alasannya:

account_id dan status dipakai untuk menyaring.
created_at dipakai untuk urutan.
Query dapat mengambil hasil terbaru tanpa sort besar terhadap subset data.

Kasus 2: Multi-tenant list dengan tie-breaker stabil

SELECT id, tenant_id, priority, created_at, title
FROM tickets
WHERE tenant_id = 77
  AND priority = 'high'
ORDER BY created_at DESC, id DESC
LIMIT 50;

Kandidat indeks:

CREATE INDEX idx_tickets_tenant_priority_created_id
ON tickets(tenant_id, priority, created_at, id);

Ini berguna terutama jika endpoint list memerlukan hasil yang stabil antar-page. Tanpa tie-breaker, urutan pada data dengan timestamp sama bisa berubah.

Kasus 3: Jangan memaksa satu indeks untuk semua query

Misalnya ada dua query utama:

-- Query A
WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC

-- Query B
WHERE tenant_id = ? AND assignee_id = ? ORDER BY updated_at DESC

Satu indeks besar seperti berikut belum tentu bagus:

(tenant_id, status, assignee_id, created_at, updated_at)

Lebih realistis menyiapkan dua indeks yang sesuai akses utama masing-masing:

(tenant_id, status, created_at)
(tenant_id, assignee_id, updated_at)

Menggabungkan terlalu banyak kebutuhan ke satu indeks sering menghasilkan indeks yang tidak optimal untuk keduanya.

Apa yang Perlu Dicek di EXPLAIN

Setelah merancang indeks, jangan berhenti di DDL. Lihat rencana eksekusi query dengan EXPLAIN atau alat setara di database Anda.

Contoh umum:

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

Hal-hal penting yang perlu diperiksa

Jenis akses: hindari scan penuh tabel bila query seharusnya bisa menggunakan indeks.
Indeks yang dipilih: pastikan planner benar-benar memilih indeks komposit yang Anda buat.
Estimasi jumlah baris: jika masih sangat besar, indeks mungkin belum cukup selektif.
Sort tambahan: perhatikan indikasi adanya filesort, explicit sort, atau operasi pengurutan terpisah.
Filter residual: cek apakah sebagian kondisi masih disaring setelah membaca banyak data dari indeks.

Secara umum, Anda ingin melihat pola seperti:

Database menggunakan indeks yang sesuai.
Jumlah baris yang dibaca turun signifikan.
Pengurutan tambahan berkurang atau hilang.
LIMIT dapat berhenti lebih cepat tanpa memindai terlalu banyak baris.

Contoh interpretasi sederhana

Jika sebelum penambahan indeks query menunjukkan scan besar dan sort terpisah, lalu setelah indeks komposit planner membaca subset lebih kecil tanpa sort besar, itu tanda perbaikan yang benar.

Sebaliknya, jika indeks baru tetap tidak dipilih, beberapa kemungkinan penyebabnya:

Urutan kolom indeks tidak sesuai dengan pola query.
Filter kurang selektif.
Statistik database belum cukup representatif.
Query mengambil terlalu banyak kolom sehingga biaya akses tabel tetap tinggi.

Langkah Praktis Mengidentifikasi Bottleneck

1. Temukan query list yang paling mahal

Fokus pada query yang:

Sering dieksekusi.
Latency p95/p99 tinggi.
Muncul di endpoint list, dashboard, admin panel, atau export ringan.
Melambat seiring pertumbuhan data.

Jangan mulai dari semua query sekaligus. Pilih satu query yang paling berdampak.

2. Catat bentuk query yang sebenarnya

Gunakan query nyata dari aplikasi, termasuk parameter yang umum muncul. Indeks yang cocok untuk status='paid' belum tentu cukup baik untuk pola parameter lain bila distribusi datanya berbeda.

3. Jalankan EXPLAIN sebelum perubahan

Simpan baseline:

Indeks apa yang dipakai saat ini.
Apakah ada full scan.
Apakah ada filesort atau sort tambahan.
Berapa estimasi baris yang dibaca.

4. Rancang satu indeks komposit yang paling relevan

Jangan langsung menambah banyak indeks. Buat satu kandidat terbaik berdasarkan query prioritas.

5. Uji ulang dengan EXPLAIN dan pengukuran nyata

Bandingkan sebelum dan sesudah. Jika memungkinkan, ukur juga latency di staging dengan dataset yang representatif, bukan hanya database kosong.

Pagination: Mengapa LIMIT/OFFSET Sering Tetap Berat

Banyak endpoint list menggunakan:

SELECT id, created_at, status
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Walaupun sudah ada indeks, OFFSET besar tetap mahal karena database biasanya harus melewati banyak baris sebelum mengambil 50 baris berikutnya.

Indeks komposit tetap penting karena mengurangi biaya scan dan sort, tetapi untuk skala besar, pertimbangkan keyset pagination atau seek pagination:

SELECT id, created_at, status
FROM orders
WHERE tenant_id = 42
  AND (created_at, id) < ('2025-01-10 12:00:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Pendekatan ini cocok dipasangkan dengan indeks:

CREATE INDEX idx_orders_tenant_created_id
ON orders(tenant_id, created_at, id);

Keuntungannya:

Tidak perlu melewati ribuan baris seperti pada OFFSET besar.
Lebih stabil untuk feed, activity log, dan daftar transaksional.
Lebih konsisten saat data baru terus masuk.

Keterbatasannya:

Tidak sefleksibel offset untuk loncat ke page arbitrer.
Implementasi API sedikit lebih kompleks karena butuh cursor.

Strategi Validasi Setelah Menambah Indeks

Menambah indeks bukan akhir proses. Anda perlu memastikan indeks benar-benar membantu produksi dan tidak hanya terlihat bagus di atas kertas.

Checklist validasi

Lihat EXPLAIN lagi: pastikan indeks digunakan.
Bandingkan latency: ukur query sebelum dan sesudah pada dataset yang realistis.
Periksa row scan: idealnya jumlah baris yang dibaca turun.
Pantau endpoint: lihat apakah p95/p99 membaik, bukan hanya rata-rata.
Evaluasi write path: insert/update/delete bisa menjadi lebih mahal setelah indeks ditambah.

Tanda bahwa indeks baru mungkin tidak efektif

Planner tetap memilih indeks lama atau scan penuh.
Sort tambahan masih terjadi dalam volume besar.
Latency nyaris tidak berubah.
Write throughput turun padahal query read yang dibantu tidak terlalu penting.

Trade-off yang Wajib Dipertimbangkan

1. Overhead pada write

Setiap indeks tambahan harus diperbarui saat INSERT, UPDATE, atau DELETE. Jika tabel sangat aktif ditulis, terlalu banyak indeks akan memperlambat write path.

2. Konsumsi storage

Indeks komposit pada tabel besar bisa memakan storage signifikan. Semakin banyak kolom, semakin besar ukuran indeks.

3. Indeks berlebihan membuat planner punya lebih banyak pilihan

Terlalu banyak indeks mirip dapat menyulitkan pemeliharaan dan terkadang membuat planner memiliki banyak alternatif yang tidak semuanya berguna. Ini bukan berarti planner buruk, tetapi desain indeks yang berantakan meningkatkan kompleksitas operasional.

4. Tidak semua query layak dibuatkan indeks khusus

Jika query jarang dipakai atau bukan bagian dari jalur kritis, biaya indeks tambahan bisa lebih besar daripada manfaatnya. Prioritaskan query yang benar-benar penting.

Kesalahan Umum Saat Mendesain Indeks Komposit

Mengandalkan banyak indeks tunggal untuk query yang butuh filter dan sort sekaligus.
Salah urutan kolom di indeks komposit.
Menaruh semua kolom populer ke satu indeks tanpa mempertimbangkan pola query.
Tidak mengecek selectivity sehingga indeks tetap membaca area data yang terlalu besar.
Tidak menguji dengan EXPLAIN dan pengukuran nyata.
Mengabaikan pagination, padahal OFFSET besar tetap mahal walau indeks sudah lebih baik.

Panduan Praktis Memilih antara Indeks Tunggal dan Komposit

Pilih indeks tunggal jika:

Query benar-benar hanya memfilter satu kolom.
Kolom tersebut dipakai oleh banyak query sederhana yang berbeda.
Tidak ada kebutuhan sort yang konsisten pada hot path.

Pilih indeks komposit jika:

Query list utama selalu menggabungkan WHERE dan ORDER BY.
Anda ingin mengurangi scan besar dan sort tambahan.
Endpoint pagination menjadi bottleneck saat data membesar.
Pola query relatif stabil dan layak dioptimasi khusus.

Kesimpulan

Untuk query list pada tabel besar, performa biasanya turun bukan karena jumlah data semata, tetapi karena indeks tidak mengikuti pola akses query. Strategi indeks komposit untuk filter dan sort SQL skala besar adalah menyusun indeks berdasarkan urutan filter yang dominan, selectivity yang masuk akal, lalu kolom ORDER BY yang dibutuhkan query.

Jangan puas hanya karena “sudah ada indeks”. Periksa apakah query masih full scan, masih melakukan filesort, dan masih berat pada pagination. Gunakan EXPLAIN untuk memverifikasi rencana eksekusi, ukur hasilnya setelah perubahan, dan pertimbangkan trade-off pada write serta storage. Indeks yang tepat bisa memangkas kerja database secara signifikan, tetapi indeks yang salah hanya menambah biaya tanpa menyelesaikan bottleneck utama.

Quick Search

shopping_cart Keranjang