Use EXPLAIN #

Query yang terlihat biasa-biasa saja bisa menjadi bom waktu di production. Ketika data masih ribuan baris, semua terasa cepat — tapi ketika sudah menyentuh jutaan baris dengan ratusan concurrent user, query yang salah bisa membuat seluruh aplikasi merangkak. Masalahnya, kamu tidak bisa memperbaiki sesuatu yang tidak kamu pahami. EXPLAIN adalah perintah yang memungkinkan kamu melihat bagaimana database benar-benar mengeksekusi query, bukan sekadar apa hasilnya. Artikel ini membahas cara membaca output EXPLAIN, pola masalah yang paling sering muncul, dan bagaimana memperbaikinya dengan pendekatan yang berbasis data — bukan tebak-tebakan.

Mengapa Query Lambat Sulit Didiagnosis Tanpa Alat #

Sebagian besar engineer pertama kali menyadari ada masalah performa dari laporan user, bukan dari monitoring. Itu sendiri sudah menunjukkan gap yang serius: masalah sudah ada sejak lama, tapi tidak terdeteksi. Query lambat punya karakteristik yang membuatnya sulit dilacak tanpa alat yang tepat.

Pertama, query lambat tidak selalu konsisten. Di staging dengan data kecil, query selesai dalam 2ms. Di production dengan 5 juta baris, query yang sama butuh 8 detik. Tim menyalahkan jaringan, lalu menyalahkan server, padahal sumber masalah ada di satu baris SQL.

Kedua, ORM menyembunyikan SQL aslinya. Kamu menulis kode yang terlihat bersih di level aplikasi, tapi di baliknya ORM menghasilkan query yang tidak efisien — dan kamu tidak pernah melihatnya.

Ketiga, penyebab umum query lambat sangat beragam:

Penyebab Umum Query Lambat
─────────────────────────────────────────────────────
  ✗ Full table scan — tidak ada index yang dipakai
  ✗ Fungsi di kolom WHERE — index ikut tidak terpakai
  ✗ SELECT * — kolom yang tidak perlu ikut ditarik
  ✗ JOIN tanpa index di kolom join — nested loop besar
  ✗ ORDER BY tanpa index — filesort di memory/disk
  ✗ Subquery yang tidak dioptimasi — dieksekusi berulang
  ✗ LIMIT tanpa index di kolom ORDER — scan semua dulu

Tanpa EXPLAIN, kamu hanya menebak mana yang jadi penyebab. Dengan EXPLAIN, jawabannya terlihat langsung.

Apa Itu EXPLAIN dan Cara Menggunakannya #

EXPLAIN adalah perintah yang meminta database menjelaskan rencana eksekusi sebuah query — bukan menjalankannya, tapi menjelaskan langkah apa yang akan dilakukan, index mana yang akan dipakai, berapa baris yang akan dibaca, dan dalam urutan apa operasi akan dijalankan.

Sintaks Dasar #

-- MySQL / MariaDB
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- PostgreSQL (format tabel)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- PostgreSQL (format lebih detail)
EXPLAIN (FORMAT JSON) SELECT * FROM orders WHERE user_id = 42;

EXPLAIN ANALYZE — Eksekusi Nyata #

-- MySQL 8.0+
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Perbedaan penting antara EXPLAIN dan EXPLAIN ANALYZE:

EXPLAIN
  │
  ├── Hanya estimasi planner
  ├── Tidak menjalankan query
  └── Aman dipakai di production

EXPLAIN ANALYZE
  │
  ├── Benar-benar menjalankan query
  ├── Menampilkan waktu aktual vs estimasi
  └── ⚠ Hati-hati di production (query tetap dieksekusi)

EXPLAIN ANALYZE dengan query UPDATE atau DELETE akan benar-benar mengubah data. Selalu bungkus dalam transaksi dan rollback jika tidak sengaja:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'expired';
ROLLBACK;

Format EXPLAIN di MySQL vs PostgreSQL #

MySQL dan PostgreSQL punya format output yang berbeda, tapi konsepnya sama:

-- MySQL: output tabular
EXPLAIN SELECT id, name FROM users WHERE email = '[email protected]';
-- +----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+
-- | id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra |

-- PostgreSQL: output tree
EXPLAIN SELECT id, name FROM users WHERE email = '[email protected]';
-- Index Scan using idx_users_email on users  (cost=0.43..8.45 rows=1 width=36)
--   Index Cond: ((email)::text = '[email protected]'::text)

Membaca Output EXPLAIN — Kolom Paling Penting #

Output EXPLAIN di MySQL punya beberapa kolom. Tidak semuanya perlu kamu hafal — tapi ada empat kolom yang wajib kamu baca setiap kali melihat output EXPLAIN.

Kolom type — Cara Database Mengakses Data #

Ini kolom paling kritis. type menunjukkan metode akses yang digunakan database untuk membaca data.

Urutan dari PALING BURUK ke PALING BAIK:
──────────────────────────────────────────────────────
  ALL      → Full table scan. Baca semua baris. Merah.
  index    → Scan seluruh index. Masih buruk di tabel besar.
  range    → Scan index dalam rentang. Cukup baik.
  ref      → Baca menggunakan index non-unique. Baik.
  eq_ref   → Baca satu baris via index unique di setiap JOIN. Baik.
  const    → Baca tepat satu baris. Sangat baik.
  system   → Tabel punya satu baris saja. Hanya untuk sistem.
──────────────────────────────────────────────────────
  Tujuan: selalu usahakan ref, eq_ref, atau const
  Waspada: ALL di tabel > 10.000 baris = masalah performa

Kolom key — Index yang Benar-Benar Dipakai #

Jika key bernilai NULL, artinya tidak ada index yang digunakan untuk query ini. Kolom possible_keys menunjukkan index yang bisa dipakai, tapi key menunjukkan yang benar-benar dipakai.

Jika possible_keys tidak kosong tapi key adalah NULL, database memutuskan bahwa full scan lebih efisien — ini terjadi ketika tabel kecil, atau selectivity index rendah (banyak nilai duplikat).

Kolom rows — Estimasi Baris yang Dibaca #

Bukan jumlah baris hasil query, tapi jumlah baris yang harus dibaca database untuk menghasilkan jawaban. Semakin kecil angka ini, semakin efisien query-nya.

Query yang mengembalikan 1 baris tapi harus membaca 500.000 baris untuk menemukannya adalah query yang sangat tidak efisien.

Kolom Extra — Informasi Tambahan yang Kritis #

Extra yang harus diwaspadai:
──────────────────────────────────────────────────────
  Using filesort    → Sort dilakukan di luar index.
                      Artinya ORDER BY tidak memanfaatkan index.
                      Mahal di data besar.

  Using temporary   → MySQL membuat tabel sementara.
                      Sering muncul di GROUP BY atau DISTINCT yang kompleks.
                      Sangat mahal.

  Using where       → Filter WHERE dilakukan setelah data diambil.
                      Tidak selalu buruk, tapi perlu diperhatikan.

Extra yang bagus:
──────────────────────────────────────────────────────
  Using index       → Query bisa dijawab dari index saja,
                      tidak perlu baca tabel utama (covering index).
                      Sangat efisien.

  Using index condition → Index condition pushdown aktif.
                          Filter dilakukan di level storage engine.

Kasus 1: Full Table Scan karena Tidak Ada Index #

Ini kasus paling klasik dan paling sering ditemukan. Query terlihat sederhana, tapi karena kolom yang di-filter tidak punya index, database harus membaca seluruh tabel.

Masalah #

-- Query: cari user berdasarkan email
SELECT id, name, email FROM users WHERE email = '[email protected]';

Output EXPLAIN:

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 482000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

Diagnosis: type = ALL, key = NULL, rows = 482000. Database membaca hampir setengah juta baris untuk menemukan satu email.

Solusi #

-- ANTI-PATTERN: tidak ada index di kolom yang difilter
SELECT id, name, email FROM users WHERE email = '[email protected]';
-- → type: ALL, rows: 482000, setiap request baca seluruh tabel

-- BENAR: tambahkan index di kolom email
CREATE INDEX idx_users_email ON users(email);

-- Sekarang jalankan query yang sama
SELECT id, name, email FROM users WHERE email = '[email protected]';
-- → type: ref, key: idx_users_email, rows: 1

Output EXPLAIN setelah optimasi:

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | users | ref  | idx_users_email | idx_users_email | 767     | const |    1 | NULL  |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+

Dari 482.000 baris menjadi 1 baris. Kompleksitas berubah dari O(n) menjadi O(log n).

Kasus 2: Index Ada Tapi Tidak Dipakai karena Fungsi di WHERE #

Ini adalah jebakan yang lebih halus dan lebih sering tidak disadari. Kamu sudah punya index, sudah merasa aman — tapi query tetap lambat karena cara penulisan kondisi WHERE-nya menghalangi index untuk dipakai.

Masalah #

-- Query: ambil semua order di tanggal tertentu
SELECT id, user_id, total FROM orders WHERE DATE(created_at) = '2026-01-15';

Output EXPLAIN:

+------+------+---------+------+-----------+-------------+
| type | key  | key_len | ref  | rows      | Extra       |
+------+------+---------+------+-----------+-------------+
| ALL  | NULL | NULL    | NULL | 1,280,000 | Using where |
+------+------+---------+------+-----------+-------------+

Padahal created_at punya index! Kenapa key = NULL?

Mengapa Ini Terjadi #

Kolom created_at punya index → ✓
Tapi query menggunakan DATE(created_at)

Yang terjadi di database:
  ┌──────────────────────────────────────────────┐
  │ Untuk setiap baris:                          │
  │   1. Ambil nilai created_at                  │
  │   2. Terapkan fungsi DATE() ke nilai itu     │
  │   3. Bandingkan hasilnya dengan '2026-01-15' │
  └──────────────────────────────────────────────┘

Index menyimpan nilai created_at yang asli, bukan hasil DATE(created_at).
Database tidak bisa pakai index untuk nilai yang sudah ditransformasi.
Fungsi di kolom = index tidak bisa dipakai.

Solusi #

-- ANTI-PATTERN: fungsi di kolom mematikan index
SELECT id, user_id, total
FROM orders
WHERE DATE(created_at) = '2026-01-15';
-- → type: ALL, rows: 1,280,000

-- BENAR: ubah kondisi agar kolom tidak dibungkus fungsi
SELECT id, user_id, total
FROM orders
WHERE created_at >= '2026-01-15 00:00:00'
  AND created_at <  '2026-01-16 00:00:00';
-- → type: range, key: idx_orders_created_at, rows: 14200

Aturan yang sama berlaku untuk semua fungsi lain:

-- ANTI-PATTERN: fungsi lain yang mematikan index
WHERE YEAR(created_at) = 2026              -- ✗
WHERE UPPER(name) = 'BUDI'                -- ✗
WHERE LENGTH(description) > 100           -- ✗
WHERE SUBSTRING(code, 1, 3) = 'INV'      -- ✗

-- BENAR: tulis ulang tanpa fungsi di kolom
WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31'  -- ✓
WHERE name = 'Budi'  -- (pakai collation case-insensitive)  -- ✓
WHERE code LIKE 'INV%'                                      -- ✓

PostgreSQL mendukung functional index yang memungkinkan kamu mengindex hasil fungsi:

CREATE INDEX idx_orders_date ON orders (DATE(created_at));

Dengan ini, WHERE DATE(created_at) = '2026-01-15' bisa memanfaatkan index. Tapi pendekatan yang lebih universal tetap menghindari fungsi di kolom WHERE.

Kasus 3: JOIN Lambat karena Kolom Join Tidak Ter-index #

Query dengan JOIN adalah tempat masalah performa paling sering bersembunyi — dan juga yang paling sulit dilihat tanpa EXPLAIN. Karena JOIN melibatkan beberapa tabel, satu index yang hilang bisa mengakibatkan nested loop yang besar.

Masalah #

-- Query: ambil order beserta nama user, filter berdasarkan negara
SELECT o.id, o.total, u.name, u.country
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'ID'
ORDER BY o.created_at DESC
LIMIT 20;

Output EXPLAIN (disederhanakan):

+----+-------+-------+------+---------------+------+------+------------------+
| id | table | type  | key  | possible_keys | rows | ref  | Extra            |
+----+-------+-------+------+---------------+------+------+------------------+
|  1 | u     | ALL   | NULL | PRIMARY       | 85000| NULL | Using where;     |
|    |       |       |      |               |      |      | Using temporary; |
|    |       |       |      |               |      |      | Using filesort   |
+----+-------+-------+------+---------------+------+------+------------------+
|  1 | o     | ALL   | NULL | NULL          | 1.2M | NULL | Using where      |
+----+-------+-------+------+---------------+------+------+------------------+

Tiga lampu merah sekaligus: type: ALL di kedua tabel, Using temporary, dan Using filesort.

Mengapa Ini Terjadi #

Eksekusi yang terjadi tanpa index:
─────────────────────────────────────────────────────────
  1. Scan seluruh tabel users (85.000 baris)
     └── Filter WHERE country = 'ID'
         └── Misalkan 12.000 user di Indonesia

  2. Untuk setiap user Indonesia, scan seluruh tabel orders (1.2 juta baris)
     └── Filter ON o.user_id = u.id
         = 12.000 × 1.200.000 = 14.4 MILIAR operasi perbandingan

  3. Sort hasil di tabel temporary (Using filesort)

  4. Ambil 20 baris teratas
─────────────────────────────────────────────────────────
  Total: sangat tidak efisien

Solusi #

-- ANTI-PATTERN: join tanpa index di kolom filter dan kolom join
SELECT o.id, o.total, u.name, u.country
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'ID'
ORDER BY o.created_at DESC;
-- → type: ALL di kedua tabel, nested loop besar

-- LANGKAH 1: index di kolom yang difilter (WHERE)
CREATE INDEX idx_users_country ON users(country);

-- LANGKAH 2: index di kolom join di tabel orders
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- LANGKAH 3: index di kolom ORDER BY
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- BENAR: query yang sama, sekarang memanfaatkan semua index
SELECT o.id, o.total, u.name, u.country
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'ID'
ORDER BY o.created_at DESC
LIMIT 20;
-- → type: ref di kedua tabel, rows berkurang drastis

Output EXPLAIN setelah optimasi:

+----+-------+--------+------------------+------+------+----------------+
| id | table | type   | key              | rows | ref  | Extra          |
+----+-------+--------+------------------+------+------+----------------+
|  1 | u     | ref    | idx_users_country|12000 | const| Using index    |
|  1 | o     | ref    | idx_orders_user_id|  8  | u.id | Using filesort |
+----+-------+--------+------------------+------+------+----------------+

Using filesort masih muncul, tapi sekarang dioperasikan pada dataset yang jauh lebih kecil — bukan pada 1.2 juta baris mentah.

Kasus 4: SELECT * yang Tidak Perlu #

SELECT * terasa nyaman saat development, tapi di production bisa menjadi beban yang tidak perlu — terutama jika tabel punya kolom dengan tipe data besar seperti TEXT, BLOB, atau JSON.

Masalah dan Solusi #

-- ANTI-PATTERN: SELECT * menarik semua kolom termasuk yang tidak dipakai
SELECT * FROM products WHERE category_id = 5;
-- Menarik: id, name, description (TEXT 10KB), image_url, price, stock,
--          metadata (JSON), created_at, updated_at, deleted_at, ...
-- Padahal kita hanya butuh id, name, price

-- BENAR: pilih hanya kolom yang benar-benar dibutuhkan
SELECT id, name, price FROM products WHERE category_id = 5;

-- Manfaat tambahan: covering index jadi memungkinkan
-- Jika ada index (category_id, id, name, price), query bisa dijawab
-- dari index saja tanpa membaca tabel utama → Extra: Using index

Selain performa, SELECT * juga berbahaya untuk maintainability: query akan otomatis mengambil kolom baru yang ditambahkan ke tabel di masa depan, yang bisa mengubah perilaku aplikasi secara tidak terduga.

Kasus 5: ORDER BY Tanpa Index yang Tepat #

Sorting adalah operasi yang mahal ketika tidak bisa memanfaatkan index. EXPLAIN akan menampilkan Using filesort — bukan berarti sort dilakukan di disk, tapi sort dilakukan di luar index.

Masalah dan Solusi #

-- ANTI-PATTERN: ORDER BY di kolom tanpa index
SELECT id, name, created_at FROM articles
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 10;
-- Jika tidak ada index yang mencakup (status, created_at):
-- → Extra: Using where; Using filesort

-- BENAR: buat composite index yang mencakup WHERE dan ORDER BY
CREATE INDEX idx_articles_status_created ON articles(status, created_at DESC);

-- Sekarang database bisa scan index secara langsung, sudah dalam urutan yang benar
-- → Extra: Using index condition (tidak ada lagi filesort)

Urutan kolom di composite index penting: kolom yang dipakai di WHERE (equality) harus di depan, diikuti kolom yang dipakai di ORDER BY.

Aturan composite index untuk WHERE + ORDER BY:
──────────────────────────────────────────────────────
  Kolom WHERE (equality)   → posisi pertama
  Kolom WHERE (range)      → posisi sebelum ORDER BY
  Kolom ORDER BY           → posisi terakhir

Contoh:
  WHERE status = 'published' AND created_at > '2026-01-01'
  ORDER BY created_at DESC

  Index yang tepat: (status, created_at)
  Bukan: (created_at, status)

Anti-Pattern yang Harus Dihindari #

Setelah memahami cara EXPLAIN bekerja, berikut anti-pattern paling umum yang harus selalu kamu periksa saat review query:

-- ✗ Anti-pattern 1: SELECT * di tabel dengan kolom besar
SELECT * FROM users WHERE id = 42;
-- ✓ Solusi: pilih kolom yang dibutuhkan
SELECT id, name, email FROM users WHERE id = 42;

-- ✗ Anti-pattern 2: fungsi di kolom WHERE
SELECT * FROM logs WHERE DATE(created_at) = CURDATE();
-- ✓ Solusi: gunakan range tanpa fungsi di kolom
SELECT * FROM logs WHERE created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY;

-- ✗ Anti-pattern 3: LIKE dengan wildcard di awal
SELECT * FROM products WHERE name LIKE '%keyboard%';
-- ✓ Solusi: gunakan Full-Text Search untuk pencarian kata kunci
SELECT * FROM products WHERE MATCH(name) AGAINST('keyboard' IN BOOLEAN MODE);

-- ✗ Anti-pattern 4: NOT IN dengan subquery besar
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);
-- ✓ Solusi: gunakan LEFT JOIN ... IS NULL
SELECT u.* FROM users u
LEFT JOIN banned_users b ON u.id = b.user_id
WHERE b.user_id IS NULL;

-- ✗ Anti-pattern 5: OR di kolom yang berbeda tanpa index masing-masing
SELECT * FROM orders WHERE status = 'pending' OR user_id = 42;
-- ✓ Solusi: gunakan UNION jika kedua kolom perlu index berbeda
SELECT * FROM orders WHERE status = 'pending'
UNION
SELECT * FROM orders WHERE user_id = 42;

Workflow EXPLAIN yang Efektif #

Membaca EXPLAIN adalah skill — makin sering dilakukan, makin cepat kamu mengidentifikasi masalahnya. Berikut workflow yang bisa kamu terapkan secara rutin:

Workflow Optimasi Query dengan EXPLAIN
──────────────────────────────────────────────────────────────────
  1. Identifikasi query lambat
     │  Gunakan slow query log (MySQL) atau pg_stat_statements (PG)
     │  Threshold: query > 100ms perlu diperiksa
     │
     ▼
  2. Jalankan EXPLAIN
     │  Lihat type, key, rows, Extra
     │  Cari: ALL, NULL key, tingginya rows, filesort, temporary
     │
     ▼
  3. Hipotesis
     │  type = ALL → kemungkinan tidak ada index / index tidak terpakai
     │  fungsi di WHERE → index tidak bisa dipakai
     │  filesort → ORDER BY tidak memanfaatkan index
     │
     ▼
  4. Terapkan perbaikan (index / rewrite query)
     │
     ▼
  5. Jalankan EXPLAIN lagi
     │  Verifikasi type berubah menjadi ref/range/const
     │  Verifikasi rows berkurang signifikan
     │
     ▼
  6. Uji di staging dengan data realistis
     │  Data staging harus sebanding volumenya dengan production
     │
     ▼
  7. Deploy dan monitor
     └── Pantau slow query log untuk konfirmasi perbaikan

Checklist Review Query #

Gunakan checklist ini setiap kali kamu menulis atau mereview query yang melibatkan tabel besar:

SEBELUM EKSEKUSI:
  □ Sudah jalankan EXPLAIN?
  □ Tidak ada type = ALL di tabel > 10.000 baris?
  □ Kolom di WHERE punya index?
  □ Tidak ada fungsi yang membungkus kolom di WHERE?
  □ Kolom di JOIN ter-index di kedua sisi?
  □ SELECT hanya mengambil kolom yang dibutuhkan?

ORDER BY DAN PAGINATION:
  □ Kolom di ORDER BY ter-index?
  □ Composite index sudah dalam urutan yang tepat?
  □ Untuk cursor-based pagination: kolom cursor ter-index?

OUTPUT EXPLAIN:
  □ Extra tidak mengandung Using temporary?
  □ Extra tidak mengandung Using filesort di dataset besar?
  □ Rows estimasi masuk akal (tidak jutaan untuk query sederhana)?
  □ Key menunjukkan index yang tepat, bukan NULL?

Ringkasan #

  • EXPLAIN bukan optional — setiap query yang menyentuh tabel besar di production harus pernah diperiksa dengan EXPLAIN minimal sekali.
  • type = ALL di tabel besar adalah lampu merah — selalu cari cara untuk mengubahnya menjadi ref, range, atau const dengan menambahkan index yang tepat.
  • Fungsi di kolom WHERE mematikan index — tulis ulang kondisi agar kolom tidak dibungkus fungsi; gunakan range atau kondisi langsung sebagai gantinya.
  • Kolom JOIN harus ter-index di kedua sisi — index di kolom join di tabel “many” sering terlupakan dan menyebabkan nested loop yang sangat mahal.
  • Using filesort dan Using temporary adalah sinyal biaya tinggi — perbaiki dengan composite index yang mencakup kolom WHERE dan ORDER BY dalam urutan yang benar.
  • SELECT * adalah kebiasaan buruk — pilih hanya kolom yang dibutuhkan; ini membuka peluang covering index dan mengurangi I/O secara signifikan.
  • EXPLAIN ANALYZE menjalankan query secara nyata — gunakan dengan hati-hati di production, selalu bungkus dalam transaksi untuk query yang mengubah data.
  • Optimasi berbasis data, bukan asumsi — performa yang diukur dengan EXPLAIN jauh lebih dapat diandalkan daripada intuisi atau pengalaman dari database lain.

← Sebelumnya: Sharding   Berikutnya: Type on Join →

About | Author | Content Scope | Editorial Policy | Privacy Policy | Disclaimer | Contact