Single Index vs Composite Index di MySQL: Kapan Pakai yang Mana?

Hari ini saya belajar tentang perbedaan single index dan composite index di MySQL. Topik ini sering muncul saat optimasi query yang lambat — saya sendiri kadang bingung kapan harus pakai yang mana. Catatan ini saya rangkum dari beberapa sumber, termasuk mysqltutorial.org, dev.to, dan datacamp.com. Semoga bermanfaat.

Apa itu Single Index?

Single index adalah indeks yang dibuat pada satu kolom saja. Ini adalah jenis indeks yang paling sederhana dan paling sering digunakan. Contohnya:

-- Single index pada kolom email
CREATE INDEX idx_users_email ON users(email);

-- Single index pada kolom status
CREATE INDEX idx_orders_status ON orders(status);

Single index cocok untuk query yang hanya melakukan filter pada satu kolom, seperti:

SELECT * FROM users WHERE email = 'rudy@example.com';
SELECT * FROM orders WHERE status = 'pending';

Kelebihannya: sederhana, mudah dipahami, dan index maintenance-nya ringan. Kekurangannya: tidak bisa membantu query yang filter pada banyak kolom sekaligus.

Apa itu Composite Index?

Composite index (atau multiple-column index) adalah indeks yang dibuat pada dua kolom atau lebih. MySQL memungkinkan composite index hingga 16 kolom. Contohnya:

-- Composite index pada kolom (status, created_at)
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- Composite index pada kolom (user_id, product_id, quantity)
CREATE INDEX idx_order_items_composite ON order_items(user_id, product_id, quantity);

Composite index sangat powerful karena bisa melayani banyak jenis query dengan satu indeks saja. Tapi ada aturan penting yang harus dipahami: leftmost prefix rule.

Leftmost Prefix Rule: Aturan Terpenting

Ini adalah konsep kunci yang harus dipahami saat menggunakan composite index. MySQL hanya bisa menggunakan composite index jika query melakukan filter pada kolom-kolom yang dimulai dari kolom paling kiri (leftmost).

Misalnya kita punya composite index (status, created_at, user_id):

-- ✅ BISA menggunakan index (leftmost prefix: status)
SELECT * FROM orders WHERE status = 'pending';

-- ✅ BISA menggunakan index (leftmost prefix: status, created_at)
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01';

-- ✅ BISA menggunakan index (leftmost prefix: status, created_at, user_id)
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01' AND user_id = 100;

-- ❌ TIDAK BISA menggunakan index (skip kolom pertama: status)
SELECT * FROM orders WHERE created_at > '2026-01-01';

-- ❌ TIDAK BISA menggunakan index (skip kolom pertama: status)
SELECT * FROM orders WHERE created_at > '2026-01-01' AND user_id = 100;

Bayangkan composite index seperti buku telepon yang diurutkan berdasarkan: Kota → Kecamatan → Nama. Kamu bisa mencari semua orang di “Jakarta”, atau semua orang di “Jakarta Selatan”, tapi kamu tidak bisa langsung mencari semua orang di “Kecamatan X” tanpa menyebut kota dulu.

Perbandingan: Single vs Composite Index

Covering Index: Keunggulan Tersembunyi Composite Index

Ada satu konsep penting yang sering terlewat: covering index. Covering index terjadi ketika semua kolom yang diminta oleh query sudah tersedia di dalam index, tanpa perlu melakukan “table lookup” (mengakses data di tabel utama).

Contoh: kita punya tabel orders dengan kolom id, user_id, status, total. Query ini:

SELECT status, total FROM orders WHERE user_id = 100;

Jika kita hanya punya single index pada user_id, MySQL akan:

1. Menggunakan index user_id untuk menemukan baris yang cocok
2. Melakukan table lookup untuk mengambil kolom status dan total

Tapi jika kita punya composite index (user_id, status, total), MySQL bisa:

1. Menggunakan index untuk menemukan baris yang cocok
2. Mengambil status dan total langsung dari index (tidak perlu table lookup)

Ini jauh lebih cepat karena mengurangi I/O operasi. Anda bisa melihatnya di EXPLAIN — jika kolom Extra tertulis Using index, berarti query menggunakan covering index.

Urutan Kolom dalam Composite Index

Urutan kolom dalam composite index sangat kritikal. Berikut best practices yang saya temukan:

1. Kolom equality filter di depan, range filter di belakang

-- ✅ BAIK: status (= equality) di depan, created_at (range) di belakang
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- Query yang bisa manfaatkan index ini:
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01';

2. Kolom dengan HIGH cardinality di depan

Cardinality adalah jumlah nilai unik dalam suatu kolom. Kolom dengan cardinality tinggi (banyak nilai unik) sebaiknya diletakkan di depan karena lebih efektif mempersempit hasil pencarian.

-- email punya cardinality tinggi (setiap user berbeda)
-- status punya cardinality rendah (hanya: pending, shipped, delivered)

-- ✅ BAIK: email di depan
CREATE INDEX idx_users_email_status ON users(email, status);

-- ❌ KURANG IDEAL: status di depan (terlalu banyak baris yang status = 'active')
CREATE INDEX idx_users_status_email ON users(status, email);

3. Pertimbangkan query yang paling sering dijalankan

Urutan kolom harus disesuaikan dengan pola query yang paling sering dijalankan di aplikasi Anda. Jika query paling sering filter berdasarkan user_id lalu created_at, buat index (user_id, created_at).

Kapan pakai Single Index vs Composite Index?

Contoh Praktis

Misalnya kita punya tabel posts dengan kolom: id, author_id, category_id, status, created_at, title.

Scenario 1: Query filter satu kolom

-- Query ini hanya filter berdasarkan author_id
SELECT * FROM posts WHERE author_id = 5;

-- Single index sudah cukup
CREATE INDEX idx_posts_author ON posts(author_id);

Scenario 2: Query filter dua kolom + ORDER BY

-- Query ini filter author_id + status, lalu ORDER BY created_at
SELECT * FROM posts WHERE author_id = 5 AND status = 'published' ORDER BY created_at DESC;

-- Composite index (author_id, status, created_at)
-- - author_id & status untuk WHERE (equality)
-- - created_at untuk ORDER BY (menghindari filesort)
CREATE INDEX idx_posts_author_status_created ON posts(author_id, status, created_at);

Scenario 3: Query dengan SELECT spesifik (covering index)

-- Query ini hanya butuh title, tidak perlu SELECT *
SELECT title FROM posts WHERE author_id = 5 AND status = 'published';

-- Composite index yang "cover" semua kolom yang diakses
CREATE INDEX idx_posts_cover ON posts(author_id, status, title);

-- EXPLAIN akan tunjukkan "Using index" (covering index!)
EXPLAIN SELECT title FROM posts WHERE author_id = 5 AND status = 'published';

Kesalahan Umum yang Sering Terjadi

Berdasarkan pengalaman dan bacaan, berikut beberapa kesalahan umum yang sering dilakukan:

1. Membuat terlalu banyak single index

-- ❌ Terlalu banyak index
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_created ON orders(created_at);

-- ✅ Lebih baik: composite index yang melayani semua query
CREATE INDEX idx_orders_composite ON orders(user_id, status, created_at);

2. Urutan kolom yang salah

-- Query paling sering: WHERE status = 'pending' AND user_id = 5
-- Tapi index dibuat: (user_id, status)

-- ❌ Index kurang optimal
CREATE INDEX idx_orders_bad ON orders(user_id, status);

-- ✅ Index lebih optimal (status di depan karena equality filter)
CREATE INDEX idx_orders_good ON orders(status, user_id);

3. Tidak menggunakan EXPLAIN

Selalu gunakan EXPLAIN sebelum dan sesudah membuat index untuk memastikan index benar-benar digunakan:

EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01';

-- Perhatikan kolom:
-- - type: ref, range, atau eq_ref (bagus) vs ALL (full scan = buruk)
-- - key: nama index yang digunakan
-- - Extra: "Using index" = covering index (bagus sekali)

Refleksi Saya

Belajar tentang index ini membuat saya sadar: index bukan sekadar “tambah biar cepat”. Ada ilmu di balik urutan kolom, pemilihan kolom, dan pemahaman pola query. Composite index memang lebih powerful, tapi harus dipahami aturan leftmost prefix-nya. Kalau tidak, index yang sudah dibuat malah tidak terpakai.

Satu hal yang paling berkesan: EXPLAIN adalah sahabat terbaik. Sebelum optimasi, jalankan EXPLAIN dulu. Sesudah buat index, jalankan lagi. Kalau type masih ALL (full table scan), berarti index belum optimal. Semoga catatan ini bermanfaat, dan semoga saya bisa mengaplikasikan ilmu ini di proyek-proyek berikutnya.

Sumber:
• MySQL Composite Index — mysqltutorial.org
• Understanding MySQL Composite Indexes — DEV Community (Chat2DB)
• MySQL Covering Indexes — DataCamp
• How to Choose the Right Column Order for Composite Indexes — OneUptime