Membuat dan Menghapus INDEX

Konsep index yang kita bangun di chapter sebelumnya kini perlu diturunkan menjadi perintah konkret. Pengelolaan index adalah pekerjaan DDL — keluarga perintah yang sama dengan CREATE TABLE dan ALTER TABLE — dengan tiga kata kunci utama: CREATE INDEX untuk membuat, DROP INDEX untuk menghapus, dan SHOW INDEX untuk menginspeksi. Pada chapter ini kita berlatih ketiganya pada tabel karyawan, membaca keluaran SHOW INDEX kolom per kolom supaya inspeksi tidak lagi terasa seperti tabel misterius, lalu menutup dengan dua pola penting: mendeklarasikan index langsung di dalam CREATE TABLE, dan mengenali index yang hadir otomatis tanpa perintah kita.

Menyiapkan Tabel Latihan #

Tabel karyawan berisi sepuluh baris yang sama dengan chapter sebelumnya — cukup kecil untuk diinspeksi mata, cukup bervariasi untuk latihan index pada beberapa kolom:

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

Sebelum menambahkan apa pun, mari kita lihat kondisi awalnya. Tabel baru yang hanya punya primary key sudah memuat satu index:

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       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

Satu baris keluaran mewakili satu index — PRIMARY — yang hadir otomatis karena kolom id dideklarasikan sebagai PRIMARY KEY. Tidak ada index lain, sehingga pencarian berdasarkan kota atau email masih berjalan sebagai full table scan.

CREATE INDEX pada Tabel yang Sudah Ada #

Bentuk perintah paling umum untuk menambah index pada tabel yang sudah berjalan adalah CREATE INDEX:

sql
CREATE INDEX idx_kota ON karyawan(kota);

Output yang Dihasilkan:

text

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

Mari kita bedah baris perintah tersebut satu per satu: CREATE INDEX membuka perintah pembuatan index; idx_kota adalah nama index yang kita pilih sendiri; ON karyawan menyatakan tabel targetnya; dan (kota) di dalam kurung adalah daftar kolom yang menjadi kunci index — nanti kita akan memasukkan lebih dari satu kolom di posisi ini. Nama idx_kota mengikuti konvensi idx_<nama_kolom> yang dipakai konsisten di seluruh course: prefiks idx_ langsung memberi tahu pembaca bahwa objek ini index biasa (bukan constraint), dan bagian belakangnya menunjuk kolom kuncinya. Untuk index unik, konvensi yang akan kita pakai di chapter berikutnya adalah prefiks uq_.

Inspeksi ulang memastikan index tersebut benar-benar terpasang:

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_kota |            1 | kota        | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

Membaca Keluaran SHOW INDEX #

Tabel SHOW INDEX memuat lima belas kolom, dan empat di antaranya sudah cukup untuk membaca struktur index sehari-hari. Mari kita petakan dari keluaran di atas:

Kolom	Arti	Nilai pada `idx_kota`
`Key_name`	Nama index	`idx_kota`
`Column_name`	Kolom yang menjadi kunci pada baris ini	`kota`
`Non_unique`	`1` bila nilai boleh kembar, `0` bila harus unik	`1`
`Seq_in_index`	Posisi kolom di index majemuk, mulai dari `1`	`1`
`Index_type`	Struktur yang dipakai	`BTREE`

Tiga catatan membantu membaca tabel ini dengan benar. Pertama, satu index menghasilkan satu baris per kolom kuncinya — index tunggal idx_kota menghasilkan satu baris, sedangkan index dua kolom akan menghasilkan dua baris dengan Key_name sama dan Seq_in_index berbeda; kita akan melihat bentuk itu di chapter composite index. Kedua, Non_unique akan bernilai 0 pada index PRIMARY dan semua index unik — nilai 0 pada PRIMARY di atas adalah buktinya. Ketiga, kolom Cardinality menampilkan perkiraan jumlah nilai berbeda di dalam index; angka ini hanya estimasi untuk optimizer, bukan angka pasti, sehingga wajar bila sedikit meleset dari kenyataan.

Mendeklarasikan Index di Dalam CREATE TABLE #

Index tidak harus ditambahkan belakangan. Saat merancang tabel baru, index bisa dideklarasikan langsung di dalam CREATE TABLE — pola yang membuat struktur tabel dan jalur pencariannya terbaca dalam satu tempat:

sql
CREATE TABLE produk (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kategori VARCHAR(30) NOT NULL,
  harga DECIMAL(10,2) NOT NULL,
  INDEX idx_kategori (kategori),
  UNIQUE KEY uq_nama (nama)
);

INSERT INTO produk (nama, kategori, harga) VALUES
  ('Kopi Gayo',  'kopi',    55000.00),
  ('Teh Melati', 'teh',     25000.00),
  ('Kopi Toraja','kopi',    62000.00),
  ('Gula Aren',  'pemanis', 15000.00),
  ('Teh Hijau',  'teh',     27000.00),
  ('Susu Kental','pemanis', 18000.00);

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

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

Dua baris terakhir definisi tabel adalah inti pola ini. Baris INDEX idx_kategori (kategori) mendeklarasikan index biasa dengan nama eksplisit — kata kunci INDEX boleh juga ditulis KEY, keduanya identik di MySQL. Baris UNIQUE KEY uq_nama (nama) mendeklarasikan index unik: nilainya juga diindeks seperti index biasa, tetapi server sekaligus menjaga agar nilai nama tidak pernah kembar. Hasilnya terlihat saat tabel diinspeksi:

sql
SHOW INDEX FROM produk;

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 |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| produk |          0 | PRIMARY      |            1 | id          | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| produk |          0 | uq_nama      |            1 | nama        | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| produk |          1 | idx_kategori |            1 | kategori    | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

Tiga index kini terdaftar: PRIMARY milik kolom id, uq_nama dengan Non_unique = 0, dan idx_kategori dengan Non_unique = 1. Perhatikan bahwa uq_nama hadir sebagai index meskipun kita tidak pernah menjalankan CREATE INDEX — deklarasi UNIQUE di CREATE TABLE otomatis memasang indexnya.

DROP INDEX dan Jebakan Nama #

Menghapus index memakai nama indexnya, bukan nama kolomnya:

sql
DROP INDEX idx_kota ON karyawan;
SHOW INDEX FROM karyawan;

Output yang Dihasilkan:

text
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 |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| karyawan |          0 | PRIMARY  |            1 | id          | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

Baris idx_kota lenyap dari daftar; hanya PRIMARY yang tersisa. Penghapusan ini tidak menyentuh satu pun data karyawan — baris, kolom, dan nilai kota semuanya utuh, karena yang dihapus hanyalah struktur pencariannya. Bentuk padanannya juga tersedia melalui ALTER TABLE karyawan DROP INDEX idx_kota; — hasil identik, sekadar gaya penulisan alternatif yang sering dipakai saat satu statement ALTER TABLE mengubah beberapa hal sekaligus.

Menghapus index yang tidak ada — misalnya karena terlanjur terhapus atau salah tik nama — langsung ditolak server:

sql
-- @error
DROP INDEX idx_kota ON karyawan;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

ERROR 1091 (42000): Can't DROP 'idx_kota'; check that column/key exists
Bye

Pesan ERROR 1091 secara eksplisit meminta kita memeriksa kembali nama index atau kolom yang dituju. Perhatikan pula bahwa perintah ini memakai nama index (idx_kota), bukan nama kolom (kota) — menulis DROP INDEX kota ON karyawan akan menimbulkan error yang sama meskipun kolom kota tetap ada, karena yang dicari adalah objek index bernama kota yang memang tidak pernah kita buat.

Index yang Hadir Otomatis #

Keluarga SHOW INDEX di atas sudah memperlihatkan pola penting: beberapa index hadir tanpa pernah kita minta secara eksplisit. Tiga sumber menciptakan index otomatis di MySQL. Pertama, PRIMARY KEY selalu membangun index bernama PRIMARY — terlihat di karyawan maupun produk. Kedua, setiap constraint UNIQUE — baik tulisan UNIQUE pada definisi kolom maupun UNIQUE KEY di akhir definisi tabel — memasang index unik dengan nama constraint-nya, seperti uq_nama pada produk. Ketiga, FOREIGN KEY pada InnoDB otomatis menjamin kolom FK terindeks: bila belum ada index yang cocok, server membuatnya diam-diam saat tabel anak diciptakan — perilaku yang akan kita buktikan dengan eksperimen penuh di chapter khusus foreign key.

Karena itu, sebelum membuat index baru pada kolom yang sudah diikat constraint, biasakan memeriksa SHOW INDEX terlebih dahulu. Index untuk kolom tersebut mungkin sudah tersedia dengan nama bawaan, dan menduplikasinya hanya menambah beban tulis tanpa manfaat pencarian.

Membersihkan Database Latihan #

Latihan DDL selesai dan database belajar_index kita jatuhkan kembali:

sql
DROP DATABASE belajar_index;

Output yang Dihasilkan:

text

Query OK, 2 rows affected (0.00 sec)

Referensi #

Perintah DDL index kini lengkap kita kuasai: membuat lewat CREATE INDEX maupun deklarasi inline, menginspeksi lewat SHOW INDEX yang sudah terbaca kolom per kolom, dan menghapus lewat DROP INDEX. Dari semua index yang bisa dibuat, satu varian paling sering dipakai aplikasi nyata: index yang sekaligus menjaga keunikan data. Chapter berikutnya membedah CREATE UNIQUE INDEX — kekuatan integritasnya, perlakuan khususnya terhadap NULL, dan bonus kecepatan pencarian yang dibawanya.