Pernah punya query SELECT yang jalan 8 detik di production? Saya pernah. Tabel orders di aplikasi e-commerce, cuma 2 juta row, tapi query filter by date + status kayak jalan di komputer tahun 2005. Setelah nambah satu composite index, turun jadi 0.03 detik. Dramatis banget bedanya.

MySQL indexing itu topik yang sering di-skip sama developer. Alasannya klasik: "database kan urusan DBA" atau "nanti aja kalau udah lemot". Padahal kalau paham indexing dari awal, kamu bisa hemat waktu debugging, hemat server cost, dan user pun senang karena website responsif.

Kenalan dengan MySQL Index

Index di MySQL itu ibarat daftar isi di buku. Tanpa index, MySQL harus baca semua halaman (full table scan) buat nyari data yang kamu mau. Dengan index, MySQL langsung loncat ke halaman yang tepat.

MySQL pakai B-Tree structure buat index-nya (kecuali FULLTEXT dan SPATIAL yang pakai struktur lain). B-Tree ini memungkinkan pencarian data secara logaritmik - bukan linear. Jadi kalau tabel kamu 1 juta row, MySQL cuma perlu sekitar 20 perbandingan buat nemuin satu row, bukan 1 juta.

-- Lihat index yang ada di tabel
SHOW INDEX FROM orders;

-- Cek apakah query pakai index
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND order_date > '2026-01-01';

Kolom type di hasil EXPLAIN itu kunci. Kalau ALL, berarti full table scan (jelek). Kalau ref atau range, berarti sudah pakai index (bagus). Kalau index, itu masih scan semua row tapi lewat index - kadang lebih lambat dari yang kamu kira.

Jenis-jenis Index di MySQL

MySQL punya beberapa jenis index, masing-masing punya use case berbeda:

1. Primary Key Index

Setiap tabel InnoDB pasti punya primary key. Kalau kamu nggak define, MySQL bikin hidden one. Primary key juga jadi clustered index - artinya data fisik di disk tersimpan berdasarkan urutan primary key.

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

Tips: pilih primary key yang monotonically increasing (auto_increment) karena kalau pakai UUID random, InnoDB harus sering reorganize halaman data - bikin insert lambat dan fragmentasi tinggi.

2. Unique Index

Selain bikin data valid (nggak ada duplikat), unique index juga bikin query pencarian jadi cepat karena MySQL tahu pasti cuma ada satu row yang match.

-- Email harus unik
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- Query ini sekarang super cepat
SELECT * FROM users WHERE email = '[email protected]';

3. Composite Index

Ini senjata utama buat query yang filter banyak kolom sekaligus. Urutan kolom di composite index itu penting banget - MySQL pakai aturan "leftmost prefix".

-- Composite index: status dulu, baru order_date
ALTER TABLE orders ADD INDEX idx_status_date (status, order_date);

-- Query ini PAKAI index (mulai dari kolom kiri)
SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM orders WHERE status = 'pending' AND order_date > '2026-01-01';

-- Query ini NGGAK pakai index (skip kolom kiri)
SELECT * FROM orders WHERE order_date > '2026-01-01';

Analoginya kayak indeks buku: kamu bisa cari "Bab 3, halaman 45" tapi nggak bisa langsung cari "halaman 45" tanpa tahu babnya.

4. Covering Index

Ini teknik level lanjut yang bikin query makin ngebut. Covering index artinya semua kolom yang di-SELECT sudah ada di index itu sendiri, jadi MySQL nggak perlu lookup ke data row sama sekali.

-- Query ini cuma butuh kolom: status, order_date, total
SELECT order_date, total FROM orders WHERE status = 'pending';

-- Bikin covering index yang mencakup semua kolom di SELECT
ALTER TABLE orders ADD INDEX idx_status_date_total (status, order_date, total);

-- Di EXPLAIN, kolom Extra akan muncul "Using index"

5. Partial Index (Prefix Index)

Kalau kolomnya panjang (misal VARCHAR(255) buat URL), kamu nggak perlu index keseluruhan. Cukup beberapa karakter pertama aja.

-- Index cuma 50 karakter pertama dari URL
ALTER TABLE articles ADD INDEX idx_url_prefix (url(50));

-- Cari prefix length yang optimal
SELECT 
    COUNT(DISTINCT LEFT(url, 20)) AS d20,
    COUNT(DISTINCT LEFT(url, 40)) AS d40,
    COUNT(DISTINCT LEFT(url, 50)) AS d50,
    COUNT(DISTINCT url) AS d_full
FROM articles;

Kalau d50 sudah mendekati d_full, berarti prefix 50 karakter sudah cukup. Hemat space di index.

Aturan Leftmost Prefix - Ini yang Sering Bikin Bingung

Composite index (a, b, c) bisa dipakai oleh query yang filter:

• WHERE a = 1 - pakai index (kolom pertama)
• WHERE a = 1 AND b = 2 - pakai index (dua kolom pertama)
• WHERE a = 1 AND b = 2 AND c = 3 - pakai index (semua kolom)
• WHERE b = 2 - NGGAK pakai index (skip kolom pertama)
• WHERE b = 2 AND c = 3 - NGGAK pakai index
• WHERE a = 1 AND c = 3 - cuma pakai bagian a aja

Makanya, bikin composite index itu harus pikirin query pattern yang paling sering dipakai. Jangan asal nambah kolom ke index.

Teknik Optimasi Query dengan EXPLAIN

EXPLAIN itu sahabat terdekat kamu kalau soal query optimization. Setiap kali query lemot, langsung pakai EXPLAIN.

EXPLAIN FORMAT=JSON
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed'
AND o.order_date BETWEEN '2026-01-01' AND '2026-06-01'
ORDER BY o.order_date DESC
LIMIT 50;

Yang perlu dicek di hasil EXPLAIN:

• type: system > const > eq_ref > ref > range > index > ALL (dari terbaik ke terburuk)
• key: index mana yang dipakai. NULL berarti nggak pakai index
• rows: estimasi row yang di-scan. Makin kecil makin bagus
• Extra: "Using index" bagus (covering index), "Using filesort" jelek, "Using temporary" jelek
• filtered: persentase row yang lolos filter. 100% berarti semua row berguna

Anti-Pattern: Index yang Nggak Berguna

Nambah index nggak selalu bikin cepat. Kadang malah bikin lambat karena MySQL harus update index setiap kali INSERT/UPDATE/DELETE. Hindari ini:

-- JELEK: Index pada kolom dengan sedikit nilai unik (low cardinality)
ALTER TABLE users ADD INDEX idx_gender (gender); -- cuma 'M' dan 'F'
-- MySQL lebih milih full table scan daripada pakai index ini

-- JELEK: Terlalu banyak index di tabel yang sering di-write
-- Setiap INSERT/UPDATE harus update semua index
-- Kalau tabel 90% read, boleh banyak index. Kalau 50:50, hati-hati.

-- JELEK: Index yang redundan
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
ALTER TABLE orders ADD INDEX idx_user_id_date (user_id, order_date);
-- Index pertama redundan karena sudah tercakup di index kedua

Cek index yang nggak pernah dipakai:

-- Lihat index usage statistics
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'nama_database'
AND index_name IS NOT NULL
ORDER BY count_read ASC;

Index yang count_read = 0 tapi count_write tinggi = kandidat buat di-drop.

Index untuk JOIN dan Subquery

Kalau tabel kamu sering di-JOIN, kolom foreign key WAJIB di-index. Ini sering banget kelewat.

-- Tabel orders dengan foreign key ke users
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    user_id INT NOT NULL,
    total DECIMAL(10,2),
    status VARCHAR(20),
    order_date DATETIME,
    PRIMARY KEY (id),
    INDEX idx_user_id (user_id),        -- WAJIB buat JOIN
    INDEX idx_status_date (status, order_date)
) ENGINE=InnoDB;

-- Query JOIN ini sekarang cepat
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_date > '2026-01-01'
GROUP BY u.id;

Tanpa index di user_id, MySQL harus full table scan di tabel orders buat setiap row di tabel users. Kalau tabel users 10.000 row dan orders 1 juta row, itu 10.000 full table scan.

Partitioning vs Indexing

Untuk tabel sangat besar (50 juta+ row), kadang index aja nggak cukup. Partitioning bisa jadi solusi tambahan:

-- Partition by range pada order_date
ALTER TABLE orders PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Query filter by date sekarang cuma scan partition yang relevan
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31';

Partitioning + index di setiap partition = combo yang sangat powerful buat tabel historis besar.

Monitoring dan Maintenance Index

Index butuh maintenance. Seiring waktu, B-Tree bisa jadi fragmented dan performanya menurun.

-- Cek fragmentasi tabel
SELECT 
    table_name,
    data_free,
    data_length,
    ROUND(data_free / data_length * 100, 2) as frag_pct
FROM information_schema.tables
WHERE table_schema = 'nama_database'
AND data_free > 0
ORDER BY frag_pct DESC;

-- Rebuild index (reorganize B-Tree)
ALTER TABLE orders ENGINE=InnoDB;  -- rebuild tabel + semua index
-- atau
OPTIMIZE TABLE orders;  -- lebih eksplisit

Jadwalkan ini di low-traffic hours karena prosesnya bisa locking tabel besar. Di MySQL 8.0+, InnoDB mendukung instant DDL buat beberapa operasi ALTER TABLE, jadi nggak selalu perlu downtime.

Tools yang Membantu

• Percona Toolkit pt-query-digest: analisis slow query log, kasih ranking query paling lemot
• Percona Toolkit pt-index-usage: cek index yang nggak pernah dipakai dari slow query log
• MySQL Workbench: visual EXPLAIN yang lebih gampang dibaca
• EverSQL: online tool buat suggest index otomatis dari query

# Install Percona Toolkit di Ubuntu
sudo apt install percona-toolkit

# Analisis slow query log
pt-query-digest /var/log/mysql/mysql-slow.log > /tmp/query_report.txt

# Lihat top 10 query paling lambat
head -100 /tmp/query_report.txt

Checklist Sebelum Deploy ke Production

1. Cek semua query WHERE/JOIN sudah punya index yang sesuai
2. Jalankan EXPLAIN di setiap query critical - pastikan nggak ada full table scan
3. Drop index yang redundant - hemat space dan write performance
4. Monitor index usage setelah deploy - pastikan semua index terpakai
5. Setup slow query log - biar ketahuan kalau ada query yang tiba-tiba lemot

-- Enable slow query log di my.cnf
-- [mysqld]
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/mysql-slow.log
-- long_query_time = 1

-- Atau set langsung tanpa restart
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

MySQL indexing itu investasi waktu yang sangat worth it. Sekali paham konsepnya, kamu bisa bikin query yang tadinya 10 detik jadi 10 milidetik. Dan yang lebih penting, user kamu nggak akan komplain website lemot lagi.