Kolom email yang kita index berulang kali di section ini bertipe VARCHAR(120). Index kolom penuh untuknya menyimpan hingga ratusan byte per entri — dan pada tabel jutaan baris, struktur index semacam itu sendiri menjadi colossus penyimpanan. Kabar baiknya: sebagian besar kolom panjang punya keunikan yang terkonsentrasi di potongan awalnya — dua alamat email yang berbeda hampir selalu sudah berbeda pada sepuluh karakter pertama. MySQL memanfaatkan fakta ini lewat index prefix: index yang hanya menyimpan N karakter awal dari kolom. Pada chapter ini kita membuat index prefix email(10), membaca jejaknya di SHOW INDEX dan EXPLAIN, membandingkannya langsung dengan index kolom penuh, lalu memahami batasan yang menyertainya.

Menyiapkan Tabel Latihan #

Tabel pelanggan memuat sepuluh baris dengan email yang seluruhnya berbeda sejak sepuluh karakter pertama — kondisi ideal untuk prefix pendek:

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 pelanggan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  email VARCHAR(120) NOT NULL,
  kota VARCHAR(40) NOT NULL
);

INSERT INTO pelanggan (nama, email, kota) VALUES
  ('Budi Santoso',  '[email protected]',  'Jakarta'),
  ('Siti Rahayu',   '[email protected]',   'Bandung'),
  ('Andi Wijaya',   '[email protected]',   'Surabaya'),
  ('Rina Kartika',  '[email protected]',  'Jakarta'),
  ('Dewi Lestari',  '[email protected]',  'Yogyakarta'),
  ('Tono Sutrisno', '[email protected]', 'Bandung'),
  ('Sari Wulandari','[email protected]','Medan'),
  ('Joko Pratama',  '[email protected]',  'Jakarta'),
  ('Nurul Hidayah', '[email protected]', 'Surabaya'),
  ('Rizky Maulana', '[email protected]', 'Medan');

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 pada Sebagian Kolom #

Perintahnya adalah CREATE INDEX biasa dengan satu tambahan kecil — panjang prefix ditulis dalam kurung setelah nama kolom:

sql 
CREATE INDEX idx_email ON pelanggan(email(10));

Output yang Dihasilkan:

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

Notasi email(10) berarti: indeks hanya sepuluh karakter pertama dari nilai email. Entri index untuk baris pertama menyimpan budi.santi — sepuluh karakter awal — bukan alamat lengkapnya. Jejak prefix tercatat jelas saat tabel diinspeksi:

sql 
SHOW INDEX FROM pelanggan;

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 |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| pelanggan |          0 | PRIMARY   |            1 | id          | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| pelanggan |          1 | idx_email |            1 | email       | A         |          10 |       10 |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

Kolom yang sebelumnya selalu NULL kini terisi: Sub_part bernilai 10, mencatat jumlah karakter yang diindeks. Pada index kolom penuh kolom ini NULL — tidak ada pemotongan. Satu kolom SHOW INDEX ini adalah cara paling cepat membedakan index prefix dari index biasa.

Lookup dengan Prefix Index #

Index prefix tetap berfungsi penuh sebagai jalur pencarian equality. Pencarian berdasarkan email lengkap menuju prefixnya lebih dulu:

sql 
EXPLAIN SELECT * FROM pelanggan WHERE email = '[email protected]';

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_email     | idx_email | 42      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Rencana eksekusinya sehat: type = ref dan key = idx_email. Perhatikan dua detail. Pertama, key_len bernilai 42 — sepuluh karakter dikalikan empat byte per karakter utf8mb4 ditambah dua byte informasi panjang variabel; index penuh pada VARCHAR(120) akan menuntut hingga 482 byte. Kedua, kolom Extra menampilkan Using where: karena index hanya menyimpan sepuluh karakter, kandidat yang ditemukan index masih harus diperiksa ulang terhadap email lengkap di baris tabelnya — langkah verifikasi kecil yang index penuh tidak perlukan.

Batasan Prefix: Covering dan ORDER BY #

Dua batasan prefix index muncul dari sifatnya yang hanya menyimpan potongan nilai. Batasan pertama: index prefix tidak pernah bisa menjadi covering index untuk kolomnya — query yang hanya membutuhkan kolom email pun tetap harus berpindah ke baris tabel, karena yang tersedia di index hanyalah sepuluh karakter awal, bukan nilai lengkap. Batasan kedua berhubungan dengan pengurutan: keterurutan sepuluh karakter awal tidak sama dengan keterurutan alamat lengkap, sehingga ORDER BY email tidak bisa dilayani index prefix. Kita buktikan batasan kedua ini langsung pada index prefix yang sedang terpasang:

sql 
EXPLAIN SELECT email FROM pelanggan ORDER BY email;

Output yang Dihasilkan:

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

Rencananya memang suram: type = ALL dengan Using filesort — MySQL membaca seluruh tabel lalu menyortir hasilnya dengan langkah sortir terpisah, karena keterurutan potongan sepuluh karakter tidak bisa dipercaya untuk memesan alamat lengkap. Query semacam ini tetap benar, tetapi pekerjaan sortirnya dikerjakan penuh setiap kali query berjalan. Dua batasan ini adalah harga pasti dari penghematan ruang — bukan kebetulan yang mungkin lolos pada data tertentu.

Prefix versus Index Kolom Penuh #

Mari kita adu langsung dengan index kolom penuh. Index prefix kita ganti, lalu seluruh pengamatan diulang:

sql 
DROP INDEX idx_email ON pelanggan;
CREATE INDEX idx_email_penuh ON pelanggan(email);

SHOW INDEX FROM pelanggan;

Output yang Dihasilkan:

text 
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

+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| pelanggan |          0 | PRIMARY         |            1 | id          | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| pelanggan |          1 | idx_email_penuh |            1 | email       | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
sql 
EXPLAIN SELECT * FROM pelanggan WHERE email = '[email protected]';

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_email_penuh | idx_email_penuh | 482     | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
sql 
EXPLAIN SELECT email FROM pelanggan ORDER BY email;

Output yang Dihasilkan:

text 
+----+-------------+-----------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | pelanggan | NULL       | index | NULL          | idx_email_penuh | 482     | NULL |   10 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Perbandingannya kini utuh. Sub_part kembali NULL dan key_len melonjak dari 42 ke 482 — index kini siap menyimpan hingga 120 × 4 + 2 byte per entri, sebelas kali lipat kapasitas prefix-nya. Pada sepuluh baris latihan selisih ini tak terasa; pada tabel pengguna berjuta baris, selisih ratusan byte per entri berarti ratusan megabyte ruang index tambahan. Sebagai gantinya, dua batasan tadi hilang serentak: lookup equality tidak lagi memerlukan Using where karena nilai lengkap tersimpan di index, dan ORDER BY email kini dilayani type = index — entri index dibaca secara berurutan sehingga pengurutan terpenuhi tanpa Using filesort. Bonusnya terlihat di Extra: Using index menandakan query ini sekaligus menjadi covering index — kolom email yang diminta tersedia utuh di index, sehingga tabel sama sekali tidak disentuh.

AspekPrefix email(10)Kolom penuh
Ruang per entri (maksimum)42 byte482 byte
Lookup equality emailTerpakai, plus verifikasi ulangTerpakai, cocok langsung
Lookup LIKE 'awalan%'TerpakaiTerpakai
Menyajikan nilai kolom dari index (covering)Tidak bisa — potongan sajaBisa
ORDER BY emailFilesortTerpakai (type = index)

Memilih Panjang Prefix #

Berapa karakter yang tepat? Prinsipnya: cukup panjang untuk membedakan hampir semua baris, cukup pendek untuk hemat ruang. Email kita sudah berbeda total pada sepuluh karakter pertama, jadi prefix 10 nyaris menyamai selektivitas index penuh dengan sepersebelas ruangnya. Dua cara menimbang panjang kandidat: bandingkan jumlah nilai berbeda pada prefix terhadap kolom penuh — misalnya COUNT(DISTINCT LEFT(email, 10)) versus COUNT(DISTINCT email) — dan bila keduanya hampir sama, prefix itu sudah menangkap hampir seluruh keunikan kolom. Pola nilai juga berperan: email perusahaan yang seragam formatnya ([email protected], [email protected]) sudah berbeda pada potongan yang sangat pendek, sedangkan URL panjang dengan awalan identik butuh potongan lebih panjang agar membedakan.

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 #

Index prefix melengkapi kotak peralatan kita: hemat ruang untuk kolom panjang, dengan kesadaran penuh atas batasannya pada covering dan pengurutan. Hingga titik ini semua index kita berdiri atas keputusan eksplisit kita sendiri. Chapter berikutnya membahas index yang hadir tanpa kita minta — index otomatis pada kolom foreign key — beserta konsekuensinya ketika index tersebut ingin dihapus.