Mengira Index Pasti Dipakai, Padahal DATE() di WHERE Bikin MySQL Full Scan

Kolom created_at sudah punya index, tapi query laporan harian tetap butuh beberapa detik dan CPU MySQL naik setiap kali halaman itu dibuka. Banyak yang lalu menyimpulkan index-nya rusak atau server-nya kurang besar. Sering kali penyebabnya cuma satu bagian di klausa WHERE: kolom dibungkus fungsi seperti DATE() atau LOWER(), sehingga optimizer tidak bisa memakai index tersebut. Tulisan ini disusun dengan format post-mortem: dampak awal, jalur investigasi, titik temuan, mitigasi sementara, lalu perbaikan permanen. Skenarionya bukan satu kejadian tertentu, melainkan gabungan pola yang umum muncul di server hosting yang menjalankan MySQL atau MariaDB.

Dampak awal yang terlihat dari sisi hosting

Gejalanya jarang terlihat seperti masalah database. Pemilik situs biasanya melapor bahwa website lambat di jam tertentu, dashboard admin sering timeout, atau load server naik padahal traffic tidak berubah banyak. Kalau dicek, yang sibuk adalah proses mysqld, bukan PHP. Ada satu ciri yang membedakan masalah ini dari masalah kapasitas biasa: query-nya dulu cepat, lalu makin lambat seiring waktu. Saat tabel orders masih berisi beberapa ribu baris, membaca seluruh tabel terasa instan. Begitu isinya ratusan ribu atau jutaan baris, pekerjaan yang sama jadi berat karena biayanya tumbuh mengikuti ukuran tabel, bukan mengikuti jumlah data yang benar-benar dibutuhkan. Di meja support, keluhan seperti ini sering langsung dijawab dengan saran upgrade paket atau menaikkan RAM. Kadang itu memang membantu sedikit. Tapi kalau akar masalahnya full table scan, tambahan resource hanya menunda, karena tabelnya terus bertambah besar.

Jalur investigasi dari server sampai ke satu query

Urutan pengecekan yang biasanya paling cepat mengerucut:

Pastikan beban memang di database. Lihat proses dengan konsumsi CPU tertinggi, lalu jalankan SHOW FULL PROCESSLIST; di MySQL untuk melihat query yang sedang berjalan lama.
Aktifkan atau baca slow query log. Directive yang dipakai adalah slow_query_log dan long_query_time (ambang waktu dalam detik). Ada juga log_queries_not_using_indexes, tapi opsi ini bisa sangat ramai di server dengan banyak tabel kecil.
Ambil query yang paling sering muncul atau paling lama, lalu jalankan dengan EXPLAIN di depannya.

Langkah ketiga yang paling sering dilewati. Banyak orang berhenti di langkah kedua, melihat query-nya memakai kolom yang sudah ber-index, lalu menganggap index pasti dipakai. Padahal hanya EXPLAIN yang bisa menunjukkan rencana eksekusi yang benar-benar dipilih optimizer.

Titik temuan: EXPLAIN menunjukkan type ALL

Misalnya query yang muncul di slow log seperti ini:

SQL

EXPLAIN SELECT id, total
FROM orders
WHERE DATE(created_at) = '2026-10-01'\G

Akhiran \G membuat output tampil vertikal sehingga lebih mudah dibaca di terminal. Hasilnya kira-kira seperti ini (contoh format output, beberapa kolom dipotong):

TEXT

           id: 1
  select_type: SIMPLE
        table: orders
         type: ALL
possible_keys: NULL
          key: NULL
         rows: 1000000
        Extra: Using where

Empat kolom yang paling penting dibaca: type menunjukkan cara MySQL mengakses tabel. Nilai ALL berarti full table scan: semua baris dibaca satu per satu. Nilai index juga perlu dicurigai, karena artinya seluruh index dibaca dari awal sampai akhir. Yang kita harapkan untuk filter seperti ini adalah range, ref, atau const. possible_keys berisi index yang dianggap relevan oleh optimizer. Kalau isinya NULL padahal kolomnya jelas punya index, itu tanda kuat bahwa kondisi WHERE ditulis dengan cara yang membuat index tidak bisa dipertimbangkan sama sekali. key adalah index yang akhirnya dipakai. NULL berarti tidak ada index yang dipakai. rows adalah estimasi jumlah baris yang harus diperiksa. Angka ini tidak harus persis, tapi kalau mendekati total isi tabel padahal kita hanya butuh data satu hari, rencana eksekusinya jelas boros. Penyebabnya ada di cara index B-tree bekerja. Index pada created_at menyimpan nilai asli kolom itu dalam keadaan terurut, misalnya 2026-10-01 08:15:22. Index itu tidak menyimpan hasil DATE(created_at). Ketika kondisinya ditulis sebagai DATE(created_at) = '2026-10-01', MySQL harus menghitung DATE() untuk setiap baris dulu sebelum bisa membandingkan. Urutan di index tidak bisa dimanfaatkan untuk melompat langsung ke tanggal yang dicari. Pola yang sama berlaku untuk LOWER(email) = '[email protected]', YEAR(created_at) = 2026, DATE_FORMAT(...), atau ekspresi aritmatika seperti harga + 0 = 100. Aturannya sederhana: selama kolom di sisi kiri dibungkus fungsi atau ekspresi, index biasa pada kolom itu biasanya tidak terpakai. Kalau server memakai MySQL 8.0.18 ke atas, EXPLAIN ANALYZE bisa dipakai untuk melihat waktu eksekusi dan jumlah baris yang benar-benar dibaca, bukan sekadar estimasi. Perintah ini benar-benar menjalankan query, jadi hati-hati memakainya di jam sibuk. MariaDB punya pernyataan ANALYZE dengan tujuan serupa, tapi format outputnya berbeda.

Mitigasi sementara saat kode aplikasi belum bisa diubah

Di lingkungan hosting, query yang bermasalah sering berasal dari plugin, tema, atau aplikasi pihak ketiga yang tidak bisa langsung kita edit. Untuk kasus seperti ini, ada dua jalan sementara. Yang pertama adalah index atas ekspresi. MySQL 8.0.13 ke atas mendukung functional index, sehingga kita bisa membuat index langsung pada DATE(created_at). Di MariaDB, pendekatan yang biasa dipakai adalah generated column, yaitu kolom yang nilainya dihitung otomatis dari kolom lain, lalu kolom itu diberi index.

SQL

-- MySQL 8.0.13+: functional index (perhatikan tanda kurung ganda)
CREATE INDEX idx_orders_created_date ON orders ((DATE(created_at)));

-- Alternatif (umum di MariaDB): generated column + index
ALTER TABLE orders
  ADD COLUMN created_date DATE AS (DATE(created_at)) VIRTUAL,
  ADD INDEX idx_orders_created_date (created_date);

Perintah di atas mengubah struktur tabel. Pada tabel besar, prosesnya bisa memakan waktu dan menambah beban I/O. Jalankan di luar jam sibuk dan pastikan backup sudah ada sebelum mulai. Ada beberapa trade-off yang perlu diterima. Setiap index tambahan memperlambat INSERT dan UPDATE sedikit dan memakan ruang disk. Ekspresi di query juga harus cocok dengan definisi index. DATE(created_at) dan CAST(created_at AS DATE) mungkin terlihat setara bagi kita, tapi belum tentu dianggap sama oleh optimizer. Di MariaDB, apakah optimizer bisa otomatis memakai index generated column untuk query yang masih menulis DATE(created_at) tergantung versinya. Kalau tidak bisa, query-nya tetap harus diubah agar langsung memakai created_date. Setelah memasang index, selalu jalankan ulang EXPLAIN untuk memastikan key sudah terisi. Untuk kasus LOWER(email), cek collation kolomnya dulu. Kalau collation-nya berakhiran _ci (case-insensitive), perbandingan email = '[email protected]' sudah mengabaikan huruf besar dan kecil. Dalam kondisi itu, LOWER() tidak menambah apa pun selain mematikan index. Membuangnya sudah cukup, tanpa perlu index tambahan. Kalau beban sudah terlanjur berat dan perbaikan belum bisa dilakukan, cara darurat seperti cache di level aplikasi atau membatasi frekuensi halaman laporan bisa membantu meredam. Tapi itu hanya mengurangi seberapa sering full scan terjadi, bukan menghilangkannya. Buffer pool juga ikut terdampak, karena full scan menarik banyak halaman data ke memori dan bisa menggeser data lain yang lebih sering dipakai. Soal ukuran buffer pool di VPS yang juga menjalankan web sudah dibahas di artikel tentang innodb_buffer_pool_size.

Perbaikan permanen: ubah kondisi menjadi range

Solusi yang paling bersih adalah membiarkan kolom tetap polos dan memindahkan logikanya ke nilai pembanding. Kalimat “data tanggal 1 Oktober” sebenarnya sama dengan “semua waktu mulai 1 Oktober pukul 00:00 sampai sebelum 2 Oktober pukul 00:00”. Jadi kondisinya ditulis ulang menjadi: WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02' Dengan bentuk ini, MySQL bisa langsung mencari posisi awal di index lalu membaca terus sampai batas akhir. Jalankan EXPLAIN lagi. Yang diharapkan, type berubah menjadi range, key berisi nama index created_at, dan rows turun mendekati jumlah baris untuk hari itu saja. Beberapa detail kecil yang sering menjebak: Pakai batas atas dengan < hari berikutnya, bukan BETWEEN '2026-10-01 00:00:00' AND '2026-10-01 23:59:59'. Kalau kolom menyimpan fractional seconds, misalnya DATETIME(6), data pada detik terakhir seperti 23:59:59.500 bisa terlewat. Interval setengah terbuka (>= awal, < akhir) aman untuk presisi apa pun. Pola bulanan dan tahunan diperlakukan sama. YEAR(created_at) = 2026 AND MONTH(created_at) = 10 diganti menjadi created_at >= '2026-10-01' AND created_at < '2026-11-01'. Perhatikan zona waktu. Kalau kolomnya bertipe TIMESTAMP, MySQL mengonversi nilainya mengikuti time_zone sesi. Batas range yang dihitung di aplikasi harus memakai zona waktu yang sama dengan sesi database. Kalau tidak, laporan bisa bergeser beberapa jam dari yang diharapkan. Perbaikan ini sebaiknya dibarengi kebiasaan kecil di tim: setiap query baru yang menyentuh tabel besar dicek dengan EXPLAIN sebelum rilis, dan slow query log dipantau secara berkala, bukan hanya saat ada keluhan. Untuk plugin atau aplikasi pihak ketiga, kirim temuan EXPLAIN ke developernya. Bukti berupa type: ALL dan possible_keys: NULL jauh lebih mudah ditindaklanjuti daripada laporan “website lambat”. Pegangan praktisnya: index hanya membantu kalau kolomnya dibandingkan apa adanya. Begitu melihat fungsi membungkus kolom di WHERE, curigai full scan, buktikan dengan EXPLAIN, lalu ubah kondisinya menjadi range. Functional index atau generated column cocok sebagai penyelamat sementara saat kode belum bisa disentuh, tapi menulis ulang query tetap menjadi perbaikan yang paling murah untuk jangka panjang.