Index & Performa — SQL

Index mempercepat pencarian data — seperti indeks di buku yang membantu menemukan halaman tanpa membaca semua. Membuat index: CREATE INDEX idx_users_email ON…

Index adalah daftar tambahan yang dibikin database supaya dia bisa langsung lompat ke baris yang dicari, tanpa membaca seluruh tabel. Bayangin indeks di halaman belakang buku resep: mau cari "rendang", kamu buka indeksnya, ketemu "rendang, halaman 112", lalu langsung buka halaman 112. Tanpa indeks itu, kamu harus membalik buku dari halaman pertama sampai ketemu.

🧪 Tebakan cepat: tabel orders punya 100.000 baris, dan cuma 2 yang milik customer_id = 2. Tanpa index, berapa baris yang dibaca database untuk menemukan dua baris itu: 2, sekitar 50.000, atau 100.000? Tebak dulu, jawabannya ada di bagian "Tanpa index, semua baris dibaca".

Tabel yang kita pakai

Tabel orders dari latihan, berisi pesanan Budi (customer_id 1), Siti (2) dan Andi (3):

id	customer_id	product	amount
1	1	Laptop	12000000
2	2	Mouse	150000
3	2	Keyboard	500000
4	3	Monitor	3500000

Query yang sering jalan di toko ini: "tampilkan semua pesanan customer 2".

SELECT * FROM orders WHERE customer_id = 2;

Hasilnya 2 baris: Mouse dan Keyboard.

Tanpa index, semua baris dibaca

Database belum tahu di mana baris milik customer 2 berada. Jadi dia membaca baris 1, 2, 3, 4 satu per satu dan mengecek customer_id-nya. Ini namanya full table scan. Di tabel 4 baris gak kerasa. Di tabel 100.000 baris, dia tetap membaca 100.000 baris walau hasilnya cuma 2. Itu jawaban tebakannya.

Kamu bisa minta database menjelaskan rencana (plan) yang dia pakai untuk sebuah query. Kata kuncinya EXPLAIN, artinya "jelaskan". Di SQLite, yang dipakai latihan ini, bentuk yang enak dibaca adalah EXPLAIN QUERY PLAN di depan query:

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;

SCAN orders

SCAN orders artinya "baca seluruh tabel orders dari awal sampai akhir". Query-nya sendiri gak dijalankan, yang keluar cuma rencananya.

Bikin index dengan CREATE INDEX

CREATE INDEX idx_orders_customer ON orders(customer_id);

CREATE INDEX membuat index baru.
idx_orders_customer adalah nama index-nya. Namanya bebas, tapi kebiasaannya idx_<tabel>_<kolom> supaya gampang dikenali.
ON orders(customer_id) artinya index ini untuk kolom customer_id di tabel orders.

Index menyimpan semua nilai customer_id dalam keadaan terurut, dan tiap nilai menunjuk ke barisnya: 1 ke baris 1, 2 ke baris 2 dan 3, 3 ke baris 4. Karena terurut, database bisa menemukan angka 2 dengan cepat, seperti kamu mencari kata di kamus tanpa membaca dari halaman pertama.

Jalankan EXPLAIN QUERY PLAN yang sama sekali lagi:

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

SEARCH ... USING INDEX artinya database gak membaca seluruh tabel lagi, tapi mencari lewat index. Query SELECT-nya gak perlu diubah sama sekali: database memilih sendiri kapan index dipakai.

Di Lab, dengan 100.000 baris di SQLite sungguhan, query yang sama butuh sekitar 4 ms tanpa index dan sekitar 0,1 ms dengan index. Angka pastinya tergantung perangkatmu, tapi bedanya puluhan kali lipat.

Harga sebuah index

Index mempercepat operasi baca: SELECT dengan WHERE, JOIN, dan ORDER BY. Tapi index adalah salinan kolom yang harus selalu ikut diperbarui. Setiap INSERT, UPDATE atau DELETE di orders juga mengubah idx_orders_customer. Jadi index memperlambat operasi tulis sedikit, dan memakan ruang penyimpanan. Tabel dengan 3 index berarti satu INSERT menulis ke 4 tempat: tabelnya, ditambah 3 index-nya.

Kapan bikin index

Bikin index	Gak perlu
Kolom yang sering ada di `WHERE`, misalnya `email` untuk login	Kolom yang hampir gak pernah dicari
Kolom yang dipakai `JOIN`, seperti `customer_id`	Tabel kecil, misalnya tabel pengaturan berisi 10 baris
Kolom yang sering dipakai `ORDER BY`	Tabel yang jauh lebih sering ditulis daripada dibaca, seperti log

Kolom PRIMARY KEY seperti id sudah otomatis cepat dicari, jadi gak perlu index lagi.

📌 EXPLAIN di MySQL dan PostgreSQL

Di MySQL dan PostgreSQL, cukup tulis EXPLAIN di depan query: EXPLAIN SELECT * FROM users WHERE email = '[email protected]';. Bentuk hasilnya beda. MySQL menampilkan tabel dengan kolom seperti type dan rows, PostgreSQL menulis Seq Scan (baca semua) atau Index Scan (lewat index). SQLite juga punya EXPLAIN polos, tapi isinya instruksi mesin yang susah dibaca, makanya pakai EXPLAIN QUERY PLAN. Index biasa di ketiganya berbentuk B-tree, semacam pohon yang isinya terurut. PostgreSQL punya jenis lain juga (misalnya GIN untuk mencari di dalam teks atau JSON), tapi B-tree sudah cukup untuk hampir semua kebutuhan.

⚠️ Jebakan yang sering ketemu

Index di kolom yang salah. Index di customer_id gak membantu WHERE product = 'Mouse', plan-nya tetap SCAN orders. Index cuma membantu pencarian di kolom yang di-index.
Nama index beda dari yang diminta. Latihannya mengecek index bernama persis idx_orders_customer.
Lupa query-nya. CREATE INDEX gak menampilkan apa-apa. Latihannya juga mengecek hasil SELECT: 2 baris, salah satunya Mouse.
Index di semua kolom. Tiap index menambah kerja di setiap INSERT dan UPDATE. Bikin hanya untuk kolom yang memang sering dicari.

TL;DR: Tanpa index, database membaca semua baris (SCAN), walau hasilnya cuma 2 dari 100.000. CREATE INDEX idx_orders_customer ON orders(customer_id); bikin daftar terurut untuk kolom itu, dan plan-nya berubah jadi SEARCH orders USING INDEX. Cek plan dengan EXPLAIN QUERY PLAN di SQLite, atau EXPLAIN di MySQL dan PostgreSQL. Index mempercepat baca tapi memperlambat tulis sedikit, jadi pasang di kolom yang sering ada di WHERE, JOIN dan ORDER BY, bukan di semua kolom.