Di episode kedua, query-nya tidak bersalah. Di CLI cuma 78 milidetik, sementara endpoint-nya makan 15 detik. Kali ini kebalikannya: query-nya memang bersalah. Bukan karena rumit, tapi karena satu pembungkus fungsi membuatnya mengabaikan index yang sudah disiapkan.
Index-nya Ada, Malah Tiga #
Korbannya: endpoint hitungan alert per device. Satu tabel alert berisi puluhan juta baris,1 dan di atasnya ada index komposit: (tanggal, device, tipe alert). Kalau kamu membaca kebutuhan endpoint-nya, “ambil jumlah alert per device per tipe, dalam rentang tanggal”, index itu persis seperti dibuat untuk query ini. Seseorang di masa lalu memahami masalahnya dengan benar.
Bagian favorit saya baru ketahuan saat menulis episode ini: di skema database, index yang sama ternyata punya tiga salinan. Namanya beda, isinya sama. Sepertinya ada tiga orang dari tiga masa berbeda yang menambahkannya tanpa mengecek skema dulu hahaha. Jadi index-nya bukan cuma tersedia, malah kebanyakan.
Namun query-nya tetap tidak memakainya. Full table scan di puluhan juta baris membuat endpoint merayap sampai ±10 detik. Index yang tepat sudah tersedia, tetapi query ini tidak bisa menggunakannya.
Buku Telepon yang Harus Dibaca dari Awal #
Istilah teknisnya non-sargable, alias query yang membuat index tidak bisa dipakai. Bayangkan index sebagai buku telepon yang disusun alfabetis.
Tanya buku telepon: “cari semua nama yang dimulai huruf B”, dia langsung buka halaman B, selesai sepersekian detik. Sekarang tanya: “cari semua nama yang kalau huruf-hurufnya diacak ulang, dimulai huruf B”, bukunya cuma bisa baca dari halaman satu sampai tamat, karena urutan alfabetisnya tidak lagi membantu.
WHERE DATE(kolom) >= DATE(?) itu persis pertanyaan kedua: fungsi DATE() dipanggil pada kolom, artinya setiap baris di tabel puluhan juta baris harus diubah bentuknya dulu, baru dibandingkan. Index yang tersusun rapi tidak berguna untuk nilai yang belum lahir dari fungsinya. Database-nya tidak bodoh; dia cuma tidak punya pilihan.
Pindahkan Fungsinya, Bukan Kolomnya #
Perbaikannya bukan tuning eksotis, cuma memindahkan fungsi dari sisi kolom ke sisi parameter:2
-- sebelum: index didiamkan
WHERE DATE(a.dt) >= DATE(?) AND DATE(a.dt) <= DATE(?)
-- sesudah: index kembali dipakai
WHERE a.dt >= ? AND a.dt <= ?
Konsekuensinya, parameter harus dikirim lengkap dengan jamnya. Di kode saya, batas waktunya memang sudah disiapkan di usecase, dari 00:00:00 sampai 23:59:59. Hasilnya tetap sama, semua baris di tanggal yang dimaksud ikut terambil.
Hasil akhirnya: dari ±10 detik full scan menjadi pencarian index yang sekejap mata. Deploy, dan endpoint itu pulih tanpa satu baris business logic disentuh.
COLLATE Ternyata Bukan Pelakunya #
Supaya adil, cerita episode ini punya subplot. Di penyelidikan yang sama, ada satu join yang tampak sangat mencurigakan: perbandingan antar tabel dengan penanda collate eksplisit di tengahnya. Wajahnya persis tersangka klasik query lambat: “beda collation bikin join tidak memakai index”.
Setelah dicek, kedua sisi yang di-join memakai collation yang sama. Penandanya redundan, bukan penyebab masalah. Tersangka dibebaskan.
Dua episode ini arahnya berlawanan. Di episode dua, EXPLAIN menunjukkan CTE bukan masalahnya. Kali ini, pemeriksaan membuktikan DATE() memang pelakunya. Intuisi boleh menunjuk tersangka, tapi tetap perlu diperiksa dulu.
Ada Tiga Lagi yang Belum Saya Bereskan #
Setelah memperbaiki satu kasus DATE(), saya mencari pola yang sama di codebase. Ketemu tiga join lain dengan bentuk identik di modul bahan bakar. DATE() dipanggil pada kolom di sisi kiri dan kanan join.
Statusnya saat episode ini ditulis: belum saya perbaiki. Endpoint-nya belum pernah dilaporkan lambat, jadi belum mendapat jatah waktu perbaikan. Polanya sudah masuk daftar; kalau nanti dikerjakan, EXPLAIN akan ikut memeriksanya.
Migrasi dengan paritas memang meninggalkan utang kecil seperti ini. Buat saya, yang penting utangnya tercatat. Jangan sampai pura-pura lupa, hahaha.
Catatan Buat Saya #
Kalau saya tarik benang merahnya, satu fungsi kecil di sisi kolom bisa bikin index yang sudah ada jadi tidak berguna. Jadi setelah menemukan satu pola, saya cari juga saudara-saudaranya. Kali ini ketemu tiga, dan saya catat dulu sebagai utang. Bukan berarti dibiarkan selamanya, ya. Semoga saya ingat pas ada waktu mengerjakannya hehe.
Di episode berikutnya saya pindah dari satu query ke bentuk kotanya: bagaimana monolith puluhan ribu baris dirombak jadi 14 modul domain, sementara pesawatnya tetap terbang.
Sekian. Salam.