Index & Performa — Database

Apa itu Index? Index adalah struktur data yang mempercepat pencarian data di tabel, mirip seperti indeks di belakang buku. Tanpa index, database harus membaca…

Index adalah daftar terurut berisi nilai sebuah kolom beserta penunjuk ke barisnya, supaya database bisa langsung lompat ke baris yang dicari tanpa membaca seluruh tabel. Di pelajaran SQL Index & Performa kamu sudah membuat index satu kolom dan melihat SCAN berubah jadi SEARCH. Di sini kita merancang-nya: kolom mana yang layak di-index, dan kalau satu index berisi dua kolom, urutannya bagaimana. Bayangin buku telepon yang diurutkan berdasarkan nama keluarga, lalu nama depan. Mencari "Santoso, Budi" cepat sekali. Tapi mencari semua orang bernama depan "Budi" tetap harus membaca buku dari halaman pertama sampai terakhir.

🧪 Tebakan cepat: tabel orders punya index (customer_id, created_at). Query-nya cuma WHERE created_at >= '2026-10-01', tanpa customer_id. SQLite memakai index itu, atau membaca seluruh tabel? Tebak dulu, jawabannya ada di bagian "Urutan kolom: leftmost prefix".

Tabel yang kita pakai

Kedai Senja sudah ramai. Tabel orders berisi 20.000 pesanan dari 200 pelanggan, 1 Januari sampai 7 Oktober 2026:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,       -- 'selesai' atau 'pending'
  created_at TEXT NOT NULL,   -- tanggal pesanan, '2026-10-02'
  total INTEGER NOT NULL
);

Siti (customer_id 2) punya 100 pesanan, 2 di antaranya sejak 1 Oktober. Halaman "Pesananku bulan ini" menjalankan:

SELECT * FROM orders
WHERE customer_id = 2 AND created_at >= '2026-10-01';

Fungsi utama index adalah mempercepat pencarian data, yaitu SELECT dengan WHERE, JOIN dan ORDER BY. Kita bandingkan tiga desain index dengan EXPLAIN QUERY PLAN di SQLite:

Index Plan dari SQLite Entri yang dilewati
belum ada SCAN orders 20.000
(customer_id) SEARCH orders USING INDEX idx_orders_customer (customer_id=?) 100
(customer_id, created_at) SEARCH orders USING INDEX idx_orders_customer_date (customer_id=? AND created_at>?) 2

Angka di kolom terakhir adalah jumlah baris yang cocok dengan bagian index yang dipakai plan. Index satu kolom sudah memangkas 20.000 jadi 100, tapi 98 di antaranya pesanan Siti bulan lain yang dibuang setelah dibaca.

Composite index: satu index, dua kolom

CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at);

Urutan kolom: leftmost prefix

Index (customer_id, created_at) cuma bisa dipakai mulai dari kolom paling kiri. Ini aturan leftmost prefix:

Query Pakai index? Plan
WHERE customer_id = 2 ya SEARCH ... (customer_id=?)
WHERE customer_id = 2 AND created_at >= '2026-10-01' ya, dua kolom SEARCH ... (customer_id=? AND created_at>?)
WHERE created_at >= '2026-10-01' gak SCAN orders

Tanpa customer_id, pesanan bulan Oktober tersebar di 200 tempat berbeda di index, sama seperti nama depan "Budi" di buku telepon. Jadi SQLite membaca seluruh tabel. Itu jawaban tebakannya.

Kalau urutannya dibalik jadi (created_at, customer_id), query Siti tadi jadi SEARCH ... (created_at>?): SQLite melewati 500 entri (semua pesanan sejak 1 Oktober) untuk menemukan 2 milik Siti, dan query WHERE customer_id = 2 saja jadi SCAN. Aturan praktisnya: kolom yang dicari dengan = di depan, kolom rentang (>=, BETWEEN) atau ORDER BY di belakang. Aturan leftmost prefix ini sama di MySQL dan PostgreSQL.

Kolom mana yang layak di-index

Dua pertanyaan menentukan: kolomnya dipakai di mana, dan berapa banyak nilai uniknya (cardinality).

Kolomnya... Contoh Saran
cuma ditampilkan di SELECT, gak pernah dicari total tidak perlu index
dipakai di WHERE, JOIN atau ORDER BY, nilainya banyak yang unik (high cardinality) customer_id (200 nilai), email buat index
dipakai di WHERE, tapi nilainya sedikit (low cardinality) status (2 nilai), kolom boolean pertimbangkan partial index

Index biasa di status kurang membantu: 'selesai' ada di 19.600 baris, jadi "lewat index" tetap berarti membaca hampir semuanya. Yang sering dicari justru yang langka, misalnya 400 pesanan 'pending'. Untuk itu ada partial index, index yang cuma berisi baris yang memenuhi syarat:

CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';

Index ini cuma berisi 400 entri, dan SELECT * FROM orders WHERE status = 'pending' dijawab dengan SCAN orders USING INDEX idx_orders_pending: membaca 400 entri itu saja, bukan 20.000 baris. SQLite dan PostgreSQL punya partial index, MySQL belum.

Harga sebuah index

Setiap index harus ikut diperbarui saat data berubah. Index memperlambat INSERT, UPDATE dan DELETE, dan memakan ruang penyimpanan. Tabel dengan 4 index berarti satu INSERT menulis ke 5 tempat. Saat mengimpor jutaan baris sekaligus, tim sering menghapus index dulu lalu membuatnya lagi setelah impor, karena membangun index sekali jauh lebih cepat daripada memperbaruinya jutaan kali.

Di migration Laravel:

$table->string('email')->index();                // index satu kolom
$table->index(['customer_id', 'created_at']);   // composite, urutan array = urutan kolom

⚠️ Jebakan yang sering ketemu

TL;DR: Index mempercepat pencarian (SELECT dengan WHERE, JOIN, ORDER BY) tapi memperlambat INSERT, UPDATE, DELETE. Composite index (customer_id, created_at) diurutkan per kolom dari kiri, jadi melayani customer_id saja atau customer_id + created_at (20.000 entri jadi 2), tapi gak melayani created_at saja (SCAN): itu aturan leftmost prefix. Kolom = di depan, kolom rentang di belakang. Kolom di WHERE/JOIN/ORDER BY dengan high cardinality: buat index. Kolom yang cuma di-SELECT: tidak perlu index. Low cardinality: pertimbangkan partial index (... WHERE status = 'pending'). Di Laravel: ->index() atau $table->index([...]).

Yang akan kamu pelajari