Covering Index untuk Endpoint List SQL yang Melambat

Jika endpoint list makin lambat seiring pertumbuhan data, masalahnya sering bukan pada network atau kode API, tetapi pada pola query SELECT yang memfilter, mengurutkan, lalu mengembalikan kolom yang tidak sepenuhnya tersedia di index. Akibatnya, database harus membaca index untuk menemukan kandidat baris, lalu melakukan heap lookup atau akses balik ke tabel utama untuk mengambil kolom hasil. Pada volume data kecil ini mungkin tidak terasa, tetapi saat data membesar, biaya I/O dan CPU akan naik tajam.

Covering index adalah salah satu perbaikan paling efektif untuk kasus ini. Intinya, index tidak hanya membantu mencari baris yang cocok, tetapi juga sudah memuat kolom yang dibutuhkan query, sehingga database bisa menyelesaikan query langsung dari index atau setidaknya mengurangi akses ke tabel utama. Namun, solusi ini tidak selalu tepat untuk semua query. Anda tetap perlu memahami gejala, cara membaca EXPLAIN, urutan kolom index, dan kapan masalah sebenarnya ada pada pola query atau pagination.

Mengapa endpoint list melambat saat data tumbuh

Endpoint list umumnya punya pola seperti ini:

Ada filter, misalnya status, tenant, tanggal, atau kategori.
Ada sorting, misalnya ORDER BY created_at DESC.
Ada pagination, biasanya LIMIT dan OFFSET.
Ada sejumlah kolom yang dikembalikan ke klien.

Masalah muncul saat index yang tersedia hanya membantu sebagian. Contoh: query bisa memakai index untuk memfilter berdasarkan tenant_id dan status, tetapi kolom yang dipilih di SELECT tidak ada di index. Database lalu harus mengambil baris satu per satu dari tabel utama. Jika kandidat baris banyak, proses ini menjadi mahal.

Gejala umum yang sering terlihat:

Latensi endpoint list meningkat bertahap seiring ukuran tabel bertambah.
Query yang dulu cepat kini menjadi lambat meski logika aplikasi tidak berubah.
CPU atau I/O database naik pada endpoint dengan trafik tinggi.
Query dengan OFFSET besar terasa makin berat.
Sudah ada index, tetapi performa tetap buruk atau tidak stabil.

Hal pentingnya: punya index belum tentu cukup. Index bisa membantu mencari kandidat baris, tetapi jika query tetap membutuhkan banyak akses ke tabel utama, bottleneck belum hilang.

Contoh pola query yang sering bermasalah

Misalkan endpoint menampilkan daftar pesanan per tenant:

SELECT id, customer_name, total_amount, created_at
FROM orders
WHERE tenant_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

Index biasa yang sering dibuat:

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

Index tersebut sudah membantu untuk filter dan sorting. Namun query masih meminta id, customer_name, dan total_amount. Jika kolom-kolom itu tidak tersedia di index, database mungkin perlu melakukan lookup ke tabel utama untuk setiap baris hasil.

Pada 20 baris mungkin terlihat kecil, tetapi database sering harus memeriksa lebih banyak kandidat sebelum menghasilkan hasil akhir yang terurut dan sesuai kondisi. Jika distribusi data tidak merata atau OFFSET besar, biaya lookup bisa meningkat signifikan.

Di sinilah covering index relevan: index dirancang agar query bisa dipenuhi dari struktur index itu sendiri, bukan bolak-balik ke tabel utama.

Apa itu covering index dan kapan index biasa tidak cukup

Covering index adalah index yang mencakup:

kolom untuk filter,
kolom untuk sorting, dan
kolom yang dibutuhkan di SELECT.

Dengan begitu, database dapat membaca hasil langsung dari index atau setidaknya sangat mengurangi lookup ke tabel utama. Nama dan implementasi detailnya bisa berbeda antar database, tetapi konsepnya sama: query tertutup oleh index.

Index biasa tidak cukup ketika:

Filter sudah memakai index, tetapi plan masih menunjukkan banyak akses ke tabel utama.
Jumlah baris yang diperiksa jauh lebih besar daripada jumlah baris yang dikembalikan.
Kolom hasil SELECT tidak ada di index.
Sorting masih memicu operasi sort terpisah karena urutan index tidak cocok.

Covering index paling bermanfaat untuk endpoint list yang dipanggil sering, pola query stabil, dan kolom hasil relatif sempit. Jika kolom yang dipilih banyak atau lebar, covering index bisa menjadi terlalu besar dan mahal dipelihara.

Cara membaca EXPLAIN dan EXPLAIN ANALYZE secara konseptual

Anda tidak harus menghafal output spesifik tiap database untuk menemukan masalah utama. Fokuslah pada beberapa pertanyaan berikut saat membaca EXPLAIN atau EXPLAIN ANALYZE:

1. Apakah query benar-benar memakai index yang diharapkan?

Lihat apakah plan menunjukkan pencarian berbasis index, bukan full scan. Jika ternyata full scan, kemungkinan penyebabnya antara lain:

urutan kolom index tidak cocok dengan filter/sort,
selektivitas rendah,
query terlalu luas, atau
fungsi/ekspresi pada kolom membuat index sulit dipakai.

2. Berapa banyak baris yang dibaca dibanding yang dikembalikan?

Jika endpoint mengembalikan 20 baris tetapi plan menunjukkan ribuan atau jutaan baris dipindai, berarti ada pekerjaan berlebih. Ini bisa berasal dari:

filter tidak cukup selektif,
OFFSET besar,
sorting yang tidak dibantu index, atau
heap lookup yang banyak.

3. Apakah ada operasi sort tambahan?

Jika plan menunjukkan langkah sort terpisah, index mungkin membantu filter tetapi tidak cocok untuk urutan ORDER BY. Sorting pada dataset besar bisa menjadi mahal, apalagi jika terjadi setelah membaca banyak baris.

4. Apakah ada indikasi akses balik ke tabel utama?

Nama operator berbeda antar database, tetapi secara konsep Anda ingin tahu apakah database:

cukup membaca dari index, atau
membaca index lalu mengambil kolom tambahan dari tabel utama.

Jika banyak akses balik terjadi, covering index layak dipertimbangkan.

5. Apakah estimasi dan aktual jauh berbeda?

Pada EXPLAIN ANALYZE, bandingkan estimasi jumlah baris dengan angka aktual. Selisih besar bisa menandakan statistik kurang akurat atau distribusi data tidak merata. Dalam kondisi ini, menambah index belum tentu langsung menyelesaikan semua masalah.

Merancang covering index untuk query list

Prinsip umum perancangan index untuk endpoint list:

Letakkan kolom filter yang paling menentukan di depan.
Susul dengan kolom sorting agar urutan hasil bisa dibaca langsung dari index.
Tambahkan kolom yang dipilih jika memang ingin membuat query menjadi covering.

Contoh, untuk query:

SELECT id, customer_name, total_amount, created_at
FROM orders
WHERE tenant_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 20;

Maka kandidat index secara konsep adalah:

CREATE INDEX idx_orders_list_covering
ON orders (tenant_id, status, created_at, id, customer_name, total_amount);

Tujuannya:

tenant_id dan status membantu mempersempit pencarian.
created_at membantu mempertahankan urutan yang sesuai ORDER BY.
id, customer_name, dan total_amount tersedia untuk hasil query.

Namun, jangan menganggap urutan ini selalu benar untuk semua kasus. Anda harus mempertimbangkan pola query nyata, terutama:

apakah semua filter selalu dipakai,
apakah urutan sort tetap,
apakah ada range filter seperti tanggal,
dan seberapa lebar kolom hasil.

Memilih urutan kolom index

Urutan kolom index sangat penting. Pedoman praktis:

Kolom equality filter seperti tenant_id = ? atau status = ? biasanya diletakkan lebih awal.
Kolom untuk ORDER BY biasanya diletakkan setelah kolom equality filter.
Kolom yang hanya dibutuhkan untuk SELECT diletakkan belakangan karena fungsinya lebih ke coverage daripada penyaringan.

Contoh lain:

SELECT id, title, published_at
FROM articles
WHERE site_id = ?
  AND visibility = 'public'
  AND published_at >= ?
ORDER BY published_at DESC
LIMIT 20;

Di sini ada equality filter pada site_id dan visibility, lalu ada kondisi rentang pada published_at yang juga dipakai untuk sorting. Ini sering cocok untuk index dengan urutan yang mengikuti pola tersebut. Tetapi jika query kadang tidak memfilter visibility atau kadang mengurutkan kolom lain, satu covering index belum tentu cukup untuk semua variasi.

Jangan terlalu agresif memasukkan semua kolom

Kesalahan umum adalah mencoba membuat satu index yang menampung semua kebutuhan semua endpoint. Dampaknya:

ukuran index membesar,
insert/update/delete menjadi lebih mahal,
memori cache terpakai untuk index yang jarang bermanfaat,
optimizer justru mendapat terlalu banyak opsi yang tidak efisien.

Fokuslah pada query yang paling sering dipakai atau paling mahal biayanya.

Dampak covering index pada pagination

Pagination sering menjadi sumber masalah tambahan. Ada dua pola umum:

Offset pagination

SELECT id, customer_name, total_amount, created_at
FROM orders
WHERE tenant_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

Meskipun ada covering index, OFFSET besar tetap memaksa database melewati banyak baris sebelum menemukan halaman yang diminta. Covering index membantu mengurangi biaya lookup ke tabel utama, tetapi tidak menghilangkan biaya melewati ribuan baris.

Jika endpoint memiliki halaman dalam yang sering diakses, pertimbangkan keyset pagination atau seek method:

SELECT id, customer_name, total_amount, created_at
FROM orders
WHERE tenant_id = ?
  AND status = ?
  AND created_at < ?
ORDER BY created_at DESC
LIMIT 20;

Jika ada kemungkinan nilai created_at sama, gunakan kolom tiebreaker yang stabil seperti id:

SELECT id, customer_name, total_amount, created_at
FROM orders
WHERE tenant_id = ?
  AND status = ?
  AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Secara konsep, kombinasi index yang selaras dengan filter dan urutan ini jauh lebih efisien untuk data besar dibanding OFFSET tinggi.

Jika masalah utama adalah halaman dalam dengan OFFSET besar, menambah covering index saja sering tidak cukup. Anda mungkin perlu mengubah pola pagination.

Checklist investigasi untuk endpoint list yang melambat

Ambil query SQL aktual dari endpoint, bukan asumsi dari ORM.
Catat kolom filter, sorting, dan SELECT yang benar-benar dipakai.
Jalankan EXPLAIN untuk melihat apakah index dipakai, ada sort tambahan, atau ada scan besar.
Jalankan EXPLAIN ANALYZE di lingkungan aman untuk melihat biaya aktual dan jumlah baris nyata.
Bandingkan jumlah baris dibaca vs dikembalikan. Selisih besar biasanya tanda masalah.
Periksa apakah query melakukan heap/table lookup karena kolom hasil tidak ada di index.
Evaluasi urutan kolom index apakah sesuai dengan filter equality, range, lalu sorting.
Tinjau pagination. Jika memakai OFFSET besar, ukur dampaknya terpisah.
Ukur trade-off write jika tabel sering diinsert atau diupdate.
Validasi setelah perubahan dengan plan baru dan metrik endpoint, bukan hanya asumsi.

Kesalahan umum saat mencoba memperbaiki dengan index

1. Mengindeks kolom hasil tanpa memperhatikan filter dan sort

Menambahkan kolom SELECT ke index tidak akan banyak membantu jika urutan awal index tidak cocok untuk menemukan baris yang tepat.

2. Membuat index terlalu lebar

Semakin banyak kolom dimasukkan, semakin besar ukuran index dan biaya write. Pilih kolom yang benar-benar dibutuhkan oleh query penting.

3. Mengabaikan urutan kolom

Index komposit bukan sekadar kumpulan kolom. Urutannya menentukan apakah optimizer bisa memanfaatkannya untuk filter dan sorting.

4. Tetap memakai SELECT yang terlalu lebar

Jika endpoint hanya butuh 5 kolom, jangan ambil 20 kolom. Semakin sedikit kolom yang dibutuhkan, semakin realistis membuat covering index yang efisien.

5. Menyalahkan database padahal masalah ada di pagination

OFFSET besar adalah sumber perlambatan yang sangat umum. Pada kasus seperti ini, perbaikan query pattern sering lebih penting daripada menambah index.

6. Tidak menguji query yang benar-benar dominan

Jangan mendesain index dari satu contoh query jika di produksi ada banyak variasi filter. Gunakan data akses nyata dari log atau query stats.

Kapan perlu ubah pola query, bukan sekadar menambah index

Ada kondisi di mana covering index bukan jawaban utama:

Kolom hasil terlalu banyak atau terlalu besar, sehingga index menjadi tidak praktis.
Variasi query terlalu banyak, sehingga satu index tidak bisa menutup pola akses yang beragam.
OFFSET sangat besar, sehingga biaya skip baris tetap mahal.
Filter kurang selektif, sehingga terlalu banyak baris tetap perlu dibaca.
Query mengambil data turunan atau agregasi kompleks yang memang tidak cocok dipercepat hanya dengan coverage sederhana.

Pada situasi ini, pertimbangkan pendekatan lain:

ganti offset pagination menjadi keyset pagination,
pecah query menjadi dua tahap bila masuk akal,
kurangi kolom yang dipilih,
buat endpoint list yang lebih spesifik,
gunakan ringkasan atau tabel hasil prahitung untuk kebutuhan tertentu.

Contoh alur perbaikan yang praktis

Misalkan Anda menemukan query endpoint list berikut melambat:

SELECT id, customer_name, total_amount, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20 OFFSET 2000;

Langkah perbaikannya bisa seperti ini:

Periksa plan saat ini. Apakah database memakai index untuk tenant_id, status, created_at? Apakah masih ada sort tambahan? Apakah masih banyak lookup ke tabel utama?
Kurangi SELECT jika ada kolom yang sebenarnya tidak dipakai klien.
Buat covering index yang sesuai untuk filter, sorting, dan kolom hasil yang penting.
Uji ulang plan untuk memastikan query lebih banyak diselesaikan dari index.
Evaluasi OFFSET. Jika halaman dalam sering diakses, ubah ke keyset pagination.
Pantau write overhead setelah index baru dibuat, terutama jika tabel orders sangat aktif.

Hasil yang dicari bukan sekadar “query memakai index”, tetapi:

baris yang dibaca lebih sedikit,
akses balik ke tabel utama berkurang,
sorting tambahan hilang atau mengecil,
latensi endpoint turun secara konsisten pada data besar.

Trade-off yang harus dipahami sebelum menambah covering index

Ukuran index bertambah: semakin banyak kolom, semakin besar konsumsi storage dan cache.
Biaya write naik: setiap insert, update, atau delete perlu memelihara index tambahan.
Tidak semua query akan diuntungkan: index ini biasanya efektif untuk pola query tertentu, bukan universal.
Kolom yang sering berubah kurang ideal: perubahan data dapat membuat pemeliharaan index lebih mahal.

Karena itu, covering index paling cocok untuk endpoint list yang:

sering dipanggil,
pola query-nya stabil,
kolom hasilnya terbatas,
dan tabelnya lebih sering dibaca daripada ditulis.

Penutup

Covering index untuk endpoint list SQL yang melambat efektif ketika bottleneck berasal dari query yang sudah bisa memfilter lewat index, tetapi masih harus melakukan banyak heap lookup untuk mengambil kolom hasil. Diagnosis yang benar dimulai dari query nyata dan pembacaan EXPLAIN secara konseptual: apakah index dipakai, apakah sorting terbantu, berapa banyak baris dibaca, dan apakah akses ke tabel utama masih dominan.

Jangan berhenti pada asumsi “sudah ada index”. Untuk endpoint list, performa biasanya ditentukan oleh kombinasi filter + sort + kolom hasil + pagination. Jika keempatnya selaras dengan index, Anda bisa mendapat peningkatan yang nyata. Jika tidak, Anda mungkin perlu mengubah pola query atau pagination, bukan sekadar menambah index baru.

Quick Search

shopping_cart Keranjang