Index #

“Query-nya lambat, coba tambahkan index.” Nasihat ini benar — tapi hanya separuhnya. Bagian yang tidak diucapkan adalah: index yang salah dipasang justru memperlambat sistem secara keseluruhan. Write menjadi lebih lambat karena database harus memperbarui semua index setiap kali data berubah. Storage membengkak. Query planner bingung memilih index mana yang optimal. Dan yang paling menjengkelkan: index yang sudah dipasang susah payah ternyata tidak pernah digunakan sama sekali karena ada kesalahan kecil di query.

Index adalah salah satu topik yang paling mudah dipahami di permukaan — “buat index supaya query cepat” — tapi paling dalam kalau ditelusuri sampai ke cara kerjanya. Memahami index secara benar berarti memahami mengapa sebuah query lambat meskipun sudah ada index, mengapa menambah index bisa membuat sistem secara keseluruhan lebih lambat, dan bagaimana merancang index yang benar-benar bekerja untuk pola akses data yang kamu miliki.

Cara Kerja Index: Dari Full Scan ke Pencarian Logaritmik #

Tanpa index, cara database mencari data sangat sederhana dan sangat mahal: ia membaca setiap baris dari awal sampai akhir — disebut full table scan — sampai menemukan baris yang cocok dengan kondisi query. Untuk tabel dengan seribu baris, ini masih cepat. Untuk tabel dengan sepuluh juta baris, ini bisa memakan detik.

Full table scan — tanpa index:

  Tabel users (10.000.000 baris):
  ┌────┬────────────────────────┬──────┐
  │ id │ email                  │ name │
  ├────┼────────────────────────┼──────┤
  │  1 │ [email protected]          │ Andi │  ← baca, cek, tidak cocok
  │  2 │ [email protected]          │ Budi │  ← baca, cek, tidak cocok
  │  3 │ [email protected]          │ Cici │  ← baca, cek, tidak cocok
  │ .. │ ...                    │ ...  │  ← terus sampai 10 juta baris
  │ 7M │ [email protected]        │ Tejo │  ← ketemu di baris ke-7 juta
  └────┴────────────────────────┴──────┘

  SELECT * FROM users WHERE email = '[email protected]';
  → Database membaca 7.000.000 baris sebelum menemukan hasil
  → Waktu: O(n) — linear terhadap jumlah baris

Index menyelesaikan masalah ini dengan cara yang sama seperti daftar isi di sebuah buku: alih-alih membaca halaman per halaman, kamu langsung tahu di halaman berapa topik yang kamu cari berada.

Dengan index pada kolom email:

  Index B-Tree (terurut):              Tabel asli:
  ┌──────────────────┬─────────┐       ┌────┬────────────────────┐
  │ email (terurut)  │ row ptr │       │ id │ email              │
  ├──────────────────┼─────────┤       ├────┼────────────────────┤
  │ [email protected]    │ → id=1  │       │  1 │ [email protected]      │
  │ [email protected]    │ → id=2  │       │  2 │ [email protected]      │
  │ [email protected]    │ → id=3  │       │  7M│ [email protected]    │
  │ ...              │ ...     │       └────┴────────────────────┘
  │ [email protected]  │ → id=7M │
  └──────────────────┴─────────┘

  SELECT * FROM users WHERE email = '[email protected]';
  → Database navigasi di B-Tree: O(log n)
  → Untuk 10 juta baris: hanya ~23 langkah, bukan 7 juta

Perbedaan antara O(n) dan O(log n) pada tabel besar bukan sekadar perbedaan kecepatan — ini perbedaan antara query yang selesai dalam milidetik dan query yang timeout.

Struktur Internal: Bagaimana B-Tree Bekerja #

Hampir semua index di database relasional modern menggunakan struktur B-Tree atau variannya B+Tree. Memahami cara kerjanya penting untuk mengerti mengapa index bekerja untuk beberapa jenis query tapi tidak untuk yang lain.

B-Tree adalah pohon yang selalu seimbang — kedalaman dari root ke setiap leaf selalu sama, tidak peduli datanya berapa banyak. Ini yang menjamin kompleksitas O(log n) untuk semua operasi.

Visualisasi B-Tree untuk index pada kolom 'email':

                    [M]
                   /   \
              [D-G]     [R-T]
             /  |  \   /  |  \
           [A] [E] [H][P] [S] [V]

  Setiap node menyimpan nilai yang terurut.
  Pencarian selalu mulai dari root, turun ke arah yang tepat.
  Untuk menemukan '[email protected]': root → kanan (R-T) → kanan (V) → tidak ada,
  cek [S] → ketemu. Hanya 3 langkah untuk seluruh dataset.

Struktur terurut ini adalah alasan mengapa B-Tree index tidak hanya berguna untuk equality search (=), tapi juga untuk:

Range query: WHERE price BETWEEN 100 AND 500 — database navigasi ke nilai 100, lalu scan ke kanan sampai 500
Prefix match: WHERE name LIKE 'Ali%' — database navigasi ke ‘Ali’, lalu scan selama prefix masih cocok
Sorting: ORDER BY created_at — data sudah terurut di index, tidak perlu sort tambahan
Min/Max: SELECT MIN(price) — cukup ambil node paling kiri atau paling kanan

Yang tidak bisa dilakukan B-Tree index secara efisien:

WHERE name LIKE '%Ali%' — wildcard di awal tidak bisa memanfaatkan urutan index
WHERE LOWER(email) = '[email protected]' — fungsi pada kolom membuat index tidak terpakai
Kolom dengan cardinality sangat rendah (boolean, gender) — index tidak efektif karena setiap nilai cocok dengan terlalu banyak baris

Empat Jenis Index yang Paling Penting #

Single Column Index #

Index pada satu kolom — bentuk paling sederhana dan paling umum. Efektif untuk query yang memfilter, mengurutkan, atau melakukan join berdasarkan satu kolom.

-- Membuat single column index
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Query yang akan memanfaatkan index ini:
SELECT * FROM orders WHERE user_id = 42;
SELECT * FROM orders WHERE created_at >= '2025-01-01' ORDER BY created_at;
SELECT COUNT(*) FROM orders WHERE user_id = 42;

Composite Index #

Index pada lebih dari satu kolom. Ini adalah jenis index yang paling sering salah digunakan — baik urutannya maupun asumsi tentang query mana yang bisa memanfaatkannya.

Aturan paling penting untuk composite index: urutan kolom menentukan segalanya. Database hanya bisa menggunakan composite index jika query menggunakan kolom-kolom tersebut dari kiri ke kanan tanpa melewati kolom di tengah.

-- Composite index: (user_id, status, created_at)
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);

-- Query yang BISA memanfaatkan index ini:
SELECT * FROM orders WHERE user_id = 42;                          -- ✓ kolom pertama
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';   -- ✓ dua kolom pertama
SELECT * FROM orders
WHERE user_id = 42 AND status = 'pending'
  AND created_at >= '2025-01-01';                                  -- ✓ tiga kolom

-- Query yang TIDAK BISA memanfaatkan index secara penuh:
SELECT * FROM orders WHERE status = 'pending';        -- ✗ melewati user_id (kolom pertama)
SELECT * FROM orders WHERE created_at >= '2025-01-01'; -- ✗ melewati user_id dan status

Visualisasi "leftmost prefix rule":

  Index: (A, B, C)
  ┌───────────────────────────────────────────────────────────────┐
  │  Query memakai A        → index digunakan (kolom A)           │
  │  Query memakai A, B     → index digunakan (kolom A dan B)     │
  │  Query memakai A, B, C  → index digunakan penuh               │
  │  Query memakai B        → index TIDAK digunakan               │
  │  Query memakai B, C     → index TIDAK digunakan               │
  │  Query memakai A, C     → index digunakan hanya untuk A       │
  └───────────────────────────────────────────────────────────────┘

Unique Index #

Unique index menggabungkan dua fungsi: mempercepat pencarian sekaligus menjamin tidak ada nilai duplikat. Setiap UNIQUE KEY di DDL tabel secara otomatis membuat unique index.

-- Unique index pada satu kolom
ALTER TABLE users ADD UNIQUE KEY uk_users_email (email);

-- Composite unique index: kombinasi kolom yang harus unik
ALTER TABLE order_items ADD UNIQUE KEY uk_order_items_order_product (order_id, product_id);
-- Satu produk tidak bisa muncul dua kali dalam satu order

-- Unique index juga dimanfaatkan untuk upsert:
INSERT INTO users (email, name) VALUES ('[email protected]', 'Ali')
ON DUPLICATE KEY UPDATE name = VALUES(name);

Covering Index #

Covering index adalah index yang sudah mencakup semua kolom yang dibutuhkan oleh query — baik di WHERE, SELECT, maupun ORDER BY. Ketika covering index digunakan, database tidak perlu mengakses tabel utama sama sekali: semua data yang dibutuhkan sudah ada di index.

Ini adalah optimasi yang sangat signifikan karena akses ke index jauh lebih cepat dari akses ke tabel (index lebih kecil, lebih mungkin masuk ke memory/cache).

-- Query: ambil email dan name untuk semua user aktif
SELECT email, name FROM users WHERE is_active = 1;

-- Index biasa pada is_active:
-- Database pakai index untuk filter, tapi harus baca tabel untuk ambil email dan name

-- Covering index yang mencakup semua kolom yang dibutuhkan query:
CREATE INDEX idx_users_active_email_name ON users(is_active, email, name);
-- Database tidak perlu baca tabel sama sekali — semua ada di index

-- Cara verifikasi covering index digunakan:
EXPLAIN SELECT email, name FROM users WHERE is_active = 1;
-- Lihat kolom 'Extra': jika ada "Using index" → covering index berhasil digunakan

Covering index adalah salah satu optimasi yang dampaknya paling besar untuk query read-heavy. Jika ada query yang sangat sering dijalankan dan columnnya tidak terlalu banyak, pertimbangkan untuk membuat covering index yang mencakup semua kolom yang dibutuhkan query tersebut.

Trade-off yang Tidak Boleh Diabaikan #

Index bukan gratis. Setiap index yang kamu buat membawa biaya yang harus dibayar di setiap operasi write.

Write Menjadi Lebih Lambat #

Setiap INSERT, UPDATE, dan DELETE tidak hanya memperbarui data di tabel — ia juga harus memperbarui semua index yang relevan. Semakin banyak index, semakin mahal setiap operasi write.

Dampak index terhadap write:

  Tabel orders tanpa index:
    INSERT → update 1 struktur (tabel)     → cepat

  Tabel orders dengan 5 index:
    INSERT → update 6 struktur (tabel + 5 index) → lebih lambat

  Tabel orders dengan 15 index:
    INSERT → update 16 struktur → lambat, terutama untuk bulk insert

  Sistem dengan INSERT rate tinggi (logging, event tracking, IoT):
  → Terlalu banyak index bisa menjadi bottleneck write yang serius

Ini juga mengapa sistem yang sangat write-heavy — seperti logging, analytics event, atau sensor data — biasanya menggunakan database terpisah yang dioptimalkan untuk write, bukan database operasional yang memiliki banyak index.

Storage Bertambah #

Index adalah struktur data terpisah yang disimpan di disk. Untuk tabel besar dengan banyak index, ukuran total index bisa melebihi ukuran data itu sendiri.

-- Cara cek ukuran index per tabel di MySQL
SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND(index_length / data_length * 100, 1) AS index_ratio_pct
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY index_length DESC;

-- Jika index_ratio_pct jauh di atas 100%,
-- artinya index lebih besar dari data → patut dievaluasi apakah semua index perlu

Cardinality Rendah Membuat Index Tidak Efektif #

Cardinality adalah jumlah nilai unik dalam sebuah kolom. Index paling efektif pada kolom dengan cardinality tinggi — banyak nilai berbeda, sehingga setiap nilai di index menunjuk ke sedikit baris. Index hampir tidak berguna pada kolom dengan cardinality rendah.

Contoh cardinality:

  Kolom 'id' (primary key)        → cardinality = jumlah baris (sangat tinggi)
  Kolom 'email' (unique)          → cardinality ≈ jumlah baris (sangat tinggi)
  Kolom 'user_id' di tabel orders → cardinality = jumlah user (tinggi)
  Kolom 'status' (5 nilai unik)   → cardinality = 5 (rendah)
  Kolom 'is_active' (boolean)     → cardinality = 2 (sangat rendah)
  Kolom 'gender'                  → cardinality = 3-4 (sangat rendah)

  Index pada 'is_active':
  → Nilai TRUE cocok dengan 50% baris
  → Nilai FALSE cocok dengan 50% baris
  → Database sering memilih full table scan karena lebih efisien
    dari menggunakan index yang menunjuk ke jutaan baris

-- Cek cardinality index yang ada
SHOW INDEX FROM orders;
-- Kolom 'Cardinality' menunjukkan estimasi jumlah nilai unik
-- Semakin tinggi → semakin efektif index tersebut

Jebakan-Jebakan yang Membuat Index Tidak Terpakai #

Ini adalah bagian yang paling penting dan paling sering tidak disadari. Kamu sudah membuat index, tapi query tetap lambat — karena ada kondisi yang membuat database memutuskan untuk tidak menggunakan index sama sekali.

Fungsi pada Kolom yang Diindex #

Menempatkan fungsi pada kolom di klausa WHERE mematikan kemampuan database untuk menggunakan index pada kolom tersebut.

-- ANTI-PATTERN: fungsi pada kolom → index tidak terpakai
SELECT * FROM orders WHERE DATE(created_at) = '2025-06-01';
-- Database tidak bisa menggunakan index pada created_at
-- karena harus mengevaluasi DATE() untuk setiap baris dulu

SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Index pada email tidak terpakai karena LOWER() mengubah nilai sebelum dibandingkan

SELECT * FROM products WHERE YEAR(released_at) = 2024;
-- Sama — YEAR() pada kolom membuat index tidak berguna

-- BENAR: ubah query agar kolom tidak dibungkus fungsi
SELECT * FROM orders
WHERE created_at >= '2025-06-01' AND created_at < '2025-06-02';
-- Index pada created_at bisa digunakan — range query pada kolom itu sendiri

SELECT * FROM users WHERE email = LOWER('[email protected]');
-- Terapkan fungsi pada nilai yang dicari, bukan pada kolom
-- Index pada email tetap bisa digunakan

SELECT * FROM products WHERE released_at >= '2024-01-01' AND released_at < '2025-01-01';
-- Range query menggantikan YEAR() — index pada released_at digunakan

Implicit Type Conversion #

Jika tipe data nilai yang dicari berbeda dari tipe kolom, database melakukan konversi otomatis — dan konversi ini bisa membuat index tidak terpakai.

-- ANTI-PATTERN: type mismatch → konversi implicit → index tidak terpakai
-- Kolom 'phone' bertipe VARCHAR
SELECT * FROM users WHERE phone = 628123456789;
-- Database mengkonversi angka ke string untuk setiap baris → full scan

-- BENAR: gunakan tipe yang sama
SELECT * FROM users WHERE phone = '628123456789';
-- Tidak ada konversi → index pada phone bisa digunakan

Leading Wildcard pada LIKE #

Wildcard di awal string pada LIKE membuat database tidak bisa menggunakan urutan B-Tree untuk menemukan kecocokan.

-- ANTI-PATTERN: wildcard di awal → index tidak terpakai
SELECT * FROM products WHERE name LIKE '%Sepatu%';
-- Database tidak tahu di mana di B-Tree harus mulai mencari
-- → full table scan

-- BENAR: wildcard di akhir → masih bisa pakai index
SELECT * FROM products WHERE name LIKE 'Sepatu%';
-- Database tahu: mulai dari nilai yang diawali 'Sepatu' di B-Tree
-- → index terpakai untuk prefix match

-- Untuk pencarian teks di mana-mana (substring), gunakan Full-Text Index:
ALTER TABLE products ADD FULLTEXT INDEX ft_products_name (name);
SELECT * FROM products WHERE MATCH(name) AGAINST('Sepatu' IN BOOLEAN MODE);

OR Condition yang Melemahkan Selectivity #

Kondisi OR antara dua kolom berbeda sering membuat query planner tidak bisa menggunakan single index secara efektif.

-- ANTI-PATTERN: OR pada dua kolom berbeda
SELECT * FROM users WHERE email = '[email protected]' OR phone = '08123456789';
-- Index pada email dan index pada phone tidak bisa digunakan bersamaan secara efisien
-- Database mungkin memilih full scan

-- BENAR: pecah menjadi dua query dan gabungkan dengan UNION
SELECT * FROM users WHERE email = '[email protected]'
UNION
SELECT * FROM users WHERE phone = '08123456789';
-- Setiap bagian query bisa menggunakan index masing-masing

Cara Membaca EXPLAIN untuk Mendiagnosis Index #

EXPLAIN adalah alat utama untuk memverifikasi apakah index digunakan dan seberapa efektif. Kamu harus terbiasa membacanya sebelum membuat atau menghapus index apapun.

-- Jalankan EXPLAIN sebelum query yang ingin diperiksa
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Output yang perlu diperhatikan:
-- ┌────────────┬────────────┬─────────────────────────┬──────────┬────────────────────────┐
-- │ type       │ key        │ rows                    │ filtered │ Extra                  │
-- ├────────────┼────────────┼─────────────────────────┼──────────┼────────────────────────┤
-- │ ALL        │ NULL       │ 5.000.000               │ 1.00     │ Using where            │
-- │ ref        │ idx_u_s    │ 150                     │ 100.00   │ Using index condition  │
-- │ const      │ PRIMARY    │ 1                       │ 100.00   │                        │
-- └────────────┴────────────┴─────────────────────────┴──────────┴────────────────────────┘

Panduan membaca kolom 'type' (dari terbaik ke terburuk):

  const    → primary key atau unique key dengan nilai tunggal. Tercepat.
  eq_ref   → join menggunakan primary key atau unique key. Sangat efisien.
  ref      → index digunakan tapi bukan unique. Efisien untuk kolom berindex.
  range    → index digunakan untuk range (BETWEEN, >, <). Masih baik.
  index    → full scan pada index (lebih cepat dari ALL, tapi tetap scan).
  ALL      → full table scan. Tidak ada index yang digunakan. ← yang harus dihindari

Panduan membaca kolom 'Extra':

  Using index         → covering index digunakan, tidak perlu baca tabel. Terbaik.
  Using where         → database filter baris setelah membaca. Wajar.
  Using filesort      → database perlu sort tambahan di memory/disk. Pertimbangkan index ORDER BY.
  Using temporary     → database buat tabel sementara. Sering muncul di GROUP BY tanpa index.
  Using index condition → Index Condition Pushdown — sebagian filter dilakukan di index level.

-- Untuk analisis lebih detail, gunakan EXPLAIN ANALYZE (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';
-- Menampilkan actual rows, actual time, dan loops — bukan hanya estimasi

Strategi Merancang Index yang Tepat #

Merancang index yang benar bukan tentang menambahkan index sebanyak mungkin — tapi tentang memahami pola query yang paling sering dan paling penting, lalu membuat index yang melayani pola tersebut seefisien mungkin.

Urutan prioritas kolom dalam composite index:

  1. Kolom equality (=) dulu — letakkan di posisi paling kiri
  2. Baru kolom range (>, <, BETWEEN) — letakkan setelah equality
  3. Kolom ORDER BY bisa jadi bagian dari index jika urutannya cocok

  Contoh query:
  WHERE user_id = 42 AND status = 'pending' AND created_at >= '2025-01-01'
  ORDER BY created_at

  Index yang tepat: (user_id, status, created_at)
  → user_id dan status sebagai equality columns (kiri)
  → created_at sebagai range column sekaligus ORDER BY (kanan)
  → Satu index melayani filter, range, dan sorting sekaligus

-- Cara menemukan query lambat yang butuh index
-- Aktifkan slow query log di MySQL:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- log query yang > 1 detik
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Atau gunakan Performance Schema untuk query yang paling banyak scan:
SELECT
    digest_text,
    count_star,
    avg_timer_wait / 1000000000 AS avg_seconds,
    sum_rows_examined / count_star AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE avg_timer_wait > 1000000000  -- lebih dari 1 detik
ORDER BY avg_timer_wait DESC
LIMIT 20;
-- Query dengan avg_rows_examined tinggi adalah kandidat yang perlu index

Mengelola Index yang Sudah Ada #

Index bukan sesuatu yang dibuat sekali lalu dilupakan. Seiring fitur berkembang dan pola query berubah, ada index yang menjadi tidak relevan — dan index yang tidak digunakan tetap membebani write operation dan storage.

-- Menemukan index yang tidak pernah digunakan (MySQL 8.0+)
SELECT
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY object_schema, object_name;
-- Index dengan count_star = 0 tidak pernah digunakan sejak server terakhir restart

-- Menghapus index yang tidak digunakan
DROP INDEX idx_yang_tidak_dipakai ON nama_tabel;

-- Menambah index baru di produksi tanpa memblokir tabel (MySQL 5.6+)
-- Algoritma INPLACE atau INSTANT memungkinkan ALTER tanpa full table lock
ALTER TABLE orders
ADD INDEX idx_orders_status_created (status, created_at),
ALGORITHM=INPLACE, LOCK=NONE;

Sebelum menghapus index berdasarkan data count_star = 0, pastikan server sudah berjalan cukup lama dan sudah melewati semua siklus penggunaan normal (termasuk proses bulanan atau kuartalan). Index yang tidak digunakan sehari-hari mungkin sangat penting untuk proses yang berjalan sekali sebulan.

Anti-Pattern yang Harus Dihindari #

-- ✗ Anti-pattern 1: index pada kolom dengan cardinality rendah
CREATE INDEX idx_users_is_active ON users(is_active);
-- is_active hanya punya dua nilai: 0 dan 1
-- Setiap nilai cocok dengan ~50% baris → database sering abaikan index ini
-- Storage terbuang, write menjadi lebih lambat, tanpa manfaat nyata

-- ✓ Solusi: gunakan composite index yang menambahkan selectivity
CREATE INDEX idx_users_active_created ON users(is_active, created_at);
-- Kombinasi is_active + created_at jauh lebih selektif

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 2: membuat index untuk setiap kolom secara terpisah
--    padahal query selalu menggunakan keduanya bersama
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
-- Query: WHERE user_id = 42 AND status = 'pending'
-- Database hanya bisa menggunakan satu index, tidak keduanya sekaligus

-- ✓ Solusi: composite index untuk pola query yang konsisten
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Satu index yang lebih efektif menggantikan dua index terpisah

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 3: composite index dengan urutan kolom yang terbalik
-- Query: WHERE user_id = 42 AND status = 'pending'
CREATE INDEX idx_wrong_order ON orders(status, user_id);
-- Karena status punya cardinality rendah (hanya beberapa nilai),
-- index ini kurang efektif dibanding (user_id, status)
-- Ditambah: query yang hanya memakai user_id tidak bisa manfaatkan index ini

-- ✓ Solusi: letakkan kolom equality dengan cardinality tinggi di kiri
CREATE INDEX idx_correct_order ON orders(user_id, status);

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 4: tidak pernah memeriksa apakah index digunakan
-- Menambah index berdasarkan perkiraan tanpa verifikasi dengan EXPLAIN
ALTER TABLE orders ADD INDEX idx_asal ON orders(category_id);
-- Mungkin tidak pernah digunakan karena query yang ada tidak cocok

-- ✓ Solusi: selalu jalankan EXPLAIN sebelum dan sesudah menambah index
-- Verifikasi: apakah kolom 'key' di EXPLAIN berubah ke index baru?
-- Verifikasi: apakah kolom 'rows' turun signifikan?
-- Verifikasi: apakah kolom 'type' berubah dari ALL ke ref atau range?

────────────────────────────────────────────────────────────────────────────────

-- ✗ Anti-pattern 5: terlalu banyak index pada tabel yang write-heavy
-- Tabel logs dengan 20 index: setiap INSERT memperbarui 21 struktur
-- Throughput INSERT turun drastis

-- ✓ Solusi: untuk tabel yang sangat write-heavy, minimkan index
-- Hanya buat index yang benar-benar dibutuhkan untuk operasi kritis
-- Pertimbangkan arsitektur yang memisahkan write store dan read store

Checklist Review Index #

SEBELUM MEMBUAT INDEX BARU:
  □ EXPLAIN sudah dijalankan dan menunjukkan query butuh index (type=ALL)
  □ Cardinality kolom yang akan diindex sudah dicek — cukup tinggi untuk efektif
  □ Tidak ada index yang sudah ada dan bisa melayani query ini
  □ Trade-off write performance sudah dipertimbangkan
  □ Untuk composite index: urutan kolom sudah dipikirkan (equality dulu, range kemudian)

VERIFIKASI SETELAH INDEX DIBUAT:
  □ EXPLAIN setelah index dibuat menunjukkan index digunakan (key = nama index)
  □ Kolom 'type' berubah dari ALL ke ref, range, atau const
  □ Kolom 'rows' turun signifikan dibanding sebelum index
  □ Query yang relevan dipastikan tidak menggunakan fungsi pada kolom berindex
  □ Tipe data dalam WHERE clause cocok dengan tipe kolom (tidak ada implicit conversion)

JEBAKAN YANG HARUS DICEK:
  □ Tidak ada fungsi (DATE(), LOWER(), YEAR()) yang membungkus kolom berindex di WHERE
  □ LIKE tidak menggunakan wildcard di awal ('%keyword')
  □ Tipe data nilai di WHERE cocok dengan tipe kolom di schema
  □ Composite index digunakan dari kolom paling kiri (leftmost prefix rule)

MAINTENANCE BERKALA:
  □ Index yang tidak pernah digunakan sudah diidentifikasi dan dievaluasi
  □ Ukuran total index per tabel dimonitor — tidak jauh melebihi ukuran data
  □ Index dievaluasi ulang setiap ada perubahan besar pada pola query atau fitur
  □ Slow query log aktif untuk mendeteksi query baru yang butuh index

Ringkasan #

Index mengubah pencarian dari O(n) menjadi O(log n) — perbedaannya bukan sekadar kecepatan, tapi apakah query selesai dalam milidetik atau timeout di tabel besar.
B-Tree index efektif untuk equality, range, prefix LIKE, dan ORDER BY — tapi tidak efektif untuk wildcard di awal, fungsi pada kolom, atau kolom dengan cardinality sangat rendah.
Setiap index memperlambat write — INSERT, UPDATE, DELETE harus memperbarui semua index terkait. Tabel dengan banyak index adalah tabel yang write-nya mahal.
Composite index mengikuti leftmost prefix rule — database hanya bisa menggunakan index dari kolom paling kiri secara berurutan. Urutan kolom dalam composite index sangat menentukan query mana yang bisa memanfaatkannya.
Covering index menghilangkan kebutuhan akses ke tabel — jika semua kolom yang dibutuhkan query sudah ada di index, database tidak perlu membaca tabel sama sekali.
Fungsi pada kolom di WHERE membunuh index — DATE(created_at), LOWER(email), YEAR(released_at) semua membuat index pada kolom tersebut tidak terpakai. Tulis ulang query agar kolom tidak dibungkus fungsi.
Cardinality rendah = index tidak efektif — index pada kolom boolean atau gender hampir tidak pernah memberikan manfaat nyata. Database sering memilih full scan.
EXPLAIN adalah alat wajib — selalu jalankan EXPLAIN sebelum membuat index dan setelah membuat index. Pastikan type bukan ALL dan key menunjukkan index yang diharapkan.
Index butuh maintenance — index yang tidak pernah digunakan tetap membebani write dan storage. Audit index secara berkala dan hapus yang tidak relevan.
Rancang index berdasarkan query nyata — bukan berdasarkan feeling atau “kolom ini kelihatannya penting”. Lihat slow query log, gunakan EXPLAIN, baru putuskan.

← Sebelumnya: Locking Berikutnya: Connection Pooling →