COMPOSITE INDEX

Filter satu kolom memang dilayani index satu kolom — tetapi query dunia nyata jarang sesederhana itu. Pencarian karyawan paling sering menyebut nama lengkap, dan laporan penjualan menyaring kota sekaligus rentang tanggal. Untuk pola seperti ini MySQL menyediakan composite index: satu index yang kuncinya adalah gabungan beberapa kolom, disimpan dalam satu struktur terurut. Keputusan desainnya terletak pada satu hal yang kelihatannya sepele — urutan kolom — padahal urutan itu menentukan query mana yang bisa dilayani index dan query mana yang kembali menjadi full table scan. Pada chapter ini kita membuktikan aturan leftmost prefix dengan EXPLAIN, lalu membandingkan composite index dengan alternatifnya: dua index terpisah.

Menyiapkan Tabel Latihan #

Kita pakai kembali tabel karyawan sepuluh baris yang sudah akrab, dengan nama yang sengaja dipisah menjadi kolom nama_depan dan nama_belakang — bentuk yang umum di tabel nyata:

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

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 karyawan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama_depan VARCHAR(50) NOT NULL,
  nama_belakang VARCHAR(50) NOT NULL,
  email VARCHAR(120) NOT NULL,
  kota VARCHAR(40) NOT NULL,
  gaji DECIMAL(10,2) NOT NULL,
  tanggal_masuk DATE NOT NULL
);

INSERT INTO karyawan (nama_depan, nama_belakang, email, kota, gaji, tanggal_masuk) VALUES
  ('Budi',  'Santoso',  '[email protected]',  'Jakarta',    8500000.00, '2026-01-05'),
  ('Siti',  'Rahayu',   '[email protected]',   'Bandung',    7200000.00, '2026-01-12'),
  ('Andi',  'Wijaya',   '[email protected]',   'Surabaya',   9100000.00, '2026-02-01'),
  ('Rina',  'Kartika',  '[email protected]',  'Jakarta',    6800000.00, '2026-02-10'),
  ('Dewi',  'Lestari',  '[email protected]',  'Yogyakarta', 7500000.00, '2026-03-03'),
  ('Tono',  'Sutrisno', '[email protected]', 'Bandung',    6000000.00, '2026-03-15'),
  ('Sari',  'Wulandari','[email protected]','Medan',      7300000.00, '2026-04-01'),
  ('Joko',  'Pratama',  '[email protected]',  'Jakarta',    8900000.00, '2026-04-20'),
  ('Nurul', 'Hidayah',  '[email protected]', 'Surabaya',   7000000.00, '2026-05-05'),
  ('Rizky', 'Maulana',  '[email protected]', 'Medan',      7700000.00, '2026-05-18');

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

Membuat Index Dua Kolom #

Composite index dibuat dengan memasukkan beberapa kolom ke dalam daftar kunci — urutan penulisannya adalah urutan yang menentukan:

sql
CREATE INDEX idx_nama ON karyawan(nama_depan, nama_belakang);

Output yang Dihasilkan:

text

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

sql
SHOW INDEX FROM karyawan;

Output yang Dihasilkan:

text
+----------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| karyawan |          0 | PRIMARY  |            1 | id            | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| karyawan |          1 | idx_nama |            1 | nama_depan    | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| karyawan |          1 | idx_nama |            2 | nama_belakang | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

Perhatikan keluaran SHOW INDEX: index idx_nama kini menghasilkan dua baris — satu untuk nama_depan dengan Seq_in_index = 1, satu untuk nama_belakang dengan Seq_in_index = 2. Angka urutan ini menegaskan cara index mengurutkan datanya: entri-entri diurutkan lebih dulu berdasarkan nama_depan, lalu untuk nilai nama_depan yang sama diurutkan lagi berdasarkan nama_belakang. Kamus telepon adalah analogi yang tepat — seluruhnya terurut menurut nama belakang, lalu nama depan, sehingga semua “Santoso” berderet berdampingan dan di dalam barisan itu Budi, Dwi, dan Eka tersusun alfabetis.

Leftmost Prefix: Kolom Pertama Menentukan Semuanya #

Aturan paling penting composite index bernama leftmost prefix: index hanya bisa dilayani oleh query yang menyaring awalan kolomnya, mulai dari kolom pertama. Query yang menyebut nama_depan memenuhi awalan satu kolom:

sql
EXPLAIN SELECT * FROM karyawan WHERE nama_depan = 'Budi';

Output yang Dihasilkan:

text
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | karyawan | NULL       | ref  | idx_nama      | idx_nama | 202     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Kolom key bernilai idx_nama — index dua kolom dilayani walau query hanya menyebut kolom pertamanya. Query yang menyebut kedua kolom tentu dilayani juga, dan justru inilah bentuk yang paling dimanfaatkan index:

sql
EXPLAIN SELECT * FROM karyawan WHERE nama_depan = 'Budi' AND nama_belakang = 'Santoso';

Output yang Dihasilkan:

text
+----+-------------+----------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref         | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | karyawan | NULL       | ref  | idx_nama      | idx_nama | 404     | const,const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Dua perbedaan mencolok muncul pada keluaran kedua. Kolom key_len melonjak dari 202 menjadi 404 — angka ini mencatat berapa byte dari kunci index yang benar-benar dipakai untuk mempersempit pencarian: 202 berarti hanya bagian nama_depan yang terpakai, 404 berarti keduanya. Kolom ref yang semula const kini const,const — dua nilai pembanding, satu untuk tiap kolom kunci. Semakin banyak bagian kunci yang terpakai, semakin sempit wilayah index yang dibaca, dan semakin sedikit baris yang disentuh.

Kolom Kedua Sendirian Tidak Bisa Memakai Index #

Sekarang ujian sebenarnya: bagaimana bila query hanya menyebut nama_belakang? Index tetap ada, data tetap ada — mari kita lihat rencana eksekusinya:

sql
EXPLAIN SELECT * FROM karyawan WHERE nama_belakang = 'Santoso';

Output yang Dihasilkan:

text
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | karyawan | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Kolom type bernilai ALL, possible_keys dan key sama-sama NULL: optimizer memilih full table scan dan sama sekali tidak mempertimbangkan idx_nama. Penyebabnya murni struktur: index mengurutkan berdasarkan nama_depan lebih dulu, sehingga semua “Santoso” tersebar di posisi index yang tak terprediksi — dari Budi Santoso di awal hingga pasangan nama depan apa pun di belakangnya. Mencari seluruh “Santoso” dari index berarti membaca hampir seluruh struktur, dan optimizer memilih jujur membaca tabelnya saja. Query-nya sendiri tetap benar, hasilnya tetap lengkap:

sql
SELECT id, nama_depan, nama_belakang FROM karyawan WHERE nama_belakang = 'Santoso';

Output yang Dihasilkan:

text
+----+------------+---------------+
| id | nama_depan | nama_belakang |
+----+------------+---------------+
|  1 | Budi       | Santoso       |
+----+------------+---------------+
1 row in set (0.00 sec)

Sekali lagi index tidak pernah mengubah hasil — yang hilang hanyalah jalan cepatnya. Analogi kamus telepon menjelaskan mengapa: mencari semua orang ber nama belakang “Santoso” dari kamus yang terurut menurut nama belakang mudah; mencarinya di kamus yang terurut menurut nama depan memaksa kita membaca seluruh kamus. Urutan kolom di index dan urutan kolom di query harus bertemu di titik yang sama.

Index (a, b) versus Dua Index Terpisah #

Alternatif yang sering diusulkan: daripada satu index dua kolom, buat saja dua index satu kolom. Kita coba tambahkan index kedua yang terpisah dan amati keduanya berdampingan:

sql
CREATE INDEX idx_belakang ON karyawan(nama_belakang);

EXPLAIN SELECT * FROM karyawan WHERE nama_belakang = 'Santoso';

Output yang Dihasilkan:

text
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      | karyawan | NULL       | ref  | idx_belakang  | idx_belakang | 202     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

sql
EXPLAIN SELECT * FROM karyawan WHERE nama_depan = 'Budi' AND nama_belakang = 'Santoso';

Output yang Dihasilkan:

text
+----+-------------+----------+------------+------+-----------------------+----------+---------+-------------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys         | key      | key_len | ref         | rows | filtered | Extra |
+----+-------------+----------+------------+------+-----------------------+----------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | karyawan | NULL       | ref  | idx_nama,idx_belakang | idx_nama | 404     | const,const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+-----------------------+----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Query nama belakang kini dilayani idx_belakang — masalahnya memang teratasi. Namun perhatikan query dua kolom: possible_keys mencantumkan kedua index, dan optimizer tetap memilih idx_nama sebagai key-nya. Dua index terpisah masing-masing mengurutkan datanya sendiri; tidak ada cara sederhana menggabungkan dua daftar terurut yang berbeda menjadi satu jalur pencarian — optimizer paling banyak memakai salah satunya lalu menyaring sisanya. Index gabungan (a, b) justru melayani tiga pola sekaligus: filter a saja, filter a dan b bersama, dan — bila kuerinya sesuai — pengurutan berdasarkan a lalu b.

Aspek	Satu index `(a, b)`	Dua index terpisah
Filter `a` saja	Terpakai (prefix)	Terpakai lewat index `a`
Filter `b` saja	Tidak terpakai	Terpakai lewat index `b`
Filter `a` dan `b`	Terpakai penuh, dua kolom menyempitkan	Terpakai salah satu, kolom lain hanya menyaring
Struktur penyimpanan	Satu struktur tambahan	Dua struktur tambahan

Pilihan antara keduanya akhirnya mengikuti pola query nyata: bila b memang jarang dicari sendirian, satu index (a, b) lebih hemat dan lebih tajam; bila kedua kolom sama-sama sering menjadi filter tunggal, dua index terpisah memang lebih tepat.

Memilih Urutan Kolom #

Karena kolom pertama menentukan segalanya, memilih urutan adalah keputusan desain terbesar composite index. Tiga aturan praktis menjadi pegangan:

Kolom yang paling sering difilter sendirian naik ke depan — supaya leftmost prefix-nya dimanfaatkan oleh query tunggal itu.
Kolom equality (=) mendahulukan kolom range — bagian kunci setelah kolom range tidak lagi mempersempit pencarian pada pola rentang, jadi letakkan kolom >/BETWEEN di belakang.
Selektivitas tinggi di depan bila pola query seimbang — kolom dengan lebih banyak nilai berbeda memangkas kandidat lebih awal; angka Cardinality pada SHOW INDEX memberi gambarannya.

Membersihkan Database Latihan #

Database belajar_index kita jatuhkan kembali:

sql
DROP DATABASE belajar_index;

Output yang Dihasilkan:

text

Query OK, 1 row affected (0.00 sec)

Referensi #

Leftmost prefix kini menjadi naluri: kolom pertama adalah gerbang index, kolom kedua hanya berkuasa di dalam wilayah kolom pertama. Aturan yang sama berlaku untuk kolom teks yang panjang — dengan satu persoalan tambahan: VARCHAR(255) memaksa index menyimpan hingga ratusan byte per entri. MySQL punya solusi untuk itu: mengindeks hanya potongan awal kolom. Chapter berikutnya membedah index prefix beserta hemat ruangnya dan batasan-batasannya.