Membaca Hasil EXPLAIN MySQL: Memahami Execution Plan untuk Optimasi Query

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

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:

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

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

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:

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