Apa itu indeks dalam SQL? Kapan Anda harus dan tidak boleh menggunakannya?

Question

Accepted Answer

**Indeks** adalah struktur data (biasanya **B-tree**) yang memungkinkan database menemukan baris dengan cepat **tanpa memindai seluruh tabel** — seperti indeks dalam sebuah buku. Ini secara dramatis mempercepat pencarian, filter, join, dan sort pada kolom yang diindeks, dengan biaya penyimpanan ekstra dan penulisan yang lebih lambat.

## Bagaimana indeks membantu

```sql
-- without an index on email, this scans EVERY row (slow on a big table) — O(n)
SELECT * FROM users WHERE email = 'ann@x.com';

-- create an index → the database can find the row directly — O(log n)
CREATE INDEX idx_users_email ON users(email);
-- now the same query uses the index → dramatically faster
```

Tanpa indeks, database melakukan **full table scan** (memeriksa setiap baris). Dengan indeks, ia menavigasi B-tree untuk menemukan baris yang cocok dalam waktu logaritmik — perbedaan antara memindai jutaan baris dan segelintir pencarian.

## Kapan menggunakan indeks

```text
✓ Columns in WHERE clauses (frequently filtered)
✓ Columns used in JOINs (foreign keys especially)
✓ Columns in ORDER BY (sorting can use the index)
✓ Columns with high SELECTIVITY (many distinct values — email, id)
✓ Primary keys (auto-indexed) and unique constraints
```

## Kapan TIDAK menggunakan indeks (trade-off)

```text
✗ Columns rarely queried — wasted storage and write overhead
✗ Small tables — a full scan is already fast; an index adds no benefit
✗ Low-selectivity columns (e.g. a boolean with 2 values) — index barely helps
✗ Write-heavy columns — EVERY INSERT/UPDATE/DELETE must also update the indexes
  → too many indexes SLOW DOWN writes significantly
```

Indeks tidak gratis: mereka **mengonsumsi penyimpanan** dan **memperlambat penulisan** (setiap modifikasi harus memperbarui indeks). Over-indexing merusak performa penulisan.

## Indeks komposit dan tipe indeks lainnya

```sql
CREATE INDEX idx_users_country_name ON users(country, name);   -- COMPOSITE (multi-column)
-- helps WHERE country = ? AND name = ?, and WHERE country = ? (leftmost prefix)
-- but NOT WHERE name = ? alone (order matters!)

CREATE UNIQUE INDEX ...      -- enforces uniqueness
-- also: partial indexes, covering indexes, GIN/GiST (Postgres) for special data
```

## Mengapa ini penting

Indeks adalah salah satu topik terpenting dalam performa database — mereka adalah alat utama untuk membuat query cepat, dan memahami kapan dan bagaimana menggunakannya adalah pengetahuan esensial bagi siapa pun yang bekerja dengan database pada skala besar.

Nilai intinya adalah dramatis: tanpa indeks, query yang memfilter, join, atau sort pada kolom memerlukan **full table scans** (memeriksa setiap baris — sangat lambat saat data bertambah), sementara indeks mengubah ini menjadi pencarian logaritmik yang cepat — sering kali perbedaan antara query yang memakan waktu milidetik versus detik atau menit.

Memahami **kapan mengindeks** (kolom WHERE yang sering difilter, kolom JOIN/foreign-key, kolom ORDER BY, kolom dengan selectivity tinggi) adalah fundamental untuk performa query.

Tidak kalah pentingnya adalah memahami **trade-off dan kapan TIDAK mengindeks**: indeks mengonsumsi penyimpanan dan, yang krusial, **memperlambat penulisan** (setiap INSERT/UPDATE/DELETE harus memperbarui semua indeks), jadi over-indexing merusak performa penulisan, dan indeks menambah sedikit nilai pada tabel kecil atau kolom dengan selectivity rendah.

Keseimbangan ini — mengindeks apa yang di-query sambil tidak over-indexing — adalah keterampilan kunci.

Mengetahui tentang **indeks komposit** (multi-kolom, di mana urutan kolom penting untuk query mana yang mereka bantu) melengkapi pengetahuan indeks praktis.

Karena performa query sangat penting untuk aplikasi nyata, dan karena indexing yang tepat (indeks yang tepat pada kolom yang tepat, menyeimbangkan kecepatan baca dengan biaya penulisan dan penyimpanan) adalah optimisasi database yang paling berdampak, menguasai indeks — bagaimana mereka bekerja, kapan menggunakannya, trade-off penulisan/penyimpanan, dan indeks komposit — adalah pengetahuan inti, bernilai tinggi untuk database yang performan dan salah satu topik database yang paling penting dan sering diuji.