Query lambat di tabel jutaan baris hampir selalu masalah yang sama: database membaca terlalu banyak baris. Solusi standarnya: index.
Artikel ini menjelaskan cara kerja index, cara membuatnya, dan — yang paling penting — kapan index justru TIDAK boleh dipakai.
1. Analogi: Daftar Isi Buku
Tanpa index:
Cari kata “optimasi” di kamus 1000 halaman → baca satu per satu (full table scan)
Dengan index:
Buka daftar huruf O → lompat ke halaman 412 (index scan)
Index SQL bekerja persis seperti itu — struktur bantu yang menyimpan nilai kolom + pointer ke baris asli, sudah terurut.
2. B-tree — Index Paling Umum
B-tree (balanced tree) adalah struktur default index di hampir semua DBMS.
Sifat penting:
- Seimbang → kedalaman seragam, pencarian O(log n)
- Terurut → mendukung
=,<,>,BETWEEN,LIKE 'abc%',ORDER BY - Dapat duplikat (kecuali index unik)
[P:50]
/ \
[M:25] [T:75]
/ \ / \
[A..L] [N..S] [U..Z]
Query WHERE harga = 75 → turun 3 langkah, bukan scan jutaan baris.
Jenis index lain (singkat)
| Jenis | Untuk |
|---|---|
| B-tree | Default, equality + range |
| Hash | Equality = saja (jarang dipilih manual) |
| GIN/GiST (PostgreSQL) | Full-text, array, JSON |
| BRIN (PostgreSQL) | Data fisik sudah urut (log/timestamp besar) |
| Spatial | Geometri |
Seri ini fokus B-tree.
3. Membuat Index
-- Index pada satu kolom
CREATE INDEX idx_produk_nama ON produk(nama);
-- Index unik (nilai harus beda + otomatis constraint UNIQUE)
CREATE UNIQUE INDEX idx_users_email ON users(email);
Lihat index yang ada
-- PostgreSQL
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'produk';
-- MySQL
SHOW INDEX FROM produk;
-- SQLite
.schema produk
Hapus index
DROP INDEX IF EXISTS idx_produk_nama;
Index dari constraints
Sering otomatis dibuat DBMS:
| Constraint | Index otomatis |
|---|---|
PRIMARY KEY |
Ya (umumnya) |
UNIQUE |
Ya |
FOREIGN KEY |
Umumnya (untuk validasi & join) |
4. Composite Index — Index Multi-Kolom
CREATE INDEX idx_order_user_date ON orders(user_id, order_date);
Urutan kolom SANGAT penting
Prinsip leftmost prefix:
| Query | Pakai index? |
|---|---|
WHERE user_id = 1 |
✅ prefix kolom-1 |
WHERE user_id = 1 AND order_date > ... |
✅ kedua kolom |
WHERE order_date > ... (tanpa user_id) |
❌ lewati kolom-1 → index tidak kepakai |
Bayangkan kamus diurutkan dulu per huruf, baru per kata — mencari “Zebra” tanpa tahu huruf awal tetap harus telusuri.
Panduan urutan
- Kolom equality dulu (
=) →user_id - Lalu range/order (
>,BETWEEN,ORDER BY) →order_date - Kolom yang sering dipakai di banyak query → prioritas
-- Equality lalu range ✅
CREATE INDEX idx ON orders(user_id, order_date);
-- Untuk: WHERE user_id = ? AND order_date BETWEEN ? AND ?
-- ORDER BY order_date
5. Kapan Index MEMBANTU?
Kandidat index yang baik:
| Pola query | Kandidat |
|---|---|
WHERE kolom = ? |
Index kolom itu |
WHERE kolom IN (...) |
Index kolom itu |
WHERE kolom > ? / BETWEEN |
Index range |
ORDER BY kolom |
Index sesuai urutan |
JOIN ON a.id = b.id |
Sudah ada PK/FK |
Kolom di WHERE + ORDER BY |
Composite sesuai prinsip |
| Kolom sering di-filter di dashboard | Index khusus |
Rules of thumb
- Foreign key hampir selalu layak di-index (join +
ON DELETEcheck). - Kolom dengan selectivity tinggi (banyak nilai unik):
email,sku,username. WHERE status = 'pending'— lihat distribusi: bilapendinghanya 1% dari jutaan baris, index sangat membantu; bila 90% barispending, index kurang berguna (lebih baik full scan).
6. Kapan Index TIDAK Membantu (atau Merugikan)?
❌ Selectivity rendah
-- Kolom hanya punya 2 nilai (TRUE/FALSE) di jutaan baris
CREATE INDEX idx_aktif ON produk(aktif); -- biasanya SIA-SIA
Database lebih memilih full scan bila index akan mengarah ke sebagian besar tabel.
❌ Tidak dipakai di query
Index yang tidak pernah disentuh query = membuang-buang:
| Biaya index | Kapan dibayar |
|---|---|
| Ruang disk | Selalu |
| INSERT/UPDATE/DELETE lebih lambat | Setiap tulis (index harus di-update) |
| Optimasi lebih kompleks | Saat planning |
Aturan emas: jangan bikin index “jaga-jaga”. Index harus lahir dari query yang memang lambat.
❌ Fungsi pada kolom (biasanya)
-- ❌ Index di 'nama' tidak kepakai
WHERE LOWER(nama) = 'kopi'
-- ✅ Index expression / functional index (PostgreSQL)
CREATE INDEX idx ON products (LOWER(nama));
-- atau tulis ulang query agar pakai kolom polos
❌ Leading wildcard LIKE
WHERE nama LIKE '%kopi%' -- ❌ index biasa tidak membantu
WHERE nama LIKE 'Kopi%' -- ✅ prefix cocok → index bisa dipakai
Untuk %kopi%, pertimbangkan full-text index (PostgreSQL to_tsvector, MySQL FULLTEXT).
❌ Terlalu banyak index di tabel tulis-besar
Tabel dengan ribuan INSERT per detik + 20 index = tulis jadi lambat. Index hanya yang benar-benar dipakai.
7. Index Unik vs Index Biasa
CREATE UNIQUE INDEX idx_users_email ON users(email);
| Index biasa | Index unik | |
|---|---|---|
| Fungsi | Mempercepat | Mempercepat + jamin keunikan |
| Duplikat | Boleh | Tidak boleh |
| Setara dengan | — | UNIQUE constraint |
8. Covering Index — Query tanpa Kembali ke Tabel
-- Index sudah memuat semua kolom yang diSELECT
CREATE INDEX idx_cover ON orders(user_id, order_date, total);
SELECT order_date, total
FROM orders
WHERE user_id = 1;
Database bisa jawab hanya dari index (index-only scan) — tidak perlu buka row asli.
Di PostgreSQL bisa juga:
CREATE INDEX idx_cover ON orders(user_id) INCLUDE (total);
9. Checklist Sebelum Membuat Index
- ✅ Ada query lambat yang terbukti? (bukan asumsi)
- ✅ Lihat query plan (
EXPLAIN) — memang full scan? - �️ Kolom sering muncul di
WHERE/ORDER BY/JOIN? - ✅ Selectivity cukup tinggi?
- ✅ Urutan composite sesuai leftmost prefix?
- ✅ Tabel tidak terlalu sering di-tulis dengan index sudah banyak?
10. Recap
| Konsep | Poin utama |
|---|---|
| B-tree | Default, untuk =, range, ORDER BY |
CREATE INDEX idx ON t(kolom) |
Buat index |
CREATE UNIQUE INDEX |
Unik + cepat |
| Composite | Equality dulu, lalu range; leftmost prefix |
| FK layak di-index | Join + referential check |
| Rendah selectivity | Hindari (2 nilai di jutaan baris) |
LOWER(kolom) / %x% |
Index biasa tidak kepakai |
| Index tulis | Tiap INSERT/UPDATE harus update index |
| Langkah awal | EXPLAIN dulu, index sesudah bukti |
Penutup
Index adalah pedang bermata dua: query baca jauh lebih cepat, tapi tulis jadi lebih mahal dan ruang disk terpakai. Kuncinya bukan “bikin banyak index”, tapi “bikin index yang tepat untuk query yang terbukti lambat”.
Di artikel selanjutnya, kita lihat bagaimana database berpikir — EXPLAIN & Query Optimization membaca query plan dan mengoptimasi query.