create index mysql

🏠 Beranda › Tutorial MySQL › Artikel 26

🔴 Lanjutan ⚡ Optimasi Database ⏱️ 13 Menit Baca 📘 Artikel 26 dari 28

Mempercepat Query:
Konsep INDEX dan Optimasi Performa

Kuasai INDEX MySQL optimasi query dan buat database-mu bekerja 100× lebih cepat — bukan mitos, ini matematika nyata.

7

Perintah SQL

9

Contoh Query

13

Menit Baca

Bayangkan kamu masuk ke sebuah perpustakaan besar dengan 1 juta buku — tapi tidak ada katalog, tidak ada rak berlabel, semua buku ditumpuk sembarangan. Kamu diminta mencari buku berjudul "Database Systems". Berapa lama? Bisa berjam-jam, kan? Nah, itulah yang terjadi pada MySQL saat mencari data di tabel besar tanpa Index. Dengan INDEX MySQL optimasi query, MySQL punya "katalog pintar" yang langsung menunjuk ke lokasi data yang kamu cari — dalam hitungan milidetik, bukan menit.

Ini adalah artikel ke-26 dari seri 28 artikel Belajar Query SQL MySQL. Di artikel sebelumnya kamu sudah belajar membuat View. Kini saatnya naik level: belajar bagaimana membuat query-mu bekerja jauh lebih cepat dengan teknik indexing yang tepat. Topik ini sangat krusial — terutama saat tabel datamu sudah mencapai ribuan hingga jutaan baris.

Di artikel ini kamu akan belajar: apa itu Index, jenis-jenis Index di MySQL, cara membuat dan menghapusnya, cara membaca perintah EXPLAIN untuk menganalisis query, serta kapan sebaiknya Index digunakan atau justru dihindari. Semua menggunakan contoh nyata dari database toko online.

⚡

Fakta Mengejutkan

Menurut berbagai benchmark database, menambahkan Index pada kolom yang sering digunakan di klausa WHERE bisa mempercepat query hingga 100–1000 kali lipat pada tabel dengan jutaan baris. Namun di saat yang sama, Index yang berlebihan justru bisa memperlambat operasi INSERT, UPDATE, dan DELETE karena MySQL harus memperbarui semua Index setiap ada perubahan data!

Apa Itu Index? Cara MySQL Menemukan Data Super Cepat

Index di MySQL bekerja persis seperti indeks di belakang buku pelajaran. Daripada membaca seluruh isi buku untuk mencari kata "normalisasi", kamu cukup lihat halaman berapa kata itu muncul — langsung lompat ke sana. Secara internal, MySQL menggunakan struktur data B-Tree (Balanced Tree) untuk menyimpan Index, yang memungkinkan pencarian logaritmik — jauh lebih cepat dari pemindaian baris demi baris.

Tanpa Index, MySQL melakukan Full Table Scan — membaca setiap baris dari awal sampai akhir. Dengan Index, MySQL langsung melompat ke lokasi data yang relevan. Perbedaannya dramatis saat tabel sudah berisi ratusan ribu baris.

-- Tanpa Index: MySQL scan SEMUA baris (lambat!)
SELECT * FROM pesanan
WHERE id_pelanggan = 42;

-- Cek: berapa baris yang harus di-scan?
EXPLAIN SELECT * FROM pesanan
WHERE id_pelanggan = 42;

📊 Output EXPLAIN (Sebelum Index — Full Table Scan):

id	select_type	table	type	key	rows	Extra
1	SIMPLE	pesanan	ALL	NULL	250.000	Using where

⚠️ Type ALL = Full Table Scan. MySQL membaca 250.000 baris hanya untuk menemukan data pelanggan #42!

💎 TIP PRAKTIS

Selalu gunakan EXPLAIN sebelum dan sesudah menambahkan Index untuk membuktikan efeknya. Perhatikan kolom type (idealnya ref atau eq_ref) dan rows (makin kecil makin baik).

INDEX MySQL Optimasi Query: Cara Membuat dan Menghapus Index

Membuat Index di MySQL sangat mudah. Ada beberapa cara: saat membuat tabel (CREATE TABLE), atau menambahkan ke tabel yang sudah ada (CREATE INDEX atau ALTER TABLE). Untuk toko online, kolom yang paling sering digunakan di WHERE adalah kandidat utama untuk di-index.

-- Cara 1: Tambah Index ke tabel yang sudah ada
CREATE INDEX idx_pelanggan
ON pesanan (id_pelanggan);

-- Cara 2: Index UNIQUE (nilai tidak boleh duplikat)
CREATE UNIQUE INDEX idx_email_unik
ON pelanggan (email);

-- Cara 3: Composite Index (kombinasi dua kolom)
CREATE INDEX idx_produk_kategori
ON produk (kategori, status);

-- Melihat semua Index di sebuah tabel
SHOW INDEX FROM pesanan;

-- Menghapus Index
DROP INDEX idx_pelanggan ON pesanan;

📊 Output SHOW INDEX FROM pesanan:

Table	Key_name	Column_name	Non_unique	Index_type
pesanan	PRIMARY	id_pesanan	0	BTREE
pesanan	idx_pelanggan	id_pelanggan	1	BTREE
pesanan	idx_tanggal	tanggal_pesan	1	BTREE

⚠️ PERINGATAN

Jangan asal membuat Index di semua kolom! Setiap Index membutuhkan ruang penyimpanan tambahan dan memperlambat operasi tulis (INSERT/UPDATE/DELETE). Aturan praktisnya: buat Index hanya pada kolom yang sering muncul di WHERE, JOIN ON, atau ORDER BY.

Membaca EXPLAIN: Diagnosa Query Seperti Dokter

EXPLAIN adalah alat diagnostik paling penting untuk INDEX MySQL optimasi query. Bayangkan seperti rontgen database-mu — kamu bisa melihat "organ dalam" eksekusi query: apakah MySQL menggunakan Index atau tidak, berapa baris yang dipindai, dan apa yang terjadi di balik layar.

Setelah menambahkan Index pada kolom id_pelanggan, jalankan EXPLAIN lagi dan bandingkan hasilnya:

-- Buat Index dulu
CREATE INDEX idx_pelanggan ON pesanan (id_pelanggan);

-- Sekarang EXPLAIN lagi query yang sama
EXPLAIN SELECT * FROM pesanan
WHERE id_pelanggan = 42;

📊 Output EXPLAIN (Setelah Index — Jauh Lebih Cepat!):

id	select_type	table	type	key	rows	Extra
1	SIMPLE	pesanan	ref	idx_pelanggan	12	Using index condition

✅ MySQL kini hanya membaca 12 baris (bukan 250.000)! Peningkatan performa lebih dari 20.000×!

💎 PANDUAN BACA EXPLAIN

Kolom type di output EXPLAIN adalah indikator terpenting:
🔴 ALL = Full Table Scan (terburuk)
🟡 index = Scan seluruh Index (lumayan buruk)
🟢 range = Scan sebagian Index (cukup baik)
✅ ref = Menggunakan Index dengan kondisi tertentu (bagus)
🏆 eq_ref / const = Satu baris pasti (terbaik)

Composite Index: Satu Index untuk Dua Kolom Sekaligus

Kadang query kamu melibatkan dua atau lebih kolom di klausa WHERE secara bersamaan. Alih-alih membuat dua Index terpisah, kamu bisa membuat satu Composite Index (Index gabungan). Analoginya seperti katalog perpustakaan yang diurutkan berdasarkan "genre" dulu, baru "judul" — pencarian jauh lebih terarah.

Di toko online, sering kali kita mencari pesanan berdasarkan kategori produk + status pembayaran sekaligus. Inilah kandidat sempurna untuk Composite Index:

-- Composite Index: urutan kolom sangat penting!
CREATE INDEX idx_kategori_status
ON produk (kategori, status);

-- Query ini AKAN menggunakan Index di atas
SELECT * FROM produk
WHERE kategori = 'Elektronik' AND status = 'aktif';

-- Query ini juga bisa pakai Index (kolom pertama saja)
SELECT * FROM produk
WHERE kategori = 'Elektronik';

-- Query ini TIDAK bisa pakai Index (lewati kolom pertama)
SELECT * FROM produk
WHERE status = 'aktif'; -- Full Scan!

📊 Output Query dengan Composite Index:

id_produk	nama_produk	kategori	harga	status
1	Laptop Gaming A	Elektronik	12.500.000	aktif
5	Headphone Pro Z	Elektronik	875.000	aktif
9	Webcam HD Ultra	Elektronik	450.000	aktif

⚠️ ATURAN LEFTMOST PREFIX

Composite Index (kategori, status) hanya bisa digunakan jika query menyertakan kolom paling kiri terlebih dahulu (yaitu kategori). Jika kamu hanya filter berdasarkan status saja tanpa kategori, Index ini tidak akan terpakai. Urutan kolom di Composite Index sangat menentukan!

📋 Referensi Sintaks Lengkap INDEX MySQL

-- ================================================
-- 1. MEMBUAT INDEX (berbagai varian)
-- ================================================

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX nama_index
ON nama_tabel (kolom1 [ASC|DESC], kolom2, ...);
                      -- UNIQUE: nilai harus unik
                      -- FULLTEXT: untuk pencarian teks penuh

-- ================================================
-- 2. ALTER TABLE (alternatif)
-- ================================================

ALTER TABLE nama_tabel
  ADD INDEX nama_index (kolom),
  ADD UNIQUE INDEX nama_index_unik (kolom);

-- ================================================
-- 3. MENGHAPUS INDEX
-- ================================================

DROP INDEX nama_index ON nama_tabel;
-- ATAU:
ALTER TABLE nama_tabel DROP INDEX nama_index;

🔬 Langkah Praktikum: Optimalkan Query Toko Online

1

Siapkan Tabel dengan Data Banyak

Buat tabel pesanan dan isi dengan data simulasi menggunakan stored procedure sederhana agar efek Index terasa nyata.

CREATE TABLE pesanan (
  id_pesanan INT PRIMARY KEY AUTO_INCREMENT,
  id_pelanggan INT NOT NULL,
  id_produk INT NOT NULL,
  jumlah INT DEFAULT 1,
  status_pembayaran ENUM('pending','lunas','batal'),
  tanggal_pesan DATETIME DEFAULT NOW()
);

2

Jalankan EXPLAIN Sebelum Index

Catat nilai kolom type dan rows sebagai baseline performa awal.

-- Baseline: catat hasil ini
EXPLAIN SELECT * FROM pesanan
WHERE id_pelanggan = 42
AND status_pembayaran = 'lunas';

3

Tambahkan Index yang Tepat

Buat Composite Index karena query menggunakan dua kolom sekaligus di WHERE.

CREATE INDEX idx_pelanggan_status
ON pesanan (id_pelanggan, status_pembayaran);

4

Jalankan EXPLAIN Setelah Index

Bandingkan hasilnya. Kolom type harusnya berubah dari ALL menjadi ref, dan rows turun drastis.

-- Sekarang bandingkan!
EXPLAIN SELECT * FROM pesanan
WHERE id_pelanggan = 42
AND status_pembayaran = 'lunas';

-- Lihat juga semua Index yang ada
SHOW INDEX FROM pesanan;

5

Audit & Hapus Index yang Tidak Terpakai

Secara berkala, cek Index mana yang jarang atau tidak pernah digunakan lalu hapus untuk menghemat storage dan mempercepat operasi tulis.

-- Cek statistik penggunaan Index
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'toko_online';

-- Hapus Index yang tidak terpakai
DROP INDEX idx_tidak_terpakai ON pesanan;

📊 Jenis-Jenis Index MySQL: Mana yang Harus Dipakai?

Jenis Index	Kegunaan	Contoh Kolom	Batasan
PRIMARY KEY	Identifikasi baris unik	id_pesanan	Hanya 1 per tabel, NOT NULL
UNIQUE INDEX	Cepat + tidak ada duplikat	email, username	Nilai harus unik
INDEX (biasa)	Percepat pencarian umum	id_pelanggan, kategori	Nilai boleh duplikat
COMPOSITE INDEX	Query multi-kolom WHERE	(kategori, status)	Aturan leftmost prefix
FULLTEXT INDEX	Pencarian kata dalam teks	deskripsi_produk	Hanya TEXT/VARCHAR
SPATIAL INDEX	Data geografis/koordinat	lokasi_toko (POINT)	Hanya tipe data spasial

🎯

Tips dari Trainer Lapangan

Kolom mana yang layak di-index? Gunakan rumus 80/20: identifikasi 20% query yang paling sering dijalankan di aplikasimu, lalu optimasi Index untuk query-query tersebut. Kolom yang ideal untuk Index adalah yang memiliki high cardinality (banyak nilai unik) — seperti email, nomor faktur, atau tanggal. Kolom dengan sedikit nilai unik seperti gender (M/F) atau status (aktif/nonaktif) seringkali tidak efisien untuk di-index karena MySQL tetap akan membaca banyak baris.

🚀

Insight: Kapan Index Justru Berbahaya?

Di lingkungan production dengan traffic tinggi, over-indexing bisa menjadi bumerang. Bayangkan tabel pesanan yang menerima 10.000 INSERT per menit — setiap INSERT harus memperbarui semua Index yang ada. Semakin banyak Index, semakin lambat proses penulisan. Pernah ada kasus nyata di mana menghapus 5 Index yang tidak terpakai dari sebuah tabel transaksi meningkatkan throughput INSERT hingga 40%!

🎓 Kesimpulan

• INDEX MySQL optimasi query adalah kunci untuk membuat database yang cepat, terutama saat data sudah mencapai skala besar.
• Gunakan EXPLAIN selalu sebelum dan sesudah menambahkan Index untuk mengukur dampaknya secara nyata.
• Index jenis PRIMARY KEY otomatis ada; tambahkan Index lain hanya pada kolom yang sering muncul di WHERE, JOIN, atau ORDER BY.
• Composite Index efektif untuk query multi-kolom, tapi perhatikan aturan leftmost prefix.
• Index mempercepat pembacaan (SELECT), tapi memperlambat penulisan (INSERT/UPDATE/DELETE) — selalu pertimbangkan keseimbangannya.
• Audit Index secara berkala dan hapus yang tidak terpakai untuk menjaga performa keseluruhan database.

Optimasi bukan sihir — ini adalah sains yang bisa dipelajari. Setiap kali query-mu berjalan lambat, ingat: kemungkinan besar ada Index yang hilang, atau ada Index yang salah tempat. Jadikan EXPLAIN sebagai teman setiamu dan database-mu akan berterima kasih!

💬 Tulis Komentar 🔖 Bookmark Seri Ini 📤 Share Artikel

---

📚

Navigasi Seri Artikel · MySQL Mastery

← Artikel Sebelumnya

Artikel 25/28: CREATE VIEW — Tabel Virtual MySQL

Membuat view sebagai tabel virtual dari query yang disimpan untuk menyederhanakan query kompleks.

← Baca Artikel 25

Artikel Selanjutnya →

Artikel 27/28: Transaksi MySQL — BEGIN, COMMIT, ROLLBACK

Melindungi integritas data dengan mekanisme transaksi — konsep ACID dan cara rollback saat terjadi error.

Baca Artikel 27 →

Artikel 26 dari 28 dalam seri ini 93% selesai

#MySQL #SQL #Database #Query #Tutorial #INDEX MySQL optimasi query #OptimisasiQuery #EXPLAIN