Index strategy adalah memilih sekumpulan index untuk semua query penting aplikasimu sekaligus, bukan menambah satu index setiap ada query lambat. Bayangin rak bumbu di dapur Kedai Senja. Tiap rak mempercepat koki mengambil bumbu, tapi setiap belanjaan datang, semua rak harus dirapikan ulang. Dapur yang bagus punya sedikit rak yang dipakai banyak menu, bukan satu rak untuk tiap menu. Rak itu index, menu itu query, dan belanjaan yang datang itu INSERT.
🧪 Tebakan cepat: tabel
orderssudah punya index(customer_id), lalu tim menambah(customer_id, created_at)untuk halaman riwayat. Sekarang, query mana yang masih memakai index(customer_id)yang lama? Tebak dulu, jawabannya ada di bagian "Index dobel dan index yang gak terpakai".
Ingat lagi: leftmost prefix
Di pelajaran Index & Performa kamu sudah membuat composite index, index yang terdiri dari dua kolom atau lebih. Isinya diurutkan menurut kolom paling kiri dulu, jadi index (a, b, c) cuma bisa dipakai kalau WHERE memakai kolom dari kiri tanpa melompat:
| WHERE | Pakai index (a, b, c)? |
|---|---|
a = 1, a = 1 AND b = 2, a = 1 AND b = 2 AND c = 3 |
Ya |
b = 2, c = 3, b = 2 AND c = 3 |
Gak, kolom pertama dilompati |
Urutan penulisan di WHERE gak berpengaruh: b = 2 AND a = 1 tetap memakai index itu. Yang menentukan adalah kolom mana saja yang disebut. Di pelajaran ini kita pakai aturan itu untuk merancang index bagi beberapa query sekaligus.
Tiga query, satu set index
Tabel orders di Lab sama dengan pelajaran Index & Performa: 20.000 pesanan, Siti (customer_id 2) punya 100, dan 400 masih 'pending'. Tiga query ini yang paling sering jalan di Kedai Senja:
-- 1. Riwayat pesanan Siti
SELECT * FROM orders WHERE customer_id = 2 ORDER BY created_at DESC;
-- 2. Ringkasan belanja Siti: tanggal dan total saja
SELECT created_at, total FROM orders WHERE customer_id = 2 ORDER BY created_at DESC;
-- 3. Antrian admin: pesanan yang belum diproses
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;
Untuk tiap query, catat tiga hal: kolom yang dicari dengan =, kolom untuk rentang atau ORDER BY, dan kolom yang diambil SELECT. Query 1 dan 2 sama-sama mencari customer_id lalu mengurutkan created_at, jadi mereka bisa berbagi satu index.
Covering index: satu index untuk dua query
Covering index adalah index yang sudah berisi semua kolom yang dibutuhkan query, jadi database gak perlu membuka tabel sama sekali. Tambahkan total di belakang:
CREATE INDEX idx_orders_customer_date_total ON orders(customer_id, created_at, total);
query 1: SEARCH orders USING INDEX idx_orders_customer_date_total (customer_id=?)
query 2: SEARCH orders USING COVERING INDEX idx_orders_customer_date_total (customer_id=?)
- Query 1 memakai index ini persis seperti
(customer_id, created_at): lompat ke pesanan Siti, sudah urut tanggal. - Query 2 cuma butuh
created_atdantotal, dan keduanya ada di index. Tabelnya gak dibuka: COVERING INDEX, atau Index Only Scan di PostgreSQL. - PostgreSQL juga bisa menitipkan kolom tanpa ikut diurutkan:
CREATE INDEX ... ON orders (customer_id, created_at) INCLUDE (total);. Di SQLite,id(rowid) selalu ikut di setiap index, di PostgreSQL enggak.
Kolom sesudah rentang gak dipakai mencari
Admin juga mencari pesanan pending seorang pelanggan sejak tanggal tertentu: WHERE customer_id = 2 AND created_at >= '2026-10-01' AND status = 'pending'. Bandingkan dua urutan kolom:
(customer_id, created_at, status) SEARCH ... (customer_id=? AND created_at>?)
(customer_id, status, created_at) SEARCH ... (customer_id=? AND status=? AND created_at>?)
Begitu index bertemu kolom rentang (created_at >=), kolom sesudahnya gak bisa dipakai untuk melompat lagi, cuma untuk menyaring. Jadi urutannya: semua kolom = dulu, baru satu kolom rentang atau ORDER BY di paling belakang.
Index dobel dan index yang gak terpakai
Di Lab, nyalakan (customer_id) bersama (customer_id, created_at). Ketiga plan gak pernah menyebut idx_orders_customer lagi: query 1 dan 2 lebih memilih index gabungan, karena isinya sudah urut tanggal. Jadi jawabannya: gak ada. Itu jawaban tebakannya. (customer_id) sudah jadi awalan (prefix) dari index gabungan, sehingga dia cuma makan 188 KB dan ikut ditulis di setiap INSERT, tanpa pernah dibaca. Hapus index seperti itu.
Di PostgreSQL, cari index yang gak terpakai lewat pg_stat_user_indexes: kolom idx_scan mencatat berapa kali tiap index dipakai. Yang tetap 0 setelah berminggu-minggu di production adalah calon untuk dibuang.
Partial index untuk nilai yang jarang
Query 3 mencari 400 dari 20.000 baris. Kamu sudah kenal partial index dari pelajaran Index & Performa:
CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';
-- query 3: SCAN orders USING INDEX idx_orders_pending
Di Lab ukurannya cuma 3 halaman (12 KB), sedangkan index biasa di status 75 halaman (300 KB) dan masih harus mengurutkan sendiri. Query-nya harus menyebut syarat yang sama, status = 'pending', kalau gak, index ini gak terpakai. Pola yang sama berlaku untuk kolom yang cuma berisi TRUE/FALSE: index biasa gak banyak membantu, tapi partial index untuk nilai yang jarang dan sering dicari, misalnya WHERE is_active = false, kecil dan cepat.
Harga: ukur, jangan menebak
Ini ukuran yang diukur Lab dengan PRAGMA page_count (1 halaman = 4 KB) untuk 20.000 pesanan:
| Isi database | Ukuran |
|---|---|
tabel orders |
636 KB |
(customer_id) |
188 KB |
(customer_id, created_at) |
404 KB |
(customer_id, created_at, total) |
468 KB |
(created_at) WHERE status = 'pending' |
12 KB |
Index gabungan hampir sebesar tabelnya sendiri. Dan setiap INSERT menulis ke tabel ditambah setiap index. Set terbaik untuk tiga query di atas cuma dua index: (customer_id, created_at, total) dan partial index pending, 480 KB, dan ketiga query jalan tanpa membaca seluruh tabel dan tanpa mengurutkan sendiri. Di Lab kamu bisa mengukur sendiri waktu 5.000 INSERT dengan 0 dan dengan 3 index.
📌 Index di hasil fungsi
Di pelajaran EXPLAIN kamu lihat kolom yang dibungkus fungsi gak bisa memakai index biasa. Kalau query-nya memang selalu memakai fungsi itu, index-nya bisa dibuat di hasil fungsinya (expression index):
CREATE INDEX idx_customers_email_lower ON customers(lower(email));
SELECT * FROM customers WHERE lower(email) = '[email protected]';
-- SEARCH customers USING INDEX idx_customers_email_lower (<expr>=?)
Tanpa index itu, plan-nya SCAN customers. PostgreSQL menulisnya sama, MySQL (sejak 8.0.13) dengan kurung ganda: ((lower(email))). Ekspresi di query harus persis sama dengan ekspresi di index.
⚠️ Jebakan yang sering ketemu
- Satu index untuk tiap query. Lama-lama tabelnya punya sepuluh index yang saling dobel. Rancang dari daftar query, cari index yang bisa dipakai bersama.
- Index dobel.
(customer_id)di samping(customer_id, created_at)gak pernah dipakai. - Kolom
=ditaruh sesudah kolom rentang. Kolom itu cuma ikut menyaring, gak dipakai melompat. - Index yang gak perlu bisa bikin planner salah pilih. Di Lab, dengan
(status)dan partial index sekaligus, SQLite memilih(status)lalu mengurutkan sendiri. Lebih sedikit index berarti lebih sedikit pilihan yang salah. - Di latihan, urutan
queryColsgak berpengaruh, sama seperti diWHERE:["b", "a"]tetap bisa memakai index["a", "b", "c"], karena isinya sama dengan dua kolom pertama index. Yang dikembalikan booleantrue/false, bukan teks.
TL;DR: Rancang index untuk sekumpulan query, bukan satu per query. Composite index cuma terpakai dari kolom paling kiri (leftmost prefix): (a, b, c) melayani a, a + b, a + b + c, tapi gak b atau c saja. Taruh semua kolom = dulu, lalu satu kolom rentang atau ORDER BY. Covering index berisi semua kolom yang dibutuhkan, jadi satu index (customer_id, created_at, total) bisa melayani riwayat dan ringkasan tanpa membuka tabel. Partial index cocok untuk nilai yang jarang, termasuk kolom boolean. Index dobel atau yang gak pernah dipakai cuma memperlambat tulis dan makan tempat, jadi ukur dan buang.
