Apa iku index ing SQL? Kapan lan kapan ora kudu nggunakake?

Question

Accepted Answer

Sebuah **index** yaiku struktur data (biasane **B-tree**) sing ngidini database nemokake baris kanthi cepet **tanpa scan kabeh tabel** — kaya index buku. Iku nyepetake lookups, filters, joins, lan sorts ing kolom indexed kanthi dramatis, nanging butuh storage ekstra lan writes luwih alon.

## Cara index nulungi

```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 index, database ngerjake **full table scan** (cek saben baris). Karo index, iku navigate B-tree kanggo nemokake baris cocok ing wektu logaritmik — beda antara scan jutaan baris lan sawetara lookups.

## Kapan nggunakake indexes

```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 ORA nggunakake indexes (trade-offs)

```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
```

Indexes ora gratis: iku **nggunakake storage** lan **nylowen writes** (saben modifikasi kudu update indexes). Over-indexing nggawe write performance mumet.

## Composite lan jenis index liyane

```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 penting

Indexes yaiku salah siji topik paling penting ing database performance — iku alat utama kanggo nggawe queries cepet, lan paham kapan lan carane nggunakake iku essential knowledge kanggo sapa wae sing kerja karo databases ing skala gedhe.

Nilai utamane dramat: tanpa indexes, queries sing filter, join, utawa sort ing kolom butuh **full table scans** (cek saben baris — catastrophically alon nalika data gedhe), dene indexes ngubah iki dadi fast logaritmik lookups — biasane beda antara query mbutuhake milliseconds versus seconds utawa minutes.

Paham **kapan ngindex** (kolom WHERE sing asring difilter, JOIN/foreign-key columns, ORDER BY columns, high-selectivity columns) yaiku fundamental kanggo query performance.

Samakala penting yaiku paham **trade-offs lan kapan ORA ngindex**: indexes nggunakake storage lan, crucially, **nylowen writes** (saben INSERT/UPDATE/DELETE kudu update kabeh indexes), dene over-indexing nggawe write performance mumet, lan indexes nambah nilai sethithik ing tabel cilik utawa low-selectivity columns.

Balance iki — ngindex apa sing di-query dene ora over-indexing — yaiku key skill.

Gawe ngerti **composite indexes** (multi-column, ing mana column order penting kanggo queries apa sing iku nulungi) nggawe ilmu praktis index lengkap.

Ménangi query performance kritis kanggo aplikasi nyata, lan ménangi proper indexing (indexes bener ing kolom bener, balance read speed lawan write cost lan storage) yaiku database optimization paling impactful, mastery indexes — cara iku kerja, kapan nggunakake, write/storage trade-offs, lan composite indexes — yaiku core, high-value knowledge kanggo databases performan lan salah siji topik paling penting lan commonly-tested database.