Cara Optimasi Query MySQL dengan Index

Seiring bertambahnya jumlah data di database, query yang tadinya cepat bisa menjadi lambat. Salah satu solusi paling efektif untuk mempercepat query di MySQL adalah menggunakan Index. Index bekerja seperti indeks buku: daripada membaca setiap halaman satu per satu, Anda langsung melompat ke halaman yang tepat. Artikel ini membahas cara membuat dan menggunakan index MySQL secara tepat.

Apa Itu Index di MySQL?

Index adalah struktur data tambahan yang MySQL simpan untuk mempercepat operasi pencarian. Tanpa index, MySQL harus melakukan Full Table Scan — membaca setiap baris dari awal hingga akhir. Dengan index, MySQL bisa langsung menemukan data yang dicari dalam waktu jauh lebih singkat. Kelemahannya, index membutuhkan ruang penyimpanan tambahan dan sedikit memperlambat operasi INSERT, UPDATE, dan DELETE.

Melihat Performa Query dengan EXPLAIN

Sebelum membuat index, gunakan EXPLAIN untuk menganalisis bagaimana MySQL menjalankan query Anda:

-- Contoh tabel tanpa index (selain PRIMARY KEY)
CREATE TABLE produk (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_produk VARCHAR(200) NOT NULL,
    kategori VARCHAR(50) NOT NULL,
    harga DECIMAL(10, 2) NOT NULL,
    stok INT NOT NULL DEFAULT 0,
    tgl_dibuat DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Analisis query sebelum index
EXPLAIN SELECT * FROM produk WHERE kategori = 'Elektronik';

-- Kolom penting yang perlu diperhatikan:
-- type: ALL = full scan (buruk), ref/range/const = pakai index (baik)
-- key: nama index yang digunakan (NULL = tidak pakai index)
-- rows: estimasi jumlah baris yang dibaca
-- Extra: info tambahan seperti "Using filesort", "Using temporary"

Membuat Index Dasar (Single Column Index)

Buat index pada kolom yang sering digunakan di klausa WHERE:

-- Cara 1: Membuat index saat CREATE TABLE
CREATE TABLE produk (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_produk VARCHAR(200) NOT NULL,
    kategori VARCHAR(50) NOT NULL,
    harga DECIMAL(10, 2) NOT NULL,
    stok INT NOT NULL DEFAULT 0,
    INDEX idx_kategori (kategori)
) ENGINE=InnoDB;

-- Cara 2: Menambahkan index ke tabel yang sudah ada
CREATE INDEX idx_kategori ON produk (kategori);

-- Cara 3: Menggunakan ALTER TABLE
ALTER TABLE produk ADD INDEX idx_harga (harga);

-- Cek ulang EXPLAIN setelah index dibuat
EXPLAIN SELECT * FROM produk WHERE kategori = 'Elektronik';

Composite Index (Index Multi Kolom)

Jika query sering memfilter berdasarkan beberapa kolom sekaligus, gunakan Composite Index:

-- Buat composite index untuk query yang sering filter kategori + harga
CREATE INDEX idx_kategori_harga ON produk (kategori, harga);

-- Query ini memanfaatkan composite index dengan baik
SELECT * FROM produk
WHERE kategori = 'Elektronik'
AND harga BETWEEN 1000000 AND 5000000;

-- Aturan "Leftmost Prefix": composite index (A, B, C)
-- bisa dipakai untuk: WHERE A, WHERE A AND B, WHERE A AND B AND C
-- TIDAK bisa untuk: WHERE B saja, atau WHERE C saja

UNIQUE Index

UNIQUE Index memastikan tidak ada nilai duplikat pada kolom tersebut, sekaligus berfungsi sebagai index untuk mempercepat pencarian:

-- Tambah kolom SKU dan buat unique index
ALTER TABLE produk ADD COLUMN sku VARCHAR(50);

-- Buat unique index
CREATE UNIQUE INDEX idx_unique_sku ON produk (sku);

-- Atau saat CREATE TABLE
CREATE TABLE produk_v2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    nama_produk VARCHAR(200) NOT NULL,
    UNIQUE INDEX idx_sku (sku)
) ENGINE=InnoDB;

FULLTEXT Index untuk Pencarian Teks

Untuk pencarian teks seperti fitur search, gunakan FULLTEXT Index:

-- Buat tabel artikel dengan fulltext index
CREATE TABLE artikel (
    id INT AUTO_INCREMENT PRIMARY KEY,
    judul VARCHAR(255) NOT NULL,
    isi TEXT NOT NULL,
    FULLTEXT INDEX ft_judul_isi (judul, isi)
) ENGINE=InnoDB;

-- Gunakan MATCH ... AGAINST untuk pencarian fulltext
SELECT id, judul,
    MATCH(judul, isi) AGAINST('mysql database' IN NATURAL LANGUAGE MODE) AS relevansi
FROM artikel
WHERE MATCH(judul, isi) AGAINST('mysql database' IN NATURAL LANGUAGE MODE)
ORDER BY relevansi DESC
LIMIT 10;

Melihat Semua Index pada Tabel

-- Tampilkan semua index pada tabel produk
SHOW INDEX FROM produk;

-- Atau query ke INFORMATION_SCHEMA
SELECT
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    NON_UNIQUE,
    SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'produk'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

Menghapus Index

-- Hapus index berdasarkan nama
DROP INDEX idx_kategori ON produk;

-- Atau menggunakan ALTER TABLE
ALTER TABLE produk DROP INDEX idx_harga;

Kapan Sebaiknya Membuat Index?

• Kolom yang sering digunakan di klausa WHERE, JOIN ON, ORDER BY, atau GROUP BY.
• Kolom dengan kardinalitas tinggi (banyak nilai unik), seperti email atau nomor telepon.
• Kolom Foreign Key yang digunakan untuk JOIN antar tabel.

Kapan Sebaiknya Menghindari Index?

• Tabel yang sangat kecil — Full Scan lebih cepat daripada menggunakan index.
• Kolom dengan kardinalitas rendah seperti kolom gender (hanya berisi 2 nilai).
• Tabel yang lebih sering dioperasikan INSERT/UPDATE/DELETE daripada SELECT.

Kesimpulan

Index adalah alat utama untuk mengoptimasi performa query di MySQL. Gunakan EXPLAIN untuk menganalisis apakah query Anda sudah memanfaatkan index dengan baik. Buat index pada kolom yang sering difilter atau dijoinkan, dan pertimbangkan composite index untuk query multi-kondisi. Namun ingat, index bukan solusi universal — terlalu banyak index justru bisa memperlambat operasi tulis. Kuncinya adalah memonitor query lambat dan menambahkan index secara strategis.