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
| Aspek | Single Index | Composite Index |
|---|---|---|
| Definisi | Index pada 1 kolom | Index pada 2+ kolom |
| Query yang dilayani | Filter 1 kolom saja | Filter 1, 2, atau N kolom (sesuai leftmost prefix) |
| Ukuran index | Kecil | Lebih besar (menyimpan kombinasi nilai) |
| Index maintenance | Ringan | Lebih berat saat INSERT/UPDATE |
| Kapan pakai | Query selalu filter 1 kolom | Query filter banyak kolom, atau ingin covering index |
| Covering index | Sulit dicapai | Mudah dicapai (semua kolom SELECT ada di 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:
- Menggunakan index
user_iduntuk menemukan baris yang cocok - Melakukan table lookup untuk mengambil kolom
statusdantotal
Tapi jika kita punya composite index (user_id, status, total), MySQL bisa:
- Menggunakan index untuk menemukan baris yang cocok
- Mengambil
statusdantotallangsung 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?
| Situasi | Rekomendasi |
|---|---|
| Query selalu filter 1 kolom | Single index |
| Query filter 2+ kolom dengan AND | Composite index |
| Query butuh covering index | Composite index (include kolom SELECT) |
| Query filter dengan OR antar kolom berbeda | Single index per kolom (index merge) |
| Tabel INSERT-heavy (write performance kritikal) | Hati-hati dengan composite index besar |
| Ingin kurangi jumlah index | Composite index (1 index bisa gantikan beberapa single 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.
Komentar Terbaru