Index Database dalam Praktik: Kenapa Query Jadi Lambat dan Index Mana yang Memperbaikinya

Sebagian besar aplikasi lambat yang diminta untuk kami periksa sebenarnya tidak butuh server yang lebih besar. Yang dibutuhkan adalah index yang tepat. Query yang langsung selesai saat development dengan seribu baris data bisa makan waktu beberapa detik di production ketika tabelnya sudah berisi jutaan baris. Penyebabnya hampir selalu sama: database harus membaca seluruh tabel hanya untuk menemukan segelintir baris. Untuk menunjukkan apa yang benar-benar membantu, kami mengisi PostgreSQL 18 dengan dua juta data pesanan lalu mengukur query yang umum dipakai sebelum dan sesudah berbagai index ditambahkan. Sebagian hasilnya sesuai dugaan. Beberapa justru tidak.

Apa yang Dilakukan Index

Tanpa index, database menjawab query dengan membaca setiap baris di tabel lalu memeriksanya satu per satu. Ini disebut sequential scan atau full table scan. Index adalah struktur terpisah yang sudah terurut, biasanya berbentuk B-tree, yang menyimpan nilai dari satu atau beberapa kolom beserta penunjuk ke barisnya. Karena terurut, database bisa langsung melompat ke nilai yang dicari, sama seperti mencari nama di buku telepon tanpa harus membacanya dari halaman pertama.

Urutan itulah kunci dari semua pembahasan berikutnya. Index membantu ketika query bisa memanfaatkan urutannya: pencocokan nilai yang persis, rentang nilai, atau pengurutan dengan arah yang sama. Ketika query tidak bisa memanfaatkan urutan itu, index hanya diam, memakan ruang penyimpanan, dan memperlambat setiap insert dan update tanpa mempercepat pembacaan sama sekali.

Cara Kami Menguji

Kami memakai PostgreSQL 18.4 di Docker dengan tabel orders berisi dua juta baris (256 MB) dan tabel customers berisi seratus ribu baris. Pesanan tersebar dalam rentang dua tahun dan 100.000 pelanggan. Kolom status dibuat timpang seperti di toko sungguhan: 90 persen selesai, 6 persen dibatalkan, dan hanya setengah persen yang masih pending. Setiap query dijalankan sembilan kali dengan cache yang sudah hangat, dan tabel di bawah menampilkan nilai tengah waktu eksekusi dari EXPLAIN ANALYZE. Di server yang sibuk dengan cache dingin, selisihnya biasanya justru lebih besar.

Selisih 40 ms dan 0,2 ms memang terlihat kecil untuk satu request. Tapi kalikan dengan setiap kunjungan halaman, setiap pemanggilan API, dan setiap background job, maka angka itu yang menentukan apakah CPU server database berada di 10 persen atau 100 persen. Bagian yang lebih menarik adalah bagaimana kami sampai ke index yang tepat, karena percobaan pertama yang paling jelas sering kali tidak berhasil.

Pelajaran 1: Index Kolom Filter dan Kolom Urutan Sekaligus

Untuk pesanan batal terbaru, query memfilter berdasarkan status lalu mengurutkan berdasarkan created_at. Index pada kolom status saja sama sekali tidak berpengaruh: 56,0 ms, dibanding 55,6 ms tanpa index apa pun. Pesanan batal berjumlah 120.000 baris, jadi database tetap harus mengambil semuanya lalu mengurutkannya untuk menemukan dua puluh yang terbaru. Index pada status dan created_at sekaligus memberikan jawaban dalam 0,2 ms, karena baris-barisnya sudah tersimpan dalam urutan yang benar di dalam index dan database tinggal membaca dua puluh yang pertama.

Untuk riwayat pesanan pelanggan, efeknya lebih kecil tapi polanya sama. Index pada customer_id saja menurunkan query ke 0,24 ms, dan menambahkan created_at ke index itu menurunkannya ke 0,19 ms sekaligus menghilangkan langkah pengurutan. Untuk pelanggan yang punya ribuan pesanan, langkah pengurutan itulah yang menjadi bagian paling mahal.

Pelajaran 2: Urutan Kolom di Composite Index Itu Penting

Composite index diurutkan berdasarkan kolom pertamanya, lalu kolom kedua di dalam setiap nilai kolom pertama, seperti buku telepon yang diurutkan berdasarkan nama keluarga lalu nama depan. Aturan umumnya: taruh kolom yang dibandingkan dengan sama dengan di depan, dan kolom untuk rentang atau pengurutan di belakang. Query yang hanya memfilter kolom kedua biasanya tidak bisa memakai index tersebut. Ketika yang tersedia hanya index (customer_id, created_at), menghitung pesanan pada satu hari kembali menjadi full scan dengan waktu 45,5 ms. Index terpisah pada created_at menjawabnya dalam 0,36 ms.

Ada catatan untuk versi yang lebih baru. PostgreSQL 18 memperkenalkan skip scan, dan MySQL sudah punya fitur serupa sejak 8.0.13. Ketika kolom pertama hanya punya sedikit nilai berbeda, database bisa melompati nilai-nilai itu satu per satu. Dalam pengujian kami, index (status, created_at) yang hanya berisi lima jenis status bisa menjawab hitungan satu hari dalam 0,49 ms, padahal query-nya sama sekali tidak menyebut status. Dengan 100.000 ID pelanggan yang berbeda sebagai kolom pertama, trik ini sudah tidak menguntungkan. Anggap skip scan sebagai bonus, bukan strategi desain.

Pelajaran 3: Fungsi pada Kolom Membuat Index Tidak Terpakai

Ini hasil yang paling mencolok. Menghitung pesanan pada satu hari dengan kondisi rentang, yaitu created_at dari tengah malam sampai tengah malam berikutnya, hanya butuh 0,36 ms dengan index pada created_at. Kondisi yang sama ditulis sebagai date(created_at) = '2026-03-15' butuh 175,9 ms dengan index yang persis sama, hampir empat kali lebih lambat dibanding full scan biasa tanpa index sama sekali. Database tidak bisa mencari hasil sebuah fungsi di dalam daftar timestamp yang terurut, jadi ia membaca seluruh index dan menghitung tanggal untuk setiap entri.

Hal yang sama terjadi pada pencarian email. Query dengan lower(email) butuh 24,7 ms tanpa index dan 24,8 ms dengan index biasa pada email, yang ternyata diabaikan oleh database. Expression index pada lower(email) menurunkannya ke 0,07 ms. Jebakan yang sama juga muncul di pola-pola yang sering dipakai:

• Fungsi tanggal di filter, seperti YEAR(created_at) = 2026 atau DATE(created_at) = ?. whereDate() dan whereYear() di Laravel menghasilkan SQL persis seperti ini. Gunakan whereBetween() dengan waktu awal dan akhir sebagai gantinya.
• Membandingkan kolom teks dengan angka, misalnya kolom nomor HP atau NIK yang disimpan sebagai VARCHAR lalu dibandingkan tanpa tanda kutip. MySQL akan mengonversi setiap baris sebelum membandingkan, dan index-nya dilewati.
• Pencarian dengan wildcard di depan, seperti LIKE '%budi%'. B-tree hanya bisa mencari dari awal sebuah nilai. Untuk mencari di tengah teks, gunakan full-text search, atau extension pg_trgm di PostgreSQL.
• Pencarian tanpa membedakan huruf besar-kecil dengan lower() atau upper(). Simpan nilainya dalam bentuk yang sudah dinormalisasi, atau buat expression index dengan ekspresi yang sama persis dengan yang dipakai query. MySQL mendukungnya lewat functional index sejak 8.0.13.

Pelajaran 4: Partial Index untuk Baris yang Benar-Benar Dicari

Dashboard operasional jarang melihat pesanan yang sudah selesai. Yang dilihat adalah sekumpulan kecil pesanan yang masih menunggu diproses. Di PostgreSQL, Anda bisa membuat index hanya untuk baris-baris itu dengan sebuah kondisi, misalnya index pada created_at dengan syarat status = 'pending'. Dalam pengujian kami, kecepatannya sama persis dengan index penuh pada (status, created_at), 0,36 ms berbanding 0,37 ms, tetapi ukurannya hanya 232 kB, bukan 77 MB, karena isinya hanya sekitar sepuluh ribu baris. Ukurannya juga tetap kecil meskipun tabel terus bertambah, karena pesanan otomatis keluar dari index begitu statusnya berubah. MySQL tidak mendukung partial index, jadi di sana pilihannya tetap composite index penuh.

Harganya: Setiap Index Memperlambat Proses Tulis

Index tidak gratis. Setiap index harus ikut diperbarui pada setiap insert, setiap update yang menyentuh kolom yang di-index, dan setiap delete. Kami memasukkan 500.000 pesanan yang sama ke dua tabel identik, satu hanya dengan primary key dan satu lagi dengan lima index tambahan:

Proses tulis menjadi sekitar delapan kali lebih lambat dan ruang penyimpanan naik lebih dari dua kali lipat. Untuk tabel yang dibaca seribu kali untuk setiap satu kali tulis, pertukaran ini jelas sepadan. Untuk tabel log, tabel impor, atau tabel apa pun yang terus-menerus menerima insert, setiap index tambahan harus punya alasan yang jelas. Index yang tidak pernah dipakai hanyalah beban, dan jumlahnya diam-diam terus bertambah di aplikasi yang sudah berjalan bertahun-tahun.

Cara Menemukan Index yang Dibutuhkan

1. 1Cari query yang paling mahal secara total, bukan hanya satu query yang paling lambat. Di PostgreSQL, aktifkan extension pg_stat_statements lalu urutkan berdasarkan total waktu eksekusi. Di MySQL, gunakan slow query log atau ringkasan statement di performance_schema.
2. 2Jalankan EXPLAIN ANALYZE untuk setiap query tersebut, dengan parameter yang realistis, di salinan data production. Perhatikan Seq Scan pada tabel besar, langkah Sort pada banyak baris, atau selisih besar antara perkiraan dan jumlah baris yang sebenarnya. EXPLAIN ANALYZE benar-benar menjalankan query, jadi bungkus UPDATE dan DELETE dalam transaksi lalu lakukan rollback.
3. 3Rancang index berdasarkan keseluruhan query: kolom yang dibandingkan dengan sama dengan di depan, lalu kolom rentang atau pengurutan. Sebelum menambah index baru, cek dulu apakah index yang sudah ada bisa diperluas.
4. 4Tambahkan ke production tanpa mengunci tabel. Di PostgreSQL gunakan CREATE INDEX CONCURRENTLY, yang tidak memblokir proses tulis tetapi tidak bisa dijalankan di dalam transaksi, sehingga migration-nya harus dijalankan di luar transaksi. Di MySQL dengan InnoDB, ALTER TABLE ... ADD INDEX dengan ALGORITHM=INPLACE dan LOCK=NONE berjalan secara online.
5. 5Ukur lagi, lalu hapus yang tidak terpakai. Di PostgreSQL, pg_stat_user_indexes menampilkan index dengan idx_scan bernilai nol; di MySQL, view sys.schema_unused_indexes menampilkan daftarnya. Periksa statistiknya selama satu siklus bisnis penuh, termasuk laporan akhir bulan, sebelum menghapus apa pun.

Indexing adalah salah satu cara paling murah untuk meningkatkan performa: tanpa hardware baru, tanpa menulis ulang aplikasi, sering kali cukup satu baris di migration. Hasilnya paling baik kalau dijadikan kebiasaan, bukan tindakan darurat: cek query plan setiap kali sebuah fitur menambah query baru, dan periksa query paling lambat setiap beberapa bulan seiring data bertambah. Jika aplikasi Anda mulai terasa lambat dan Anda belum tahu harus mulai dari mana, tim kami bisa membantu memeriksa database beserta query di baliknya.