Type on Join #

Dalam praktik engineering di dunia nyata, performa query sering kali bukan jatuh karena data besar, tapi karena desain skema yang “kelihatannya sepele”—salah satunya adalah perbedaan tipe data dan collation saat melakukan JOIN.

Masalah ini sangat sering muncul pada:

• Database tanpa enforced foreign key
• Sistem legacy
• Arsitektur microservices / modular database
• Tim yang berkembang cepat tanpa governance schema yang ketat

Artikel ini akan membahas:

1. Latar belakang masalah
2. Kenapa perbedaan tipe data & collation sangat berbahaya untuk performa
3. Contoh nyata di level query planner
4. Solusi dan alternatif desain
5. Best practice jangka panjang untuk menyamakan tipe data

Latar Belakang #

Banyak sistem secara sengaja tidak menggunakan foreign key constraint, dengan alasan seperti:

• Menghindari overhead constraint checking
• Mendukung distributed system
• Mengurangi coupling antar module
• Menghindari deadlock pada write-heavy workload

Akibatnya:

• Relasi antar tabel hanya “konvensi”
• Validasi integritas dilakukan di application layer
• Tidak ada mekanisme database yang memaksa kesamaan tipe data

Contoh klasik:

users.id           -> BIGINT
orders.user_id     -> VARCHAR(36)

Atau:

users.id           -> CHAR(36) COLLATE utf8mb4_general_ci
orders.user_id     -> CHAR(36) COLLATE utf8mb4_unicode_ci

Kelihatannya masih bisa JOIN, tapi di balik layar: query optimizer menderita.

Kenapa Perbedaan Tipe Data di JOIN Itu Masalah Besar #

JOIN dengan Implicit Type Casting #

Contoh query:

SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id;

Jika:

• o.user_id = VARCHAR
• u.id = BIGINT

Maka database akan melakukan:

CAST(o.user_id AS BIGINT) = u.id
-- atau
CAST(u.id AS CHAR) = o.user_id

⚠️ Masalahnya:

• Index tidak bisa digunakan
• JOIN berubah menjadi full table scan
• Cost meningkat drastis seiring data bertambah

Index tidak bisa dipakai jika kolom di-cast di runtime

Dampak Langsung ke Query Planner #

Query planner hanya bisa menggunakan index jika:

• Tipe data sama
• Collation sama
• Operator kompatibel

Jika tidak:

• Planner memilih Nested Loop + Seq Scan
• Atau Hash Join dengan memory besar
• Atau bahkan fallback ke worst plan

Efeknya:

• Query lambat tanpa terlihat di kode
• Sulit didiagnosis tanpa EXPLAIN ANALYZE

Masalah Lebih Halus: Perbedaan Collation #

Apa Itu Collation? #

Collation menentukan:

• Cara string dibandingkan
• Case sensitivity
• Sorting behavior

Contoh:

• utf8mb4_general_ci
• utf8mb4_unicode_ci
• utf8mb4_bin

JOIN dengan Collation Berbeda #

users.id       CHAR(36) COLLATE utf8mb4_general_ci
orders.user_id CHAR(36) COLLATE utf8mb4_unicode_ci

JOIN:

ON users.id = orders.user_id

Akan menyebabkan:

• Runtime collation conversion
• Index pada salah satu kolom tidak dipakai
• Extra CPU cost per row comparison

Masalah ini sangat sulit terdeteksi, karena:

• Query tetap “jalan”
• Tidak ada error
• Performa degradasi perlahan

Kenapa Foreign Key Sebenarnya Membantu (Walau Tidak Dipakai)? #

Foreign key bukan cuma soal integrity, tapi juga:

• Memaksa tipe data identik
• Memaksa collation konsisten
• Memberi sinyal kuat ke optimizer tentang relasi tabel

Tanpa FK:

• Database tidak tahu relasi semantik
• Semua JOIN dianggap “bebas”
• Optimizer kehilangan informasi penting

Solusi & Alternatif Jika Tidak Menggunakan Foreign Key #

Jika FK tidak memungkinkan, berikut solusi realistis.

Samakan Tipe Data Secara Absolut #

Rule keras:

Kolom yang akan di-JOIN HARUS identik:

• Type
• Length
• Signed/Unsigned
• Collation

❌ Buruk:

users.id BIGINT
orders.user_id INT

✅ Baik:

users.id BIGINT UNSIGNED
orders.user_id BIGINT UNSIGNED

Gunakan “Logical FK Convention” #

Buat kontrak skema, misalnya:

Dan wajib konsisten di semua module.

Biasanya dituangkan dalam:

• Schema guideline
• Migration template
• Review checklist

Hindari CAST di JOIN (Ini Anti-Pattern) #

❌ Jangan pernah:

ON CAST(o.user_id AS BIGINT) = u.id

Atau:

ON u.id = CAST(o.user_id AS CHAR)

Ini:

• Membunuh index
• Tidak scalable
• Menyembunyikan masalah desain

Jika perlu CAST → perbaiki schema, bukan query

Gunakan Generated / Shadow Column (Alternatif) #

Jika refactor besar belum memungkinkan:

ALTER TABLE orders
ADD user_id_bigint BIGINT
GENERATED ALWAYS AS (CAST(user_id AS BIGINT)) STORED,
ADD INDEX idx_user_id_bigint (user_id_bigint);

JOIN pakai:

ON orders.user_id_bigint = users.id

⚠️ Ini solusi transisi, bukan ideal.

Standarisasi Collation Global #

Best practice:

• Tentukan 1 collation default
• Gunakan untuk semua ID string

Contoh:

CHAR(36) COLLATE utf8mb4_bin

Kenapa *_bin?

• Deterministik
• Cepat
• Cocok untuk UUID / identifier

Best Practice Jangka Panjang (Yang Seharusnya Dilakukan Sejak Awal) #

Perlakukan Schema sebagai API Contract #

Schema bukan “detail internal”, tapi:

• Kontrak antar module
• Fondasi performa
• Aset jangka panjang

Perubahan tipe ID = breaking change

Buat ID Type Registry #

Contoh dokumentasi internal:

Primary Key:
- BIGINT UNSIGNED AUTO_INCREMENT

Foreign Reference:
- BIGINT UNSIGNED

UUID:
- CHAR(36) utf8mb4_bin

Dan tidak boleh improvisasi.

Selalu Review EXPLAIN untuk JOIN Kritis #

Biasakan:

EXPLAIN ANALYZE

Red flag:

• Seq Scan
• Type Cast
• Using where tanpa index

Jika Tanpa FK, Ganti dengan Discipline #

Tidak pakai FK ≠ bebas desain.

Tanpa FK:

• Discipline harus lebih ketat
• Schema review jadi wajib
• Konsistensi tipe adalah harga yang harus dibayar

Penutup #

Perbedaan tipe data dan collation pada JOIN adalah silent performance killer. Ia tidak error, tidak crash, tapi:

• Membunuh index
• Mengacaukan optimizer
• Menjadi bom waktu saat data membesar

Query optimization bukan cuma soal query — ia dimulai dari desain schema.