SQL Function Overuse #

Ada kategori bug performa yang sangat umum tapi sangat mudah terlewat saat code review: penggunaan fungsi SQL — seperti LOWER(), DATE(), CAST(), SUBSTRING(), atau TRIM() — langsung di dalam klausa WHERE atau kondisi JOIN. Query-nya terlihat wajar, hasilnya benar, dan di environment development dengan ribuan baris data semuanya terasa cepat. Masalah baru muncul di production saat data sudah jutaan baris: CPU database naik tanpa sebab yang jelas, query yang sama tiba-tiba butuh 8 detik, dan team mulai berdebat apakah perlu upgrade server. Padahal akar masalahnya sederhana — satu fungsi yang salah tempat membuat seluruh index diabaikan dan database terpaksa membaca setiap baris. Artikel ini membahas mengapa ini terjadi, delapan pola yang paling sering ditemukan, dan cara memperbaiki masing-masing tanpa mengubah logika bisnis.

Mengapa Fungsi di WHERE Membunuh Index #

Untuk memahami masalahnya, perlu dipahami dulu cara database memutuskan apakah akan menggunakan index atau melakukan full scan. Query planner bekerja dengan satu prinsip: index hanya bisa dipakai jika kolom yang di-filter bisa dibandingkan secara langsung dengan nilai yang dicari.

Ketika kamu menulis WHERE email = '[email protected]', database bisa membuka index email, langsung melompat ke entri yang nilainya [email protected], dan mengambil baris tersebut. Cepat, efisien, O(log n).

Ketika kamu menulis WHERE LOWER(email) = '[email protected]', situasinya berbeda total:

Yang terjadi saat fungsi dipakai di WHERE:
──────────────────────────────────────────────────────────────
  Index email menyimpan nilai asli:
    - "[email protected]"
    - "[email protected]"
    - "[email protected]"
    - ... (1 juta entri)

  Query mencari: LOWER(email) = '[email protected]'

  Database tidak punya index untuk LOWER(email).
  Database tidak bisa tahu hasil LOWER() sebelum dieksekusi.

  Satu-satunya cara: baca SEMUA baris, panggil LOWER() di setiap
  baris, bandingkan hasilnya.

  Hasil:
    → Full table scan: 1 juta baris dibaca
    → LOWER() dipanggil 1 juta kali
    → Index yang sudah kamu buat tidak berguna sama sekali
──────────────────────────────────────────────────────────────

Output EXPLAIN yang khas untuk kondisi ini:

-- Query dengan fungsi di WHERE
EXPLAIN SELECT id, name FROM users WHERE LOWER(email) = '[email protected]';

-- +------+------+------+------+--------+-------------+
-- | type | key  | ref  | rows | Extra               |
-- +------+------+------+------+--------+-------------+
-- | ALL  | NULL | NULL | 980000 | Using where      |
-- +------+------+------+------+--------+-------------+
-- type = ALL     → full table scan
-- key  = NULL    → index tidak dipakai
-- rows = 980000  → hampir semua baris dibaca

-- Query tanpa fungsi di WHERE
EXPLAIN SELECT id, name FROM users WHERE email = '[email protected]';

-- +-------+-----------------+-------+------+-------+
-- | type  | key             | ref   | rows | Extra |
-- +-------+-----------------+-------+------+-------+
-- | ref   | idx_users_email | const |    1 | NULL  |
-- +-------+-----------------+-------+------+-------+
-- type = ref   → pakai index
-- rows = 1     → langsung ke baris yang tepat

Perbedaan 980.000 baris vs 1 baris — dari query yang secara logika identik.

Delapan Pola Fungsi Bermasalah dan Solusinya #

Berikut delapan pola yang paling sering ditemukan saat code review, masing-masing dengan penjelasan mengapa bermasalah dan cara memperbaikinya.

Pola 1: LOWER() / UPPER() untuk Pencarian Case-Insensitive #

Kebutuhan: cari user berdasarkan email tanpa peduli huruf besar/kecil.

-- ANTI-PATTERN: fungsi di kolom WHERE
SELECT id, name FROM users
WHERE LOWER(email) = LOWER(?);
-- → Full scan, panggil LOWER() jutaan kali per query

-- BENAR — Opsi A: normalisasi data saat insert/update
-- Simpan email selalu dalam lowercase di database
INSERT INTO users (name, email) VALUES ('Budi', LOWER('[email protected]'));
-- Sekarang query sederhana langsung pakai index:
SELECT id, name FROM users WHERE email = ?;
-- Lowercase input di application layer sebelum query

-- BENAR — Opsi B: gunakan collation case-insensitive
-- Kolom dengan collation _ci sudah case-insensitive by default
CREATE TABLE users (
    email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);
-- Query tanpa LOWER() tetap menemukan '[email protected]' dan '[email protected]'
SELECT id, name FROM users WHERE email = ?;
-- Index tetap terpakai karena kolom di-compare langsung, bukan via fungsi

Pola 2: DATE() untuk Filter Tanggal #

Kebutuhan: ambil semua order yang dibuat pada tanggal tertentu.

-- ANTI-PATTERN: DATE() membungkus kolom DATETIME
SELECT id, total FROM orders
WHERE DATE(created_at) = '2026-01-15';
-- → Index di created_at tidak bisa dipakai
-- → Full scan + DATE() dipanggil setiap baris

-- BENAR: gunakan range query tanpa fungsi di kolom
SELECT id, total FROM orders
WHERE created_at >= '2026-01-15 00:00:00'
  AND created_at <  '2026-01-16 00:00:00';
-- → type: range, index terpakai, hanya baris dalam rentang yang dibaca

-- Atau di Go, hitung range di application layer:
start := time.Date(2026, 1, 15, 0, 0, 0, 0, time.UTC)
end   := start.AddDate(0, 0, 1)
db.Query("SELECT id, total FROM orders WHERE created_at >= ? AND created_at < ?",
    start, end)

Pola 3: YEAR() / MONTH() untuk Filter Periode #

Kebutuhan: ambil data berdasarkan tahun atau bulan.

-- ANTI-PATTERN: fungsi YEAR() dan MONTH()
SELECT id, amount FROM transactions
WHERE YEAR(transaction_date) = 2026
  AND MONTH(transaction_date) = 1;
-- → Dua fungsi di WHERE, index mati total

-- BENAR: konversi ke range tanpa fungsi di kolom
SELECT id, amount FROM transactions
WHERE transaction_date >= '2026-01-01'
  AND transaction_date <  '2026-02-01';

-- Untuk kueri "tahun ini":
WHERE transaction_date >= '2026-01-01'
  AND transaction_date <  '2027-01-01';

-- Untuk kueri "bulan lalu" — hitung di application layer, bukan SQL:
-- firstDayLastMonth = tanggal 1 bulan lalu
-- firstDayThisMonth = tanggal 1 bulan ini
WHERE transaction_date >= ? AND transaction_date < ?

Pola 4: CAST() / CONVERT() untuk Penyesuaian Tipe #

Kebutuhan: bandingkan nilai dari kolom dengan tipe berbeda.

-- ANTI-PATTERN: CAST di kolom WHERE karena tipe data tidak cocok
SELECT * FROM orders
WHERE CAST(user_id AS CHAR) = '12345';
-- → Index di user_id (INT) tidak terpakai karena diubah ke CHAR

-- BENAR — Opsi A: cast nilai input, bukan kolom
SELECT * FROM orders
WHERE user_id = CAST('12345' AS UNSIGNED);
-- → Atau lebih baik: cast di application layer, kirim integer ke query
SELECT * FROM orders WHERE user_id = ?;  -- bind parameter integer

-- BENAR — Opsi B: perbaiki tipe data di schema
-- Jika terpaksa CAST karena tipe kolom salah, itu sinyal schema harus diperbaiki
-- Lihat artikel Type on Join untuk strategi migrasi tipe

Pola 5: SUBSTRING() / LEFT() / RIGHT() untuk Pencocokan Awalan #

Kebutuhan: cari berdasarkan bagian dari string.

-- ANTI-PATTERN: SUBSTRING() atau LEFT() di kolom WHERE
SELECT * FROM products
WHERE LEFT(sku, 3) = 'INV';
-- → Full scan, LEFT() dipanggil setiap baris

-- ANTI-PATTERN: LIKE dengan wildcard di awal
SELECT * FROM products
WHERE sku LIKE '%INV%';
-- → Juga full scan — wildcard di awal mencegah index dipakai

-- BENAR: LIKE dengan wildcard hanya di akhir bisa pakai index
SELECT * FROM products
WHERE sku LIKE 'INV%';
-- → type: range, index di sku terpakai untuk scan prefix
-- → Database langsung lompat ke bagian index yang dimulai 'INV'

-- Untuk pencarian di tengah string (LIKE '%kata%'):
-- Gunakan Full-Text Search, bukan LIKE
-- Lihat artikel Full-Text Index untuk strategi yang benar

Pola 6: TRIM() / REPLACE() untuk Normalisasi String #

Kebutuhan: cari data meskipun ada spasi di awal/akhir yang tidak konsisten.

-- ANTI-PATTERN: TRIM() di kolom untuk mengatasi data kotor
SELECT * FROM customers
WHERE TRIM(phone_number) = '08123456789';
-- → Full scan karena TRIM() membungkus kolom

-- Akar masalah: data kotor karena tidak dinormalisasi saat insert

-- BENAR — Opsi A: normalisasi data saat insert dan update
INSERT INTO customers (phone_number) VALUES (TRIM(?));
UPDATE customers SET phone_number = TRIM(phone_number)
WHERE phone_number != TRIM(phone_number);
-- Setelah data bersih, query langsung:
SELECT * FROM customers WHERE phone_number = ?;

-- BENAR — Opsi B: normalisasi di application layer
phone := strings.TrimSpace(input)
db.Query("SELECT * FROM customers WHERE phone_number = ?", phone)

-- JANGAN: biarkan data kotor masuk database dan atasi di query level

Pola 7: COALESCE() / IFNULL() di Kondisi Filter #

Kebutuhan: filter dengan nilai default jika kolom NULL.

-- ANTI-PATTERN: COALESCE() di kolom WHERE
SELECT * FROM products
WHERE COALESCE(category_id, 0) = 5;
-- → Full scan karena COALESCE() membungkus kolom

-- BENAR: pisahkan kondisi NULL dan non-NULL
SELECT * FROM products
WHERE category_id = 5;
-- Atau jika memang perlu handle NULL:
SELECT * FROM products
WHERE (category_id = 5 OR category_id IS NULL);
-- → Kedua kondisi bisa pakai index di category_id

-- Untuk default value, lebih baik di schema:
ALTER TABLE products
MODIFY COLUMN category_id INT UNSIGNED NOT NULL DEFAULT 0;
-- Sekarang COALESCE tidak diperlukan lagi

Pola 8: Fungsi di Kondisi JOIN #

Ini adalah pola paling berbahaya karena dampaknya berlipat ganda — bukan sekadar full scan satu tabel, tapi nested loop antara dua tabel besar.

-- ANTI-PATTERN: fungsi di kondisi JOIN
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON LOWER(o.customer_email) = LOWER(u.email);
-- → Index di o.customer_email tidak terpakai
-- → Index di u.email tidak terpakai
-- → Untuk setiap baris orders, scan seluruh users sambil hitung LOWER()
-- → Kompleksitas mendekati O(n × m): jutaan × jutaan operasi

-- BENAR — Opsi A: gunakan foreign key integer, bukan email untuk JOIN
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;
-- → eq_ref: index primary key di kedua sisi, sangat efisien

-- BENAR — Opsi B: jika JOIN via string tidak bisa dihindari,
-- normalisasi data di kolom join ke format yang konsisten
-- Simpan selalu lowercase, gunakan collation yang sama
-- Lalu JOIN tanpa fungsi:
ON o.customer_email = u.email
-- (keduanya sudah lowercase, collation sama)
Fungsi di kondisi ON saat JOIN adalah kombinasi terburuk: dua full scan sekaligus, ditambah fungsi dipanggil untuk setiap pasangan baris dari kedua tabel. Di tabel dengan masing-masing 500 ribu baris, ini berarti potensi 250 miliar operasi perbandingan.

Functional Index: Jalan Terakhir, Bukan Solusi Utama #

PostgreSQL dan MySQL 8.0+ mendukung functional index (juga disebut expression index) — index yang dibuat berdasarkan hasil ekspresi atau fungsi, bukan nilai kolom aslinya. Ini berguna ketika kamu tidak bisa mengubah query atau schema yang sudah ada.

-- PostgreSQL: buat functional index untuk LOWER(email)
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Sekarang query dengan LOWER() di WHERE bisa pakai index ini
EXPLAIN SELECT id, name FROM users WHERE LOWER(email) = '[email protected]';
-- → Index Scan using idx_users_email_lower (pakai index)

-- MySQL 8.0+: functional index
CREATE INDEX idx_users_email_lower ON users ((LOWER(email)));
-- Perhatikan tanda kurung ganda — sintaks MySQL untuk functional index

-- Contoh untuk DATE():
-- PostgreSQL
CREATE INDEX idx_orders_created_date ON orders (DATE(created_at));
-- Sekarang WHERE DATE(created_at) = '2026-01-15' bisa pakai index ini

Tapi functional index bukan solusi tanpa biaya:

Trade-off functional index:
──────────────────────────────────────────────────────────────
  Manfaat:
    ✓ Query lama tidak perlu diubah
    ✓ Index bisa dipakai untuk fungsi yang sering digunakan
    ✓ Berguna untuk query dari ORM yang sulit dikontrol

  Biaya:
    ✗ Storage tambahan untuk setiap functional index
    ✗ Overhead write: setiap INSERT/UPDATE harus hitung fungsi
       dan update index
    ✗ Hanya berlaku untuk ekspresi yang identik persis
       LOWER(email) ≠ LOWER(TRIM(email)) → index berbeda
    ✗ Lebih sulit dikelola dan didokumentasikan
    ✗ Tidak semua query planner menggunakannya secara otomatis

  Kesimpulan:
    Functional index adalah pilihan terakhir untuk legacy code
    atau query yang tidak bisa diubah. Untuk kode baru, selalu
    pilih normalisasi data atau rewrite query tanpa fungsi di kolom.
──────────────────────────────────────────────────────────────
MySQL versi di bawah 8.0 tidak mendukung functional index. Untuk MySQL 5.7 dan sebelumnya, satu-satunya pilihan adalah normalisasi data atau menambahkan kolom generated (computed column) yang menyimpan hasil fungsi, lalu index kolom generated tersebut.

Strategi Normalisasi Data: Mencegah Sejak Awal #

Sebagian besar masalah SQL function overuse berakar dari data yang tidak dinormalisasi saat disimpan. Email yang kadang uppercase kadang mixed-case, nomor telepon dengan spasi atau tanda hubung yang tidak konsisten, tanggal yang disimpan sebagai string dengan format berbeda-beda — semuanya mendorong developer untuk menambahkan fungsi di query sebagai “solusi”.

Solusi yang benar adalah mencegah data kotor masuk ke database sejak awal.

-- Strategi normalisasi: terapkan di level insert/update, bukan query

-- Email: selalu lowercase
INSERT INTO users (email) VALUES (LOWER(TRIM(?)));

-- Telepon: hapus karakter non-digit
-- (lakukan di application layer sebelum insert)
-- Go: regexp.MustCompile(`\D`).ReplaceAllString(phone, "")
-- Hasilnya: "0812-345 6789" → "08123456789"

-- String untuk pencarian: simpan versi yang sudah dinormalisasi
-- di kolom terpisah jika perlu (search_name dari name)
ALTER TABLE products ADD COLUMN name_normalized VARCHAR(255)
    GENERATED ALWAYS AS (LOWER(TRIM(name))) STORED;
CREATE INDEX idx_products_name_normalized ON products(name_normalized);

-- Sekarang pencarian case-insensitive menggunakan kolom generated:
SELECT * FROM products WHERE name_normalized = LOWER(TRIM(?));
-- → Index terpakai, fungsi hanya dipanggil sekali untuk input
-- → Bukan jutaan kali untuk setiap baris di tabel

Kolom generated (atau computed column di beberapa database) adalah jalan tengah yang elegan: nilai disimpan di disk dan di-index, tapi dihasilkan secara otomatis dari kolom lain — tidak perlu logic di application layer untuk mengisinya.

Mendeteksi SQL Function Overuse di Codebase #

Jika kamu menduga ada masalah ini di codebase yang sudah berjalan, berikut cara sistematis menemukannya.

Via Slow Query Log (MySQL) #

-- Aktifkan slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;  -- query > 500ms masuk log
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Analisis dengan mysqldumpslow
-- mysqldumpslow -s t -t 20 /var/log/mysql/slow.log
-- → Tampilkan 20 query terlambat berdasarkan waktu eksekusi total

Via INFORMATION_SCHEMA (identifikasi tabel besar tanpa index optimal) #

-- Cari tabel dengan row count besar untuk diprioritaskan
SELECT
    TABLE_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'nama_database'
  AND TABLE_ROWS > 100000
ORDER BY TABLE_ROWS DESC;

-- Setelah menemukan tabel besar, jalankan EXPLAIN untuk query
-- yang menyentuh tabel tersebut — prioritaskan yang ada fungsi di WHERE

Checklist Review Query Sebelum Merge #

SAAT REVIEW PULL REQUEST YANG MENYENTUH QUERY:
  □ Ada fungsi yang membungkus kolom di klausa WHERE?
     (LOWER, UPPER, DATE, YEAR, MONTH, CAST, CONVERT,
      SUBSTRING, LEFT, RIGHT, TRIM, REPLACE, COALESCE, IFNULL)
  □ Ada fungsi di kondisi ON saat JOIN?
  □ Ada LIKE '%keyword%' (wildcard di awal)?
  □ Sudah dijalankan EXPLAIN untuk query baru ini?
  □ EXPLAIN menunjukkan type = ALL di tabel dengan > 10.000 baris?
  □ Apakah masalahnya bisa diselesaikan dengan normalisasi data
     daripada menambahkan fungsi di query?

Anti-Pattern yang Harus Dihindari #

Berikut ringkasan pasangan anti-pattern dan solusi dalam satu tampilan untuk referensi cepat:

-- ✗ Anti-pattern 1: LOWER() di kolom WHERE
WHERE LOWER(email) = '[email protected]'
-- ✓ Solusi: simpan email lowercase, atau pakai collation _ci

-- ✗ Anti-pattern 2: DATE() di kolom DATETIME
WHERE DATE(created_at) = '2026-01-15'
-- ✓ Solusi: WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16'

-- ✗ Anti-pattern 3: YEAR() dan MONTH()
WHERE YEAR(order_date) = 2026 AND MONTH(order_date) = 3
-- ✓ Solusi: WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01'

-- ✗ Anti-pattern 4: CAST() di kolom untuk menyesuaikan tipe
WHERE CAST(user_id AS CHAR) = '999'
-- ✓ Solusi: cast di application layer, perbaiki tipe kolom di schema

-- ✗ Anti-pattern 5: LEFT() untuk pencocokan awalan
WHERE LEFT(product_code, 3) = 'PRD'
-- ✓ Solusi: WHERE product_code LIKE 'PRD%'

-- ✗ Anti-pattern 6: TRIM() untuk mengatasi data kotor
WHERE TRIM(phone) = '08123456789'
-- ✓ Solusi: normalisasi data saat insert, query langsung

-- ✗ Anti-pattern 7: COALESCE() di kolom filter
WHERE COALESCE(status, 'pending') = 'pending'
-- ✓ Solusi: WHERE status = 'pending' OR status IS NULL

-- ✗ Anti-pattern 8: fungsi di kondisi JOIN
ON LOWER(o.email) = LOWER(u.email)
-- ✓ Solusi: gunakan integer FK, atau normalisasi kolom join

Ringkasan #

  • Fungsi di kolom WHERE membuat index tidak bisa dipakai — database tidak bisa mencari di index berdasarkan hasil fungsi karena index menyimpan nilai asli kolom, bukan nilai setelah transformasi. Akibatnya: full table scan setiap query.
  • Fungsi di kondisi JOIN adalah dampak ganda — full scan di dua tabel sekaligus, dengan fungsi dipanggil untuk setiap pasangan baris. Di tabel jutaan baris, ini bisa menjadi operasi yang hampir tidak pernah selesai.
  • Aturan utama: fungsi boleh dipakai di sisi nilai, bukan di sisi kolom — WHERE email = LOWER(?) aman, WHERE LOWER(email) = ? tidak. Bedanya: fungsi dipanggil sekali untuk input, bukan jutaan kali untuk setiap baris.
  • Normalisasi data saat insert adalah pencegahan terbaik — simpan email selalu lowercase, telepon selalu tanpa karakter non-digit, string pencarian selalu dalam format yang konsisten. Query jadi sederhana dan index selalu terpakai.
  • Range query menggantikan DATE() dan YEAR()/MONTH() — WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16' selalu lebih baik dari WHERE DATE(created_at) = '2026-01-15'.
  • LIKE dengan wildcard di awal juga mematikan index — LIKE '%keyword%' sama buruknya dengan fungsi di WHERE. Gunakan LIKE 'prefix%' untuk prefix search, atau Full-Text Search untuk pencarian kata kunci di tengah string.
  • Functional index adalah jalan terakhir untuk legacy code — berguna ketika query atau schema tidak bisa diubah, tapi ada biaya write overhead dan storage. Selalu utamakan normalisasi data dan rewrite query.
  • Selalu jalankan EXPLAIN sebelum merge query baru — type = ALL di tabel besar adalah sinyal bahwa ada fungsi yang salah tempat atau index yang hilang.

← Sebelumnya: Image on Table   Berikutnya: Composite Index →

About | Author | Content Scope | Editorial Policy | Privacy Policy | Disclaimer | Contact