Pernah nggak sih ngerasa query MySQL kamu lambat banget padahal data baru ratusan ribu baris? Saya pernah kena masalah ini waktu bikin aplikasi inventory query SELECT yang cuma nampilin daftar produk butuh waktu 4-5 detik. User komplain terus, "kenapa loadingnya lama?". Setelah dicek, ternyata tabelnya nggak punya index sama sekali.

Index di MySQL itu kayak daftar isi di buku. Kalau kamu mau cari bab tertentu, nggak mungkin kan baca dari halaman 1 sampai ketemu? Kamu langsung lompat ke halaman yang tepat. Nah, index bekerja dengan cara yang mirip dia bikin MySQL nggak perlu scan semua baris buat nyari data yang kamu butuh.

Kenapa Query Kamu Lambat Tanpa Index

Bayangkan tabel products dengan 500.000 baris. Kamu jalankan query ini:

SELECT * FROM products WHERE category_id = 5 AND status = 'active';

Tanpa index, MySQL harus baca semua 500.000 baris satu per satu (full table scan). Ini yang bikin query lambat. Coba jalankan EXPLAIN buat lihat apa yang terjadi:

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND status = 'active';

Kalau di kolom type muncul ALL, berarti MySQL lagi full table scan. Itu artinya dia baca SEMUA baris. Kolom rows juga nunjukin estimasi berapa baris yang harus di-scan kalau angkanya ratusan ribu, wajar aja query-nya lambat.

Untuk lebih jelasnya, saya biasanya pakai EXPLAIN FORMAT=JSON buat lihat detail cost-nya:

EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 5 AND status = 'active';

Di output JSON-nya, perhatikan query_cost. Angka ini representasi biaya eksekusi query makin kecil makin bagus. Tanpa index, cost-nya bisa ratusan ribu atau jutaan.

Jenis-Jenis Index di MySQL

MySQL punya beberapa jenis index. Yang paling sering dipakai ada tiga:

1. Primary Key Index

Ini otomatis dibuat waktu kamu bikin kolom PRIMARY KEY. Biasanya pakai auto-increment integer. Setiap tabel InnoDB pasti punya ini kalau nggak didefinisikan, MySQL bikin hidden primary key sendiri.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    category_id INT,
    price DECIMAL(10,2),
    status VARCHAR(20),
    created_at DATETIME
);

2. Index Biasa (Non-Unique Index)

Ini yang paling sering kamu tambahin sendiri. Fungsinya buat mempercepat pencarian berdasarkan kolom tertentu.

-- Single column index
CREATE INDEX idx_category ON products(category_id);

-- Composite index (multi-column)
CREATE INDEX idx_cat_status ON products(category_id, status);

3. Unique Index

Selain mempercepat pencarian, juga memastikan nggak ada duplikat. Cocok buat email, username, atau SKU produk.

CREATE UNIQUE INDEX idx_email ON users(email);

Composite Index - Senjata Rahasia Query Cepat

Composite index itu index yang mencakup lebih dari satu kolom. Ini yang bikin perbedaan paling gede di performa. Tapi ada aturan penting: urutan kolom di index harus sesuai dengan urutan di WHERE clause.

Contoh, kamu bikin composite index ini:

CREATE INDEX idx_cat_status ON products(category_id, status);

Query ini bakal pakai index dengan optimal:

-- Cocok dengan index (category_id duluan, status sesudah)
SELECT * FROM products WHERE category_id = 5 AND status = 'active';

Tapi query ini nggak bisa pakai index secara maksimal:

-- Tidak optimal! MySQL skip category_id, langsung cari status
SELECT * FROM products WHERE status = 'active';

Kenapa? Karena composite index itu kayak kolom telepon yang diurutkan berdasarkan nama dulu, baru nomor. Kalau kamu cuma tahu nomor teleponnya tapi nggak tahu namanya, daftar telepon nggak banyak membantu.

Ini konsep yang disebut leftmost prefix rule. MySQL cuma bisa pakai index dari kolom paling kiri. Jadi kalau index-nya (category_id, status, created_at), MySQL bisa pakai untuk query yang pakai:

• WHERE category_id = 5 pakai index
• WHERE category_id = 5 AND status = 'active' pakai index
• WHERE category_id = 5 AND status = 'active' AND created_at > '2026-01-01' pakai index penuh
• WHERE status = 'active' NGGAK pakai index (skip kolom pertama)

Praktik Langsung: Optimasi Query dari 4 Detik ke 30ms

Saya kasih contoh kasus nyata. Tabel orders dengan 1 juta baris:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT,
    total DECIMAL(12,2),
    status VARCHAR(20),
    payment_method VARCHAR(30),
    created_at DATETIME,
    INDEX idx_user (user_id)
);

Query yang sering dipakai di dashboard admin:

SELECT * FROM orders 
WHERE status = 'completed' 
AND payment_method = 'bank_transfer'
AND created_at BETWEEN '2026-01-01' AND '2026-06-30'
ORDER BY created_at DESC
LIMIT 50;

Waktu eksekusi: 4.2 detik. Coba cek dengan EXPLAIN:

EXPLAIN SELECT * FROM orders 
WHERE status = 'completed' 
AND payment_method = 'bank_transfer'
AND created_at BETWEEN '2026-01-01' AND '2026-06-30'
ORDER BY created_at DESC
LIMIT 50;

-- Output:
-- type: ALL
-- rows: 1000000
-- Extra: Using where; Using filesort

Dua masalah di sini: ALL (full table scan) dan Using filesort (MySQL harus mengurutkan hasil secara manual). Solusinya bikin composite index yang mencakup semua kolom di WHERE dan ORDER BY:

CREATE INDEX idx_status_payment_date ON orders(status, payment_method, created_at);

Jalankan lagi query yang sama, dan cek EXPLAIN:

EXPLAIN SELECT * FROM orders 
WHERE status = 'completed' 
AND payment_method = 'bank_transfer'
AND created_at BETWEEN '2026-01-01' AND '2026-06-30'
ORDER BY created_at DESC
LIMIT 50;

-- Output:
-- type: range
-- rows: 48
-- Extra: Using index condition

Hasilnya? 30ms. Dari 4.2 detik turun ke 30ms itu peningkatan 140x lipat. Kolom type berubah dari ALL jadi range, dan rows dari 1 juta jadi cuma 48. Using filesort juga hilang karena MySQL sudah tahu urutannya dari index.

Cara Baca EXPLAIN dengan Benar

EXPLAIN itu tool paling penting buat optimasi query. Tapi banyak yang nggak tahu cara bacanya. Ini panduan singkat:

Kolom type urutan dari paling buruk ke paling bagus:

• ALL full table scan, paling lambat
• index full index scan, lebih cepat tapi tetap scan semua
• range scan sebagian index (pakai BETWEEN, >, <, IN)
• ref ambil baris berdasarkan referensi index
• eq_ref ambil tepat satu baris (untuk primary key / unique)
• const konstan, paling cepat

Kolom rows estimasi baris yang di-scan. Makin kecil makin bagus. Kalau angkanya jutaan padahal LIMIT cuma 50, ada masalah.

Kolom Extra perhatikan flag ini:

• Using index bagus! MySQL nggak perlu baca data dari tabel (covering index)
• Using where MySQL filter setelah baca data (normal)
• Using filesort buruk! MySQL harus sort manual, tambah index
• Using temporary buruk! MySQL bikin tabel temporary, biasanya karena GROUP BY tanpa index

Kesalahan Umum saat Membuat Index

1. Terlalu banyak index

Setiap index yang kamu tambah bikin INSERT, UPDATE, dan DELETE jadi lebih lambat. Karena setiap kali data berubah, MySQL harus update semua index juga. Aturan praktisnya: maksimal 5-6 index per tabel.

-- Jangan gini: bikin index untuk SETIAP kolom
CREATE INDEX idx_name ON products(name);
CREATE INDEX idx_price ON products(price);
CREATE INDEX idx_stock ON products(stock);
CREATE INDEX idx_weight ON products(weight);
CREATE INDEX idx_color ON products(color);
-- Ini bikin INSERT lambat karena 5 index harus diupdate

Lebih baik bikin composite index yang menutupi beberapa query sekaligus.

2. Index pada kolom dengan sedikit nilai unik

Kolom seperti gender (male/female) atau is_active (0/1) nggak cocok di-index. Kenapa? Karena index cuma berguna kalau bisa menyaring banyak baris. Kalau kamu cari gender = 'male', hasilnya tetap ~50% dari total baris MySQL lebih baik full table scan.

Tapi ada pengecualian: kalau kombinasi kolom yang jarang dipakai secara bersamaan (misalnya status = 'pending' AND priority = 'high'), composite index tetap berguna karena kombinasinya lebih selektif.

3. Nggak pakai covering index

Covering index itu index yang sudah mencakup semua kolom yang kamu SELECT. MySQL nggak perlu lagi lookup ke tabel utama, jadi lebih cepat.

-- Query ini sering dipakai
SELECT product_id, status, total FROM orders WHERE user_id = 100;

-- Index biasa (MySQL masih harus lookup ke tabel buat ambil status dan total)
CREATE INDEX idx_user ON orders(user_id);

-- Covering index (MySQL bisa ambil semua data dari index saja)
CREATE INDEX idx_user_cover ON orders(user_id, product_id, status, total);

Cek apakah query pakai covering index di EXPLAIN kalau kolom Extra menunjukkan Using index, berarti berhasil.

Monitoring Index yang Sudah Dibuat

Setelah bikin index, jangan lupa pantau apakah index-nya benar-benar dipakai. MySQL menyimpan statistik ini di performance_schema:

-- Cek index usage per tabel
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    count_fetch,
    count_insert,
    count_update,
    count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'kinara_project'
AND object_name = 'orders'
ORDER BY count_read DESC;

Kalau ada index yang count_read = 0 tapi count_insert atau count_update tinggi, berarti index itu nggak pernah dipakai tapi bikin operasi tulis lambat. Pertimbangkan buat hapus:

-- Hapus index yang nggak dipakai
DROP INDEX idx_unused_column ON orders;

Kamu juga bisa lihat ukuran index di tabel:

SELECT 
    database_name,
    table_name,
    index_name,
    stat_value * @@innodb_page_size / 1024 / 1024 AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
AND database_name = 'kinara_project'
ORDER BY stat_value DESC;

Tips Tambahan: Optimasi Query Tanpa Index

Kadang kamu nggak bisa bikin index karena tabelnya terlalu besar atau sering ditulis. Beberapa trik alternatif:

Pagination pakai cursor, bukan OFFSET

-- Lambat di halaman yang jauh (MySQL harus scan semua baris sebelum offset)
SELECT * FROM orders ORDER BY id DESC LIMIT 50 OFFSET 100000;

-- Lebih cepat dengan cursor-based pagination
SELECT * FROM orders WHERE id < 100000 ORDER BY id DESC LIMIT 50;

Pakai subquery untuk filter dulu

-- Lambat: JOIN dulu, baru filter
SELECT o.*, u.name FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.status = 'completed';

-- Lebih cepat: filter dulu di subquery
SELECT o.*, u.name FROM (
    SELECT * FROM orders WHERE status = 'completed'
) o JOIN users u ON o.user_id = u.id;

Kalau kamu tertarik belajar lebih lanjut tentang optimasi MySQL, saya juga sudah nulis tentang perbandingan MySQL vs PostgreSQL vs SQLite yang bisa jadi referensi pilih database engine yang tepat.

Kesimpulan

Index itu investasi di performa database. Waktu bikin tabel, jangan langsung asal tambah index pikirkan dulu query apa yang paling sering dijalankan. Pakai EXPLAIN buat diagnose masalah, bikin composite index yang sesuai dengan pola query, dan hapus index yang nggak dipakai.

Ingat, index bukan silver bullet. Ada trade-off: bikin SELECT lebih cepat tapi INSERT/UPDATE/DELETE lebih lambat. Pahami kebutuhan aplikasi kamu, lalu bikin index yang tepat sasaran. Kalau query kamu udah di bawah 100ms untuk tabel berisi jutaan baris, kamu udah di jalur yang benar.