EXPLAIN

Di chapter sebelumnya kita membaca execution plan dengan membaca empat kolom andalan: type, key, rows, Extra. Cara cepat itu cukup untuk triase, tetapi ada saatnya kita perlu membaca plan seperti membaca hasil laboratorium lengkap — misalnya saat optimizer memilih index yang aneh, saat subquery menambah baris plan yang tidak kita duga, atau saat kita ingin tahu berapa persisnya biaya yang optimizer hitung. EXPLAIN adalah alat baca lengkapnya: satu perintah, dua belas kolom, plus dua pendamping (SHOW WARNINGS dan EXPLAIN FORMAT=JSON) yang membuka informasi lebih dalam. Chapter ini membedah semuanya sampai sel terakhir.

Menyiapkan Data Latihan #

Kita naikkan kompleksitas sedikit: dua tabel yang berelasi supaya kolom ref, id, dan filtered punya cerita nyata untuk diceritakan.

sql
DROP DATABASE IF EXISTS belajar_opt;
CREATE DATABASE belajar_opt;
USE belajar_opt;

Output yang Dihasilkan:

text
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

sql
CREATE TABLE pelanggan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kota VARCHAR(40) NOT NULL,
  email VARCHAR(100) NOT NULL,
  tanggal_daftar DATE NOT NULL
);

INSERT INTO pelanggan (nama, kota, email, tanggal_daftar) VALUES
  ('Budi Santoso',   'Jakarta',    '[email protected]',  '2026-01-05'),
  ('Siti Rahayu',    'Bandung',    '[email protected]',  '2026-01-12'),
  ('Andi Wijaya',    'Surabaya',   '[email protected]',  '2026-02-01'),
  ('Rina Kartika',   'Jakarta',    '[email protected]',  '2026-02-10'),
  ('Dewi Lestari',   'Yogyakarta', '[email protected]',  '2026-03-03'),
  ('Tono Sutrisno',  'Bandung',    '[email protected]',  '2026-03-15'),
  ('Sari Wulandari', 'Medan',      '[email protected]',  '2026-04-01'),
  ('Joko Pratama',   'Jakarta',    '[email protected]',  '2026-04-20'),
  ('Nurul Hidayah',  'Surabaya',   '[email protected]', '2026-05-05'),
  ('Rizky Maulana',  'Medan',      '[email protected]', '2026-05-18');

CREATE INDEX idx_kota ON pelanggan(kota);
CREATE INDEX idx_daftar ON pelanggan(tanggal_daftar);

CREATE TABLE pesanan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pelanggan_id INT NOT NULL,
  tanggal DATE NOT NULL,
  total DECIMAL(12,2) NOT NULL,
  status VARCHAR(20) NOT NULL
);

INSERT INTO pesanan (pelanggan_id, tanggal, total, status) VALUES
  (1,  '2026-01-10', 150000.00, 'selesai'),
  (2,  '2026-01-15', 250000.00, 'selesai'),
  (3,  '2026-01-20',  75000.00, 'batal'),
  (1,  '2026-02-05', 320000.00, 'selesai'),
  (4,  '2026-02-12', 125000.00, 'diproses'),
  (5,  '2026-02-18', 480000.00, 'selesai'),
  (2,  '2026-03-01', 210000.00, 'selesai'),
  (6,  '2026-03-08',  95000.00, 'batal'),
  (1,  '2026-03-15', 540000.00, 'diproses'),
  (7,  '2026-03-22', 175000.00, 'selesai'),
  (8,  '2026-04-02', 260000.00, 'selesai'),
  (3,  '2026-04-09', 310000.00, 'selesai'),
  (9,  '2026-04-16',  88000.00, 'batal'),
  (4,  '2026-04-25', 430000.00, 'selesai'),
  (10, '2026-05-01', 365000.00, 'selesai');

CREATE INDEX idx_tanggal ON pesanan(tanggal);
CREATE INDEX idx_pelanggan ON pesanan(pelanggan_id);

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 15 rows affected (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query Contoh dan Output EXPLAIN #

Sasaran bedah kita adalah join dengan filter tanggal — cukup kompleks untuk mengisi hampir semua kolom dengan nilai yang bermakna:

sql
EXPLAIN SELECT o.id, p.nama, o.total
FROM pesanan o
JOIN pelanggan p ON p.id = o.pelanggan_id
WHERE o.tanggal >= '2026-04-01';

Output yang Dihasilkan:

text
+----+-------------+-------+------------+--------+---------------------------+-------------+---------+----------------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys             | key         | key_len | ref                        | rows | filtered | Extra                 |
+----+-------------+-------+------------+--------+---------------------------+-------------+---------+----------------------------+------+----------+-----------------------+
|  1 | SIMPLE      | o     | NULL       | range  | idx_tanggal,idx_pelanggan | idx_tanggal | 3       | NULL                       |    5 |   100.00 | Using index condition |
|  1 | SIMPLE      | p     | NULL       | eq_ref | PRIMARY                   | PRIMARY     | 4       | belajar_opt.o.pelanggan_id |    1 |   100.00 | NULL                  |
+----+-------------+-------+------------+--------+---------------------------+-------------+---------+----------------------------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)

Dua baris plan muncul: satu untuk tabel o (alias pesanan), satu untuk tabel p (alias pelanggan). Urutan baris adalah urutan pembacaan — optimizer memulai dari o, lalu untuk setiap pesanan melompat ke p lewat PRIMARY. Sekarang kita bedah dua belas kolomnya satu per satu.

Bedah Dua Belas Kolom #

Kolom	Arti singkat
`id`	Nomor statement SELECT dalam query (semua baris join satu SELECT berbagi `id` sama)
`select_type`	Jenis blok SELECT: `SIMPLE`, `PRIMARY`, `SUBQUERY`, `UNION`, `DERIVED`, dan kawan-kawan
`table`	Tabel (atau alias, atau `<derivedN>`/`<unionN>`) yang dibaca baris tersebut
`partitions`	Partisi tabel yang disentuh — `NULL` bila tabel tidak berpartisi
`type`	Access type: cara tabel diakses, dari `const` (terbaik) sampai `ALL` (full scan)
`possible_keys`	Index yang layak dipakai menurut optimizer — kandidat, bukan keputusan
`key`	Index yang benar-benar dipilih
`key_len`	Panjang bagian index (dalam byte) yang dipakai — bukti berapa kolom composite yang terpakai
`ref`	Nilai pembanding lookup: `const` untuk literal, atau kolom tabel lain pada join
`rows`	Estimasi jumlah baris yang diperiksa pada tabel ini
`filtered`	Estimasi persentase baris yang lolos kondisi tambahan setelah diakses
`Extra`	Informasi langkah tambahan: `Using where`, `Using index`, `Using filesort`, dan lain-lain

Kita jalani satu per satu dengan nilai nyata dari plan di atas.

id dan select_type — keduanya menjawab “blok SELECT yang mana?”. Query kita satu blok (id = 1, select_type = SIMPLE) yang membaca dua tabel lewat join, sehingga kedua baris berbagi id yang sama. Kalau query memuat subquery atau UNION, angka id naik dan select_type berganti — kita lihat contoh nyatanya di bagian berikutnya.

table — tabel yang diwakili baris plan, memakai alias bila query memberi alias (o, p). Blok hasil turunan seperti subquery di FROM muncul sebagai <derived2> dan hasil UNION sebagai <union1,2>. Urutan baris dengan id sama adalah urutan join; baris pertama adalah driving table.

partitions — hanya relevan untuk tabel berpartisi (partitioning), yang menampilkan partisi mana yang perlu dibaca. Tabel kita tidak berpartisi sehingga nilainya NULL — kolom ini bisa kita abaikan di kursus ini.

type — kolom kesehatan utama. Nilai range pada o berarti index dipakai untuk membaca rentang (semua pesanan sejak awal April), dan eq_ref pada p berarti lookup satu baris lewat PRIMARY — jenis akses terbaik untuk join. Daftar lengkap dari system sampai ALL dibedah khusus di chapter Access Types.

possible_keys vs key — kandidat versus pilihan. Pada baris o, optimizer mencatat idx_tanggal dan idx_pelanggan sebagai kandidat, lalu memilih idx_tanggal di kolom key. Selisih keduanya adalah informasi diagnostik yang penting: possible_keys berisi banyak index tapi key = NULL berarti index ada namun optimizer menolaknya (sering karena estimasi biaya atau kondisi yang mematikan index).

key_len — panjang bagian index yang benar-benar dipakai, dalam byte. Nilai 3 pada idx_tanggal cocok dengan satu kolom DATE (3 byte). Pada index composite, key_len yang lebih pendek dari total panjang index menandakan hanya kolom-kolom awal yang terpakai — teknik membaca ini kita pakai berat di chapter Index Optimization.

ref — apa yang dibandingkan saat lookup lewat index. Nilai const berarti literal dari query; nilai berformat belajar_opt.o.pelanggan_id seperti pada baris p berarti kolom milik tabel lain — inilah tanda lookup join: setiap nilai o.pelanggan_id menjadi kunci pencarian PRIMARY di p.

rows — estimasi baris yang diperiksa. Angka ini estimasi, dihitung dari statistik tabel; bisa meleset bila statistik basi. rows pada baris p bernilai 1 karena setiap lookup PRIMARY paling-paling menjumpai satu baris.

filtered — persentase baris (dari rows) yang diperkirakan lolos kondisi yang tidak bisa dilayani index. Baris o kita bernilai 100.00 karena seluruh rentang tanggal sudah mewakili hasilnya; nilai kecil seperti 10.00 berarti optimizer memperkirakan sembilan dari sepuluh baris akan dibuang setelah kondisi tambahan diterapkan.

Extra — kumpulan catatan langkah tambahan. Using index condition pada baris o berarti filter tanggal sebagian dievaluasi langsung di index (index condition pushdown), sebelum baris lengkap dibaca. Nilai lain yang sering muncul: Using where (filter setelah baris dibaca), Using index (semua kolom yang dibutuhkan tersedia di index — covering), Using filesort (pengurutan butuh kerja ekstra), Using temporary (tabel sementara dibuat).

id Lebih dari 1: Subquery dan UNION #

Saat query memuat lebih dari satu blok SELECT, tabel plan bertambah baris dan id naik. Subquery skalar memberi dua baris dengan select_type berbeda:

sql
EXPLAIN SELECT id, total FROM pesanan
WHERE total > (SELECT AVG(total) FROM pesanan);

Output yang Dihasilkan:

text
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | pesanan | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |    33.33 | Using where |
|  2 | SUBQUERY    | pesanan | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.00 | NULL        |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

Baris id = 1 bertanda PRIMARY — blok SELECT utama; baris id = 2 bertanda SUBQUERY — subquery yang dievaluasi terpisah. UNION menghasilkan pola tiga baris yang khas:

sql
EXPLAIN SELECT id, nama FROM pelanggan WHERE kota = 'Jakarta'
UNION
SELECT id, nama FROM pelanggan WHERE tanggal_daftar = '2026-01-05';

Output yang Dihasilkan:

text
+----+--------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | pelanggan  | NULL       | ref  | idx_kota      | idx_kota   | 162     | const |    3 |   100.00 | NULL            |
|  2 | UNION        | pelanggan  | NULL       | ref  | idx_daftar    | idx_daftar | 3       | const |    1 |   100.00 | NULL            |
|  3 | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL       | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

Kita jumpai PRIMARY (blok pertama), UNION (blok kedua yang digabungkan), lalu UNION RESULT pada tabel <union1,2> — langkah penggabungan hasil kedua blok. Angka pada <union1,2> merujuk id blok-blok yang digabung. Dengan peta ini, plan query kompleks berhenti terlihat seperti kekacauan baris — setiap baris punya asal-usul yang jelas.

SHOW WARNINGS: Melihat Query yang Ditulis Ulang #

Optimizer tidak menjalankan query persis seperti yang kita tulis — ia menulis ulang, menyederhanakan, dan mengganti beberapa bentuk dengan bentuk setara. Bentuk akhir yang benar-benar diproses bisa dilihat dengan SHOW WARNINGS tepat setelah EXPLAIN:

sql
EXPLAIN SELECT id, nama FROM pelanggan
WHERE kota = 'Jakarta' AND tanggal_daftar >= '2026-02-01';
SHOW WARNINGS;

Output yang Dihasilkan:

text
+----+-------------+-----------+------------+------+---------------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys       | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | pelanggan | NULL       | ref  | idx_kota,idx_daftar | idx_kota | 162     | const |    3 |    80.00 | Using where |
+----+-------------+-----------+------------+------+---------------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                            |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `belajar_opt`.`pelanggan`.`id` AS `id`,`belajar_opt`.`pelanggan`.`nama` AS `nama` from `belajar_opt`.`pelanggan` where ((`belajar_opt`.`pelanggan`.`kota` = 'Jakarta') and (`belajar_opt`.`pelanggan`.`tanggal_daftar` >= DATE'2026-02-01')) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Baris Note 1003 memuat query hasil penulisan ulang: nama kolom menjadi nama lengkap dengan database dan tabel (belajar_opt.pelanggan.kota), literal tanggal ditulis ulang sebagai DATE'2026-02-01', dan kondisi-kondisi dirapikan. Versi ini adalah kenyataan internal yang dilihat optimizer — kadang memperlihatkan transformasi yang mengejutkan, misalnya IN yang diubah, atau kondisi yang digabungkan dari tempat berbeda.

EXPLAIN FORMAT=JSON: Biaya yang Teliti #

Format tabel itu ringkas, tetapi optimizer sebenarnya menghitung biaya lebih rinci daripada yang ditampilkan. EXPLAIN FORMAT=JSON membuka laporan lengkapnya:

sql
EXPLAIN FORMAT=JSON SELECT * FROM pelanggan WHERE kota = 'Jakarta';

Output yang Dihasilkan:

text
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.80"
    },
    "table": {
      "table_name": "pelanggan",
      "access_type": "ref",
      "possible_keys": [
        "idx_kota"
      ],
      "key": "idx_kota",
      "used_key_parts": [
        "kota"
      ],
      "key_length": "162",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 3,
      "rows_produced_per_join": 3,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.50",
        "eval_cost": "0.30",
        "prefix_cost": "0.80",
        "data_read_per_join": "2K"
      },
      "used_columns": [
        "id",
        "nama",
        "kota",
        "email",
        "tanggal_daftar"
      ]
    }
  }
} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Struktur JSON memuat hal-hal yang diringkas format tabel: query_cost (total biaya dalam satuan cost model), read_cost dan eval_cost per tabel, data_read_per_join (estimasi byte yang dibaca), used_key_parts (kolom index mana yang terpakai — versi rinci dari key_len), hingga used_columns (daftar kolom yang dibutuhkan query). Outputnya memang besar dan kurang enak dibaca langsung — tooling eksternal biasalah yang memformatnya — tetapi ketika kita perlu angka biaya untuk membandingkan dua rencana secara kuantitatif, inilah sumbernya.

EXPLAIN untuk INSERT, UPDATE, DELETE #

Sejak MySQL 8.0.18, EXPLAIN juga menerima statement DML — plan yang ditampilkan adalah rencana pembacaan baris yang akan terkena efek statement. Perintah ini tetap tidak menjalankan statement (data tidak berubah), sehingga aman dipakai untuk memeriksa apakah DELETE massal akan full scan:

sql
EXPLAIN DELETE FROM pesanan WHERE tanggal < '2026-02-01';
EXPLAIN UPDATE pesanan SET status = 'batal' WHERE id = 99;
EXPLAIN INSERT INTO pesanan (pelanggan_id, tanggal, total, status)
VALUES (1, '2026-06-01', 100000.00, 'baru');

Output yang Dihasilkan:

text
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | DELETE      | pesanan | NULL       | range | idx_tanggal   | idx_tanggal | 3       | const |    3 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | pesanan | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | pesanan | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Perhatikan select_type berganti menjadi DELETE, UPDATE, dan INSERT. Plan DELETE memperlihatkan akses ref/range lewat idx_tanggal — artinya penghapusan lama bisa memanfaatkan index. Plan UPDATE menunjukkan lookup PRIMARY — hanya satu baris disentuh. Untuk INSERT nilai langsung (VALUES), tidak ada rencana pembacaan yang menarik — kolom-kolom rencana kosong semua; EXPLAIN baru informatif untuk INSERT ... SELECT, yang plannya adalah plan SELECT sumbernya.

Membersihkan Database Latihan #

sql
DROP DATABASE belajar_opt;

Output yang Dihasilkan:

text

Query OK, 2 rows affected (0.00 sec)

Referensi #

Kita kini bisa membaca dua belas kolom EXPLAIN, membedah id dan select_type untuk subquery serta UNION, mengintip query hasil tulis-ulang optimizer lewat SHOW WARNINGS, dan membuka rincian biaya lewat FORMAT=JSON. Semua itu tetap berdiri di atas satu keterbatasan: seluruh angkanya estimasi. Chapter berikutnya memperkenalkan EXPLAIN ANALYZE — perintah yang benar-benar menjalankan query dan mengganti perkiraan dengan pengukuran aktual.