Membaca Query Plan Database

Salah satu skill yang menurut saya paling underrated di kalangan developer backend adalah membaca query plan. Banyak yang sudah terbiasa pakai Eloquent atau query builder bertahun-tahun, tapi begitu aplikasi mulai lambat, larinya ke "tambah index aja" tanpa tahu index mana yang sebenarnya dibutuhkan. Padahal database sudah kasih tahu kita persis apa yang terjadi di balik layar lewat EXPLAIN — tinggal kita mau baca atau tidak.

Di tulisan ini saya mau bahas dasar-dasarnya saja: bagaimana cara paling praktis mengenali full table scan dan index yang tidak terpakai dari output EXPLAIN, tanpa masuk terlalu dalam ke cost-based optimizer atau statistik internal database yang levelnya sudah lebih advanced.

Kenapa query plan itu penting

Query yang secara sintaks benar belum tentu efisien. Database punya query planner/optimizer yang menentukan cara paling "murah" untuk mengeksekusi query — apakah pakai index, atau justru menyisir seluruh tabel baris per baris. Masalahnya, keputusan itu bisa berubah seiring waktu: jumlah data bertambah, distribusi data berubah, atau statistik tabel jadi usang. Query yang dulu cepat bisa perlahan jadi lambat tanpa ada perubahan kode sama sekali.

Di sinilah EXPLAIN (atau EXPLAIN ANALYZE kalau mau lihat waktu eksekusi aktual, bukan cuma estimasi) jadi alat wajib. Sebagai ilustrasi, kalau ada query laporan yang biasanya cepat tapi tiba-tiba lambat setelah tabel membesar, langkah pertama saya selalu EXPLAIN dulu sebelum menebak-nebak.

Mengenali full table scan

Full table scan artinya database membaca seluruh baris di tabel untuk mencari data yang cocok, bukan langsung "melompat" ke baris yang relevan lewat index. Untuk query kecil ini tidak masalah, tapi untuk tabel dengan jutaan baris, ini biasanya jadi biang lambat.

Di MySQL, tandanya ada di kolom type pada output EXPLAIN: kalau nilainya ALL, itu full table scan. Di PostgreSQL, cari node bernama Seq Scan di output EXPLAIN ANALYZE. Contoh sederhana query yang rawan full scan:

SELECT * FROM visits WHERE patient_name LIKE '%budi%';

Pola LIKE dengan wildcard di depan seperti ini hampir selalu memaksa full scan, karena index B-tree biasa tidak bisa dipakai untuk mencari "mengandung" — beda dengan LIKE 'budi%' yang masih bisa memanfaatkan index kalau kolomnya ter-index.

Full scan bukan selalu buruk — untuk tabel kecil, atau query yang memang butuh sebagian besar isi tabel, planner justru sengaja memilih full scan karena lebih murah dibanding overhead random access lewat index. Yang perlu diwaspadai adalah full scan pada tabel besar untuk query yang seharusnya hanya butuh sedikit baris.

Mengenali index yang tidak terpakai

Kasus lain yang sering luput: sudah bikin index, tapi ternyata tidak dipakai sama sekali oleh query. Beberapa penyebab umum yang biasa saya temui:

• Menggunakan fungsi di kolom yang di-index, misalnya WHERE LOWER(email) = 'x' padahal index-nya di kolom email apa adanya.
• Tipe data kolom filter tidak cocok dengan tipe data di index, sehingga database harus melakukan konversi implisit.
• Index komposit tapi kolom pertamanya tidak dipakai di klausa WHERE — index gabungan umumnya hanya efektif kalau diakses berurutan dari kolom pertama.
• Selektivitas index rendah, misalnya index pada kolom status yang hanya punya dua atau tiga nilai berbeda — planner sering menilai full scan lebih murah daripada lompat-lompat lewat index untuk kolom seperti ini.

Cara mengecek: bandingkan kolom key di EXPLAIN MySQL (isinya nama index yang dipakai, atau NULL kalau tidak ada yang dipakai) dengan index yang sudah dibuat lewat migration. Di PostgreSQL, perhatikan apakah muncul node Index Scan/Index Only Scan atau justru Seq Scan padahal ada index yang relevan.

Kalau menemukan index tidak terpakai, jangan buru-buru hapus atau buru-buru tambah index baru. Cek dulu apakah query-nya yang perlu ditulis ulang, atau memang index-nya perlu disesuaikan urutan kolomnya.

Takeaway

Intinya sederhana: sebelum menyimpulkan "query ini lambat, pasti kurang index", biasakan jalankan EXPLAIN dulu. Cari ALL/Seq Scan untuk mendeteksi full table scan, dan cek kolom key atau node Index Scan untuk memastikan index yang sudah dibuat benar-benar terpakai. Kebiasaan kecil ini jauh lebih murah dibanding menambah index sembarangan yang justru memperlambat proses write di aplikasi.