Index MySQL Bukan Solusi Ajaib: Cara Memilih Index tanpa Membebani Aplikasi

Saat sebuah query MySQL mulai lambat, menambahkan index sering menjadi saran pertama. Saran itu tidak salah, tetapi index bukan tombol ajaib yang otomatis menyelesaikan semua masalah database. Index yang tepat memang bisa mengurangi pekerjaan MySQL secara drastis. Sebaliknya, index yang tidak diperlukan dapat memperbesar ukuran database dan membuat proses insert, update, serta delete menjadi lebih berat.

Karena itu, menambahkan index sebaiknya diperlakukan seperti mengatur rak di gudang. Rak yang ditempatkan dengan benar membantu menemukan barang dengan cepat. Namun, terlalu banyak rak juga membuat gudang sempit, mahal, dan menyulitkan saat barang harus dipindahkan.

Apa sebenarnya fungsi index?

Tanpa index, database bisa saja harus memeriksa baris satu per satu untuk menemukan data yang diminta. Proses ini disebut full table scan. Untuk tabel kecil, dampaknya mungkin tidak terasa. Namun, ketika tabel berisi ratusan ribu atau jutaan baris, pemeriksaan berulang dapat memakan waktu dan sumber daya.

Index menyimpan struktur tambahan yang membantu database menemukan baris tertentu tanpa membaca seluruh isi tabel. Contoh sederhana, jika aplikasi sering mencari pengguna berdasarkan alamat email, kolom email dapat diberi index agar pencarian tidak perlu memeriksa semua pengguna.

CREATE INDEX idx_users_email ON users (email);

Perlu dicatat, index bukan salinan tabel yang lengkap. Ia adalah struktur pencarian tambahan yang harus dirawat oleh database setiap kali data berubah.

Kapan sebuah kolom layak diberi index?

Jangan mulai dari pertanyaan “kolom mana yang bisa diberi index?”, tetapi dari “query mana yang paling sering atau paling mahal dijalankan?”. Index seharusnya melayani pola akses data yang nyata.

Kolom yang sering digunakan untuk pencarian

Kolom yang sering muncul dalam kondisi WHERE dapat menjadi kandidat index. Misalnya, aplikasi toko online sering mencari pesanan berdasarkan user_id atau status berdasarkan status.

SELECT * FROM orders
WHERE user_id = 125;

Untuk query seperti itu, index pada user_id kemungkinan lebih berguna daripada index pada kolom yang hampir tidak pernah digunakan dalam pencarian.

Kolom yang digunakan untuk pengurutan

Index juga dapat membantu query yang sering menggunakan ORDER BY, terutama jika digabungkan dengan kondisi pencarian. Misalnya, halaman admin menampilkan pesanan terbaru milik pengguna tertentu.

SELECT id, total, created_at
FROM orders
WHERE user_id = 125
ORDER BY created_at DESC
LIMIT 20;

Untuk pola tersebut, index gabungan pada (user_id, created_at) bisa lebih relevan daripada dua index terpisah.

Kolom yang memiliki nilai cukup beragam

Index biasanya lebih berguna pada kolom dengan banyak nilai berbeda. Kolom seperti email, invoice_number, atau product_id umumnya lebih selektif dibanding kolom seperti is_active yang hanya berisi dua kemungkinan nilai.

Namun, ini bukan aturan mutlak. Index pada kolom dengan sedikit variasi tetap dapat membantu dalam kombinasi tertentu, terutama jika tabel sangat besar atau query memiliki kondisi tambahan.

Memahami index gabungan

Index gabungan adalah index yang mencakup lebih dari satu kolom.

CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at);

Urutan kolom sangat penting. Index (user_id, created_at) terutama membantu query yang memulai penyaringan dari user_id. Ia tidak selalu efektif untuk query yang hanya mencari berdasarkan created_at.

Bayangkan daftar alamat yang disusun berdasarkan provinsi lalu kota. Daftar tersebut sangat membantu ketika kita tahu provinsinya. Namun, jika hanya mengetahui nama kota tanpa provinsi, pencariannya belum tentu efisien.

Karena itu, sebelum membuat index gabungan, tuliskan beberapa query yang benar-benar digunakan aplikasi. Periksa kolom yang selalu muncul dalam filter, kemudian pertimbangkan kolom pengurutan atau rentang setelahnya.

Gunakan EXPLAIN, jangan menebak

MySQL menyediakan EXPLAIN untuk melihat bagaimana sebuah query akan dijalankan. Dengan perintah ini, developer dapat mengetahui apakah database menggunakan index, berapa banyak baris yang diperkirakan dibaca, dan strategi apa yang dipilih.

EXPLAIN
SELECT id, total, created_at
FROM orders
WHERE user_id = 125
ORDER BY created_at DESC
LIMIT 20;

Perhatikan beberapa hal berikut:

• key: index yang dipilih oleh optimizer, jika ada.
• possible_keys: index yang mungkin digunakan.
• rows: perkiraan jumlah baris yang perlu diperiksa.
• type: gambaran metode akses data, dari yang relatif efisien hingga yang perlu dicermati.

Hasil EXPLAIN bukan vonis tunggal. Query dengan jumlah baris kecil mungkin tetap cepat meskipun tidak memakai index. Sebaliknya, query yang tampak sederhana dapat menjadi masalah ketika datanya tumbuh. Uji dengan data yang mendekati kondisi produksi, bukan hanya dengan sepuluh baris di komputer lokal.

Risiko terlalu banyak index

Setiap index membutuhkan ruang penyimpanan tambahan. Lebih dari itu, ketika sebuah baris ditambahkan atau diubah, MySQL perlu memperbarui index yang berkaitan. Jika tabel memiliki banyak index yang tidak diperlukan, operasi tulis dapat melambat.

Masalah lain adalah index yang tumpang tindih. Misalnya, sebuah tabel memiliki index pada (user_id) dan (user_id, created_at). Keduanya mungkin tidak sama-sama dibutuhkan, tergantung query yang berjalan. Menghapus index bukan berarti harus dilakukan sembarangan, tetapi index lama perlu dievaluasi setelah pola query berubah.

Index juga tidak dapat menyelamatkan query yang ditulis dengan buruk. Fungsi tertentu pada kolom, pencarian dengan pola yang tidak cocok, atau kondisi yang memaksa database membaca terlalu banyak data tetap dapat mengurangi manfaat index.

Yang bisa dilakukan sekarang

1. Catat query yang paling sering dipanggil dan query yang paling lama selesai.
2. Jalankan EXPLAIN pada query tersebut.
3. Periksa apakah kolom filter, pengurutan, dan relasi sudah memiliki index yang sesuai.
4. Uji index baru pada lingkungan pengujian dengan jumlah data yang realistis.
5. Bandingkan waktu baca dan dampaknya terhadap operasi tulis.
6. Dokumentasikan alasan setiap index dibuat agar mudah ditinjau saat struktur aplikasi berubah.

Praktik yang sehat bukanlah memberi index pada semua kolom, melainkan memastikan setiap index punya alasan yang jelas. Database yang baik tidak hanya cepat ketika membaca data, tetapi juga tetap masuk akal saat menerima perubahan setiap hari.

Intinya: pilih index berdasarkan query nyata, pahami urutan kolom pada index gabungan, dan selalu verifikasi dengan pengukuran. Dengan pendekatan ini, index menjadi alat untuk mengarahkan database bekerja lebih efisien, bukan sekadar tambalan ketika aplikasi mulai terasa lambat.

– Rio Yotto @rioyotto