Index Komposit untuk Query WHERE + ORDER BY yang Tetap Cepat

Index komposit sering menjadi solusi saat query dengan kombinasi WHERE dan ORDER BY awalnya cepat, lalu melambat seiring pertumbuhan tabel. Gejalanya biasanya terlihat pada halaman list admin, dashboard operasional, atau endpoint API pagination: CPU naik, latency bertambah, dan hasil EXPLAIN mulai menunjukkan full scan, filesort, atau index yang dipakai hanya sebagian.

Masalah ini jarang selesai dengan menambah banyak index tunggal. Jika pola query Anda adalah filter lalu urutkan, desain urutan kolom dalam index jauh lebih penting daripada sekadar jumlah index. Kuncinya adalah memahami pola query nyata, aturan leftmost prefix, dan batasan saat ada kondisi range yang bisa memutus efektivitas sorting dari index.

Mengapa query WHERE + ORDER BY melambat saat tabel membesar

Pada dataset kecil, database masih bisa terlihat cepat walaupun strategi aksesnya buruk. Saat baris bertambah, biaya membaca data yang tidak relevan dan biaya sorting menjadi jauh lebih mahal. Ini umum terjadi pada query seperti:

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

Di awal, query di atas mungkin terasa aman. Namun setelah data mencapai jutaan baris, ada beberapa kemungkinan buruk:

Database melakukan scan terlalu banyak baris karena index tidak cocok dengan filter.
Database menemukan baris dengan index tertentu, tetapi tetap perlu melakukan filesort untuk ORDER BY.
Database memilih index tunggal yang hanya membantu satu bagian query, misalnya filter saja atau sorting saja, tetapi tidak keduanya.
Database bahkan memilih full table scan karena perkiraan biayanya dianggap lebih murah daripada memakai index yang kurang selektif.

Dalam kasus list admin atau API feed, pola seperti ini sangat umum: filter berdasarkan tenant, status, atau flag aktif; lalu urutkan berdasarkan created_at, updated_at, atau id; kemudian ambil halaman pertama dengan LIMIT. Di sinilah index komposit paling berguna.

Cara membaca pola query sebelum membuat index

Jangan mulai dari nama kolom yang terlihat penting. Mulailah dari query yang benar-benar berjalan di aplikasi. Ambil query dari slow query log, APM, log SQL, atau endpoint yang paling sering dipanggil.

Fokus pada tiga bagian

Kolom filter exact match: misalnya tenant_id = ?, status = ?, is_deleted = false.
Kolom filter range: misalnya created_at > ?, price BETWEEN ? AND ?.
Kolom sorting: misalnya ORDER BY created_at DESC.

Contoh query nyata untuk halaman admin:

SELECT id, title, status, published_at
FROM articles
WHERE tenant_id = 10
  AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

Pola query ini berarti:

Filter exact match pada tenant_id dan status.
Sorting pada published_at.
Mengambil sedikit data di bagian atas hasil.

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

CREATE INDEX idx_articles_tenant_status_published_at
ON articles (tenant_id, status, published_at);

Mengapa urutannya seperti itu? Karena database dapat mempersempit baris dengan tenant_id dan status terlebih dahulu, lalu membaca hasil dalam urutan published_at tanpa sorting tambahan, selama optimizer dan bentuk query mendukungnya.

Prinsip urutan kolom: equality dulu, lalu ORDER BY, lalu pertimbangkan range

Aturan praktis yang paling sering berhasil untuk query list adalah:

Letakkan kolom equality filter di depan.
Lalu letakkan kolom yang dipakai untuk ORDER BY.
Hati-hati dengan kolom range, karena sering memutus kemampuan index untuk membantu sorting secara penuh.

Ini bukan aturan mutlak untuk semua mesin database dan semua query, tetapi sangat berguna sebagai titik awal audit.

Contoh query buruk: index tunggal tidak cukup

Misalkan Anda hanya punya dua index tunggal berikut:

CREATE INDEX idx_orders_tenant_id ON orders (tenant_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);

Lalu query:

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

Masalahnya:

Index tenant_id membantu filter tenant, tetapi tidak otomatis menyelesaikan sorting.
Index created_at membantu urutan, tetapi tidak efisien untuk membatasi ke tenant dan status tertentu.
Database umumnya tidak bisa menggabungkan dua index tunggal menjadi akses yang sama efisien dengan satu index komposit yang tepat untuk kasus ini.

Index yang lebih sesuai:

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

Dengan index ini, query lebih berpeluang membaca bagian index yang relevan untuk tenant_id = 42 dan status = 'paid', lalu mengambil 50 baris teratas sesuai urutan created_at tanpa filesort besar.

Kapan urutan kolom terbalik menjadi masalah

Kesalahan umum adalah menaruh kolom sorting terlalu depan:

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

Index ini tampak masuk akal karena query mengurutkan berdasarkan created_at, tetapi untuk query yang sangat bergantung pada filter tenant dan status, urutan ini sering buruk. Database harus menelusuri urutan created_at lebih dulu, lalu memeriksa banyak baris sampai menemukan tenant dan status yang cocok. Pada tabel besar, ini bisa mahal.

Memahami leftmost prefix

Leftmost prefix berarti database paling efektif memakai index komposit dari kolom paling kiri secara berurutan. Misalnya ada index:

(tenant_id, status, created_at)

Maka biasanya index tersebut efektif untuk pola seperti:

WHERE tenant_id = ?
WHERE tenant_id = ? AND status = ?
WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC

Namun index yang sama biasanya tidak ideal untuk:

WHERE status = ? ORDER BY created_at DESC
WHERE created_at > ? tanpa tenant_id

Karena kolom paling kiri tidak dipakai sesuai urutannya, manfaat index bisa turun drastis. Inilah alasan satu index komposit tidak selalu bisa melayani semua variasi query.

Jika aplikasi memiliki beberapa pola query yang berbeda jauh, sering kali Anda memang membutuhkan lebih dari satu index komposit. Hindari asumsi bahwa satu index besar akan menyelesaikan semuanya.

Range filter bisa memutus efektivitas sorting

Ini salah satu jebakan paling penting. Misalkan query Anda:

SELECT id, tenant_id, status, created_at
FROM orders
WHERE tenant_id = 42
  AND created_at >= '2025-01-01'
ORDER BY status, created_at DESC
LIMIT 50;

Atau contoh lain:

SELECT id, created_at
FROM orders
WHERE tenant_id = 42
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

Range pada created_at tidak selalu bermasalah jika sekaligus menjadi kolom sorting yang sesuai dengan susunan index, tetapi begitu ada kombinasi kolom lain setelah range, optimizer bisa kehilangan kemampuan untuk memakai sisa urutan index secara penuh. Secara praktis, setelah database masuk ke pencarian range pada suatu kolom index, manfaat kolom-kolom sesudahnya untuk filtering/sorting sering berkurang atau hilang, tergantung mesin dan bentuk query.

Contoh yang lebih jelas:

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

Lalu query:

SELECT id, tenant_id, status, created_at
FROM orders
WHERE tenant_id = 42
  AND created_at >= '2025-01-01'
ORDER BY status, created_at DESC
LIMIT 50;

Karena ada range pada created_at, index di atas belum tentu bisa membantu sorting ORDER BY status, created_at dengan baik. Hasilnya bisa tetap filesort.

Pelajaran praktisnya:

Jangan menganggap semua kolom dalam index komposit akan selalu aktif bersamaan.
Jika ada kondisi range, audit dengan EXPLAIN sangat penting.
Untuk query feed/list, kadang lebih efektif memakai pola pagination berbasis cursor dengan kolom urut yang stabil daripada offset besar plus range yang rumit.

Contoh buruk vs contoh yang membaik

Kasus 1: list admin artikel

Query awal:

SELECT id, title, status, published_at
FROM articles
WHERE tenant_id = 10 AND status = 'published'
ORDER BY published_at DESC
LIMIT 20 OFFSET 0;

Index yang ada:

CREATE INDEX idx_articles_tenant_id ON articles (tenant_id);
CREATE INDEX idx_articles_status ON articles (status);
CREATE INDEX idx_articles_published_at ON articles (published_at);

Gejala:

Halaman pertama masih terasa cepat saat data kecil.
Setelah data membesar, EXPLAIN menunjukkan banyak baris diperiksa.
Muncul indikasi sorting tambahan atau index yang dipilih tidak konsisten.

Perbaikan:

CREATE INDEX idx_articles_tenant_status_published_at
ON articles (tenant_id, status, published_at);

Mengapa membaik:

Filter exact match dipenuhi lebih dulu.
Urutan hasil sudah sejalan dengan index.
LIMIT 20 menjadi lebih murah karena database bisa berhenti lebih cepat setelah menemukan sejumlah baris teratas yang cocok.

Kasus 2: endpoint API order terbaru per tenant

Query buruk:

SELECT id, order_number, total, created_at
FROM orders
WHERE tenant_id = 99
ORDER BY created_at DESC
LIMIT 50;

Index yang salah urutan:

CREATE INDEX idx_orders_created_at_tenant_id
ON orders (created_at, tenant_id);

Perbaikan:

CREATE INDEX idx_orders_tenant_id_created_at
ON orders (tenant_id, created_at);

Alasannya sederhana: query ingin hasil terbaru di dalam tenant tertentu, bukan seluruh tabel lebih dulu lalu disaring ke tenant.

Audit dengan EXPLAIN dan EXPLAIN ANALYZE

Jangan menebak. Uji query sebelum dan sesudah penambahan index. Bentuk output berbeda antar database, tetapi prinsip bacanya mirip.

Yang perlu diperhatikan

Access type: apakah database melakukan scan luas atau pencarian yang lebih sempit.
Index yang dipilih: apakah index komposit yang Anda buat benar-benar dipakai.
Jumlah baris yang diperkirakan/dibaca: apakah turun signifikan.
Sorting tambahan: cari tanda adanya filesort, sort, atau operasi sort eksplisit.
Actual timing pada EXPLAIN ANALYZE: penting karena optimizer bisa salah estimasi.

Contoh penggunaan

EXPLAIN
SELECT id, title, status, published_at
FROM articles
WHERE tenant_id = 10 AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

Jika database mendukung analisis aktual:

EXPLAIN ANALYZE
SELECT id, title, status, published_at
FROM articles
WHERE tenant_id = 10 AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

Interpretasi praktis:

Jika index komposit dipakai dan jumlah baris yang dibaca kecil, desain kemungkinan benar.
Jika masih ada sort besar, cek apakah urutan kolom index sudah sesuai dengan query.
Jika optimizer tetap memilih index lain, cek selektivitas kolom, statistik, dan apakah query benar-benar identik dengan pola yang Anda targetkan.

Uji dengan parameter yang realistis. Query untuk tenant kecil bisa tampak cepat, tetapi tenant dengan volume data terbesar mungkin menunjukkan perilaku yang berbeda.

Kapan index tunggal tidak cukup

Index tunggal sering cukup untuk query sederhana. Namun untuk pola WHERE + ORDER BY + LIMIT pada tabel besar, index tunggal biasanya tidak memadai ketika:

Ada lebih dari satu kolom filter yang selalu muncul bersama.
Sorting harus tetap cepat setelah filtering.
Anda ingin menghindari membaca banyak baris hanya untuk menemukan sedikit hasil teratas.
Endpoint dipanggil sangat sering dan sensitif terhadap latency.

Contoh yang hampir selalu membutuhkan index komposit:

WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC LIMIT 50

Jika Anda hanya punya index per kolom, database tetap harus berkompromi. Kompromi ini makin mahal seiring pertumbuhan data.

Trade-off: write amplification dan ukuran index

Setiap index tambahan bukan gratis. Pada sistem yang banyak menulis, Anda harus menghitung biayanya.

Dampak yang perlu dipahami

Insert dan update lebih mahal: setiap perubahan pada kolom yang terindeks perlu memperbarui struktur index.
Penyimpanan bertambah: index komposit pada tabel besar bisa memakan ruang signifikan.
Vacuum/maintenance/rebuild pada beberapa database bisa menjadi lebih berat.
Optimizer complexity: terlalu banyak index dapat membuat pemilihan rencana eksekusi lebih rumit.

Karena itu, targetkan index pada query yang benar-benar penting: endpoint paling sering, query paling lambat, atau halaman operasional yang paling berdampak. Jangan membuat variasi index untuk setiap kemungkinan kombinasi filter tanpa data penggunaan nyata.

Checklist implementasi aman di produksi

Identifikasi query prioritas dari slow query log atau metrik aplikasi.
Kelompokkan pola query, bukan hanya satu query literal. Cari kombinasi filter dan sorting yang benar-benar dominan.
Rancang index komposit dengan urutan kolom yang mengikuti pola akses: equality, lalu sorting, sambil mewaspadai range.
Uji di staging dengan volume data yang mendekati produksi jika memungkinkan.
Bandingkan EXPLAIN sebelum dan sesudah.
Tambahkan index saat traffic rendah atau gunakan fitur pembuatan index yang meminimalkan lock jika database Anda mendukung.
Pantau dampak write: latency insert/update, ukuran storage, dan perubahan plan query lain.
Hapus index yang redundant setelah yakin index komposit baru menggantikan pola lama.

Perlu dicatat, kemampuan membuat index tanpa lock penuh atau dengan dampak minimal sangat bergantung pada mesin database dan cara deployment. Karena itu, cek dokumentasi database yang Anda pakai sebelum menjalankan migrasi di produksi.

Kesalahan umum saat merancang index komposit

1. Urutan kolom index terbalik

Menaruh kolom ORDER BY di depan padahal filter equality jauh lebih menentukan. Akibatnya banyak baris tetap harus dibaca sebelum filter terpenuhi.

2. Mengandalkan banyak index tunggal

Ini sering terlihat rapi di atas kertas, tetapi tidak memberikan jalur akses yang efisien untuk query gabungan. Satu index komposit yang tepat biasanya lebih berguna daripada beberapa index tunggal yang terpisah untuk pola query yang sama.

3. Tidak memperhatikan leftmost prefix

Index (tenant_id, status, created_at) tidak otomatis bagus untuk semua query yang melibatkan tiga kolom itu. Jika query sering hanya memfilter status dan mengurutkan created_at, Anda mungkin butuh index lain.

4. Mengabaikan efek range

Begitu ada filter range, kemampuan index untuk membantu kolom berikutnya bisa berkurang. Jangan berasumsi sorting tetap gratis setelah kondisi range.

5. Tidak menguji dengan data distribusi nyata

Query untuk tenant kecil bisa menipu. Selalu uji parameter dengan volume terbesar atau distribusi paling padat.

6. Menambahkan terlalu banyak index

Masalah read mungkin membaik, tetapi write dan storage memburuk. Fokus pada query yang paling kritis.

Tips debugging saat index terasa benar tetapi tidak dipakai

Pastikan bentuk query sama dengan pola yang Anda optimalkan.
Cek apakah ada fungsi pada kolom filter atau sorting yang membuat index sulit dipakai, misalnya transformasi langsung di klausa WHERE atau ORDER BY.
Periksa selektivitas kolom. Jika suatu kolom bernilai sangat sedikit variasi, optimizer bisa menilai index kurang berguna.
Pastikan statistik database cukup mutakhir.
Lihat apakah query mengambil terlalu banyak kolom sehingga tetap harus membaca banyak data dari tabel utama.
Bandingkan plan untuk LIMIT kecil vs besar, karena optimizer bisa berubah keputusan.

Penutup

Index komposit untuk query WHERE + ORDER BY adalah cara paling praktis untuk menjaga query list tetap cepat saat tabel membesar. Intinya bukan sekadar menambah index, tetapi menyesuaikan urutan kolom dengan pola akses nyata: filter equality terlebih dahulu, lalu kolom sorting, sambil memahami batasan leftmost prefix dan efek filter range.

Jika Anda melihat gejala seperti filesort, full scan, atau endpoint list yang makin lambat, mulai dari audit query aktual dan EXPLAIN. Dari sana, rancang satu index komposit yang benar-benar cocok dengan query dominan, ukur hasilnya, lalu evaluasi trade-off pada write dan ukuran index. Pendekatan ini jauh lebih efektif daripada menebak-nebak atau menumpuk index tunggal tanpa strategi.

Quick Search

shopping_cart Keranjang