Join adalah tempat query paling sering kehilangan performanya — bukan karena join itu mahal, melainkan karena setiap keputusan kecil dijoin dikalikan berulang kali. Salah urutan tabel membuat lookup yang semestinya lima kali menjadi sepuluh ribu kali; satu kolom join yang tidak ber-index mengubah pola “lompat ke baris” menjadi “bandingkan semua dengan semua”. Kabar baiknya, seluruh keputusan itu terlihat di EXPLAIN: urutan tabel di tabel plan, access type tiap baris, dan Extra yang mengumumkan strategi darurat optimizer. Chapter ini membedah empat tuas utama join optimization: join order, pemaksaan urutan, index pada kolom join, dan pemilihan kolom yang di-SELECT.
Menyiapkan Data Latihan #
Papan uji kita: sepuluh pelanggan dan lima belas pesanan, dengan index pada tanggal pesanan dan pada kolom relasi pelanggan_id — index terakhir inilah yang nanti kita copot untuk melihat efeknya:
DROP DATABASE IF EXISTS belajar_opt;
CREATE DATABASE belajar_opt;
USE belajar_opt;Output yang Dihasilkan:
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changedCREATE 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 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:
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)
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: 0Join Order: Siapa Jadi Driving Table #
MySQL menjalankan join sebagai nested loop: tabel pertama (driving table) dibaca sekali, lalu untuk setiap barisnya tabel kedua dicari lewat kondisi join. Urutan tabel di output EXPLAIN adalah urutan pembacaan — baris atas driving, baris bawah dicari berulang. Optimizer memilih driving table yang menghasilkan baris paling sedikit setelah filter, supaya pengulangan lookup sesedikit mungkin:
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:
+----+-------------+-------+------------+--------+---------------------------+-------------+---------+----------------------------+------+----------+-----------------------+
| 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)Plan memulai dari o: filter tanggal memangkas pesanan menjadi 5 baris lewat idx_tanggal, lalu p di-eq_ref lima kali lewat PRIMARY. Bila urutan dibalik — sepuluh pelanggan dibaca dulu — lookup pesanan menjadi sepuluh kali. Selisih lima lawan sepuluh terlihat lucu di data latihan; pada tabel sejuta baris versus sepuluh ribu, urutan yang salah berarti sepuluh ribu lookup lawan satu juta. Kebanyakan kasus terburuk lahir dari sini.
Perhatikan pula kolom filtered pada baris o. Angka itu memperkirakan persentase baris yang lolos kondisi setelah access type diterapkan — sisa kondisi yang tidak bisa dilayani index. Optimizer mengalikan rows × filtered dengan rows tabel berikutnya untuk memperkirakan jumlah lookup: perkalian kecil di kiri membuat seluruh pohon join murah. Inilah mekanisme di balik aturan praktis “letakkan tabel paling terseleksi di posisi driving”.
STRAIGHT_JOIN: Memaksa Urutan #
Optimizer kadang salah hitung — statistik basi atau estimasi filtered yang meleset bisa membuat urutan terpilih justru yang buruk. MySQL menyediakan tombol paksa: keyword STRAIGHT_JOIN di depan SELECT memaksa tabel dibaca persis dalam urutan penulisan:
EXPLAIN SELECT STRAIGHT_JOIN p.nama, o.total
FROM pelanggan p
JOIN pesanan o ON p.id = o.pelanggan_id
WHERE o.tanggal >= '2026-04-01';Output yang Dihasilkan:
+----+-------------+-------+------------+------+---------------------------+---------------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------------+---------------+---------+------------------+------+----------+-------------+
| 1 | SIMPLE | p | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 100.00 | NULL |
| 1 | SIMPLE | o | NULL | ref | idx_tanggal,idx_pelanggan | idx_pelanggan | 4 | belajar_opt.p.id | 1 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------------------+---------------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)Urutan kini terbalik: p dibaca penuh (type = ALL, sepuluh baris) sebagai driving table, lalu o dicari lewat idx_pelanggan (type = ref) sepuluh kali. Baris o sekaligus memperlihatkan cara membaca filtered: nilai 33.33 berarti dari 15 baris pesanan yang dijangkau lookup, sepertiga diperkirakan lolos filter tanggal >= '2026-04-01' — sisa kondisi yang tidak tercakup access type. STRAIGHT_JOIN adalah alat diagnosa yang jujur — ia menunjukkan harga urutan alternatif — tetapi bukan solusi permanen: urutan yang dipaksa tetap dipertahankan bahkan setelah data berubah dan urutan itu berhenti masuk akal. Gunakan untuk eksperimen, lalu perbaiki akar masalahnya (statistik, index, bentuk query).
Index pada Kolom Join: Lookup Lawan Join Buffer #
Nested loop hanya cepat bila tabel kedua bisa dicari lewat index di kolom join. Kita buktikan dengan mencopot idx_pelanggan:
ALTER TABLE pesanan DROP INDEX idx_pelanggan;
EXPLAIN SELECT STRAIGHT_JOIN p.nama, o.total
FROM pelanggan p
JOIN pesanan o ON p.id = o.pelanggan_id;Output yang Dihasilkan:
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | p | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 100.00 | NULL |
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)Kolom join o.pelanggan_id kini telanjang, sehingga tidak ada cara mencari pesanan milik satu pelanggan secara langsung. Extra mengumumkan strategi darurat: Using join buffer (hash join) — MySQL memuat baris satu sisi ke buffer, membangun tabel hash dari kunci join, lalu menyapu sisi lain dan mencocokkan lewat hash. Ini jauh lebih baik daripada block nested loop generasi lama yang membandingkan tiap pasangan baris satu per satu (nama itu masih melekat di banyak literatur), tetapi biayanya tetap sebanding jumlah baris kedua sisi — bukan sekadar jumlah baris hasil.
CREATE INDEX idx_pelanggan ON pesanan(pelanggan_id);
EXPLAIN SELECT STRAIGHT_JOIN p.nama, o.total
FROM pelanggan p
JOIN pesanan o ON p.id = o.pelanggan_id;Output yang Dihasilkan:
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
+----+-------------+-------+------------+------+---------------+---------------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+------------------+------+----------+-------+
| 1 | SIMPLE | p | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 100.00 | NULL |
| 1 | SIMPLE | o | NULL | ref | idx_pelanggan | idx_pelanggan | 4 | belajar_opt.p.id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------------+---------+------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)Index dikembalikan, dan join kembali menjadi nested loop murni: p tetap dibaca penuh, namun setiap pelanggan kini menemukan pesanannya lewat ref di idx_pelanggan — satu lompatan, bukan sapuan. Bandingkan rows kedua plan: dengan index, biaya sisi o adalah 1 per lookup; tanpa index, seluruh 15 baris pesanan harus diperiksa untuk setiap pelanggan.
Hindari SELECT *: Kolom Berlebih Merampas Covering Index #
Terakhir, tuas termurah: daftar kolom yang kita minta. SELECT * membawa dua denda. Denda pertama jelas — semua kolom dikirim ke aplikasi walaupun sebagian besar tidak dipakai, memboroskan jaringan dan memori. Denda kedua lebih halus dan paling sering terlewat: query berhenti bisa dilayani sepenuhnya oleh index (covering index):
EXPLAIN SELECT * FROM pelanggan WHERE kota = 'Jakarta';
EXPLAIN SELECT id, kota FROM pelanggan WHERE kota = 'Jakarta';Output yang Dihasilkan:
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | pelanggan | NULL | ref | idx_kota | idx_kota | 162 | const | 3 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
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 | SIMPLE | pelanggan | NULL | ref | idx_kota | idx_kota | 162 | const | 3 | 100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)Kedua plan sama-sama memakai idx_kota dengan type = ref, tetapi hanya plan kedua yang menulis Using index di Extra. Sebabnya: index InnoDB menyimpan kolom kunci plus primary key — id dan kota lengkap tersedia di dalam index, sehingga baris tabel tidak perlu dibuka sama sekali. SELECT * memaksa server membuka baris penuh untuk setiap kecocokan, termasuk nama, email, dan tanggal_daftar yang tidak dibutuhkan. Pada query panas yang dipanggil ribuan kali per detik, selisih “buka baris” lawan “baca index saja” sangat terasa.
Membersihkan Database Latihan #
DROP DATABASE belajar_opt;Output yang Dihasilkan:
Query OK, 2 rows affected (0.00 sec)Referensi #
- JOIN Clause — MySQL 8.4 Reference Manual
- EXPLAIN Output Format — MySQL 8.4 Reference Manual
- WHERE Clause Optimization — MySQL 8.4 Reference Manual
Empat tuas join kini sudah pernah kita putar sendiri: urutan driving table, STRAIGHT_JOIN untuk memaksa, index di kolom join melawan join buffer, dan daftar kolom yang menjaga covering index. Tuas berikutnya bahkan lebih fundamental — karena sebelum join bisa dioptimalkan, index yang dipakainya harus ada dan dipilih dengan benar. Chapter Index Optimization menyusun strategi memilih kolom, urutan composite, sampai membersihkan index yang menganggur.