Type on Join #

Ada kategori bug performa yang paling sulit didiagnosis: query-nya tidak error, datanya benar, tapi semakin lama semakin lambat tanpa alasan yang jelas. Salah satu penyebab paling umum dari kategori ini adalah perbedaan tipe data atau collation antara kolom yang dipakai untuk JOIN. Database akan tetap menjalankan query-nya — ia hanya diam-diam mengabaikan index yang sudah kamu buat dengan susah payah, lalu melakukan full scan setiap request. Di sistem dengan data kecil tidak terasa, tapi di production dengan jutaan baris dan ratusan concurrent user, ini bisa menjadi bottleneck yang sangat serius. Artikel ini membahas mengapa ini terjadi, bagaimana mendeteksinya, dan bagaimana mencegahnya dari awal.

Mengapa Masalah Ini Sering Tidak Terdeteksi #

Perbedaan tipe data di kolom JOIN adalah masalah yang unik karena ia tidak menimbulkan error apapun. Query tetap mengembalikan data yang benar. Tidak ada warning di log. Tidak ada exception di aplikasi. Satu-satunya sinyal adalah performa — dan performa yang buruk seringkali disalahkan ke hal lain dulu: server kurang resource, jaringan lambat, atau beban user meningkat.

Masalah ini paling sering muncul dalam beberapa skenario:

Skenario Umum Perbedaan Tipe di JOIN:
──────────────────────────────────────────────────────────────
  1. Database tanpa enforced foreign key
     → Tidak ada mekanisme yang memaksa konsistensi tipe

  2. Sistem legacy yang berkembang organik
     → Tabel lama pakai INT, tabel baru pakai BIGINT
     → Tidak ada yang sadar ada perbedaan sampai data besar

  3. Arsitektur microservices
     → Setiap service desain schema sendiri
     → Tidak ada standar tipe ID yang disepakati bersama

  4. Tim yang berkembang cepat
     → Developer baru tidak tahu konvensi yang ada
     → Schema review tidak mencakup tipe data kolom join

  5. Migrasi ORM ke raw SQL
     → ORM menyembunyikan tipe kolom
     → Saat tulis query manual, tipe berbeda tidak terlihat
──────────────────────────────────────────────────────────────

Yang membuat ini berbahaya bukan hanya dampak performa-nya, tapi juga cara dampaknya muncul: perlahan, progresif, dan hanya terasa signifikan setelah data sudah sangat besar — saat memperbaikinya jauh lebih mahal.

Bagaimana Implicit Type Casting Membunuh Index #

Ketika kamu melakukan JOIN antara dua kolom yang tipe datanya berbeda, database tidak menolak query tersebut. Sebaliknya, ia melakukan konversi tipe secara otomatis — yang disebut implicit type casting — agar perbandingan bisa dilakukan.

Contoh Kasus: INT vs BIGINT #

-- Struktur tabel (tipe berbeda di kolom join)
CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE orders (
    id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id   INT UNSIGNED NOT NULL,  -- ✗ berbeda: INT bukan BIGINT
    INDEX idx_orders_user_id (user_id)
);

-- Query JOIN yang terlihat normal
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;

Output EXPLAIN yang mengejutkan:

+----+-------+-------+------+---------------------+------+------+-------------+
| id | table | type  | key  | possible_keys       | rows | ref  | Extra       |
+----+-------+-------+------+---------------------+------+------+-------------+
|  1 | o     | ALL   | NULL | idx_orders_user_id  | 1.2M | NULL | Using where |
|  1 | u     | eq_ref| PRIMARY | PRIMARY          |    1 | func | NULL        |
+----+-------+-------+------+---------------------+------+------+-------------+

idx_orders_user_id ada di possible_keys tapi tidak dipakai (key = NULL). Database memilih full scan pada 1.2 juta baris orders.

Mengapa Index Tidak Bisa Dipakai #

Yang terjadi di database saat tipe berbeda:
──────────────────────────────────────────────────────────────
  Kolom yang dicompare: o.user_id (INT) vs u.id (BIGINT)

  Database tidak bisa langsung membandingkan — perlu casting:

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

  Index di orders.user_id menyimpan nilai INT asli.
  Tapi kondisi join sekarang evaluasinya di CAST(user_id).
  Index tidak punya informasi tentang nilai setelah di-cast.

  Hasil: index diabaikan, full scan dilakukan.

  ┌──────────────────────────────────────────────────────┐
  │  Aturan fundamental:                                 │
  │  Index HANYA bisa dipakai jika kolom dibandingkan    │
  │  SECARA LANGSUNG, tanpa transformasi apapun.         │
  │  Casting = transformasi = index tidak terpakai.      │
  └──────────────────────────────────────────────────────┘

Pasangan Tipe yang Sering Menyebabkan Masalah #

-- ✗ Kombinasi bermasalah yang umum ditemukan:

-- 1. INT vs BIGINT (paling sering)
users.id       BIGINT UNSIGNED
orders.user_id INT UNSIGNED

-- 2. VARCHAR vs CHAR
products.sku    VARCHAR(50)
order_items.sku CHAR(50)

-- 3. INT vs VARCHAR (paling buruk)
users.id       INT
sessions.user_id VARCHAR(20)

-- 4. BIGINT vs INT dengan signed/unsigned berbeda
users.id       BIGINT UNSIGNED
logs.user_id   BIGINT  -- tanpa UNSIGNED

-- ✓ Yang benar: identik di semua aspek
users.id       BIGINT UNSIGNED NOT NULL
orders.user_id BIGINT UNSIGNED NOT NULL
Perbedaan SIGNED vs UNSIGNED juga menyebabkan implicit casting meskipun tipe dasarnya sama. BIGINT dan BIGINT UNSIGNED adalah tipe yang berbeda di mata query optimizer.

Collation: Masalah yang Lebih Halus #

Perbedaan collation adalah bentuk masalah yang lebih sulit terdeteksi dibanding perbedaan tipe data numerik, karena kedua kolom terlihat identik di permukaan — sama-sama VARCHAR(36) atau CHAR(36) — tapi berbeda di level collation.

Apa Itu Collation dan Mengapa Penting #

Collation adalah aturan yang menentukan bagaimana database membandingkan dan mengurutkan string. Dua string yang “sama” secara visual bisa dianggap berbeda oleh database tergantung collation yang dipakai.

Collation yang umum di MySQL:
──────────────────────────────────────────────────────────────
  utf8mb4_general_ci
    → Case insensitive, perbandingan cepat tapi kurang akurat
    → 'a' = 'A', 'e' = 'é' (dianggap sama)

  utf8mb4_unicode_ci
    → Case insensitive, mengikuti standar Unicode penuh
    → Lebih akurat, sedikit lebih lambat dari general_ci
    → 'a' = 'A', tapi 'e' ≠ 'é'

  utf8mb4_bin
    → Binary comparison, case sensitive
    → Perbandingan byte per byte
    → Paling deterministik, cocok untuk identifier/UUID
    → 'a' ≠ 'A'
──────────────────────────────────────────────────────────────

Mengapa JOIN dengan Collation Berbeda Merusak Index #

-- Struktur tabel dengan collation berbeda
CREATE TABLE users (
    id CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE sessions (
    id      CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    user_id CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    INDEX idx_sessions_user_id (user_id)
);

-- Query JOIN
SELECT s.id, s.created_at, u.name
FROM sessions s
JOIN users u ON s.user_id = u.id;

Yang terjadi di database:

Masalah collation saat JOIN:
──────────────────────────────────────────────────────────────
  s.user_id menggunakan: utf8mb4_unicode_ci
  u.id      menggunakan: utf8mb4_general_ci

  Database harus menentukan: collation mana yang dipakai untuk compare?

  MySQL memilih collation dengan precedence lebih tinggi,
  lalu mengonversi satu kolom ke collation tersebut.

  Konversi = transformasi runtime = index tidak bisa dipakai.

  Extra di EXPLAIN: "Using where" + "Cannot use index"
──────────────────────────────────────────────────────────────

Bedanya dengan perbedaan tipe numerik: ini bahkan lebih tidak terlihat karena tidak ada casting yang eksplisit. Query-nya tertulis benar, datanya benar, tapi optimizer diam-diam melakukan konversi di balik layar.

Cara Mendeteksi Masalah Collation #

-- Cek collation kolom di tabel tertentu
SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'nama_database'
  AND TABLE_NAME IN ('users', 'sessions')
  AND COLUMN_NAME IN ('id', 'user_id');

-- Output yang bermasalah:
-- +-------------+-----------+--------------------+--------------------+
-- | COLUMN_NAME | DATA_TYPE | CHARACTER_SET_NAME | COLLATION_NAME     |
-- +-------------+-----------+--------------------+--------------------+
-- | id          | char      | utf8mb4            | utf8mb4_general_ci |
-- | user_id     | char      | utf8mb4            | utf8mb4_unicode_ci |
-- +-------------+-----------+--------------------+--------------------+
--
-- Berbeda collation = JOIN tidak bisa pakai index optimal

Dampak ke Query Planner: Bukti dari EXPLAIN #

Untuk memahami seberapa besar dampaknya, lihat perbandingan output EXPLAIN secara langsung.

Skenario: JOIN antara VARCHAR dan INT #

-- Setup data simulasi
-- users.id = BIGINT (1 juta baris)
-- orders.user_id = VARCHAR(20) (5 juta baris)

-- Query
EXPLAIN SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;  -- VARCHAR vs BIGINT

Hasil EXPLAIN (tipe berbeda):
+----+-------+------+------+---------------------+------+------+------------------+
| id | table | type | key  | possible_keys       | rows | ref  | Extra            |
+----+-------+------+------+---------------------+------+------+------------------+
|  1 | o     | ALL  | NULL | idx_orders_user_id  | 5.0M | NULL | Using where      |
|  1 | u     | ALL  | NULL | PRIMARY             | 1.0M | NULL | Using join buffer|
+----+-------+------+------+---------------------+------+------+------------------+

Full scan di kedua tabel. 5 juta × 1 juta operasi dalam worst case.

-- Setelah perbaikan: samakan tipe
-- orders.user_id diubah ke BIGINT UNSIGNED

EXPLAIN SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;  -- BIGINT UNSIGNED vs BIGINT UNSIGNED

Hasil EXPLAIN (tipe identik):
+----+-------+--------+---------------------+---------------------+------+------+-------+
| id | table | type   | key                 | possible_keys       | rows | ref  | Extra |
+----+-------+--------+---------------------+---------------------+------+------+-------+
|  1 | u     | ALL    | NULL                | PRIMARY             | 1.0M | NULL | NULL  |
|  1 | o     | ref    | idx_orders_user_id  | idx_orders_user_id  |    5 | u.id | NULL  |
+----+-------+--------+---------------------+---------------------+------+------+-------+

type = ref di tabel orders — index dipakai, 5 baris per user dibaca, bukan 5 juta.

Solusi: Memperbaiki Tipe Data yang Sudah Berbeda #

Jika masalah sudah ada di production, ada beberapa pendekatan tergantung seberapa besar dampak migrasinya.

Pendekatan 1: Migrasi Langsung (Tabel Kecil) #

Untuk tabel dengan data tidak terlalu besar dan downtime bisa dijadwalkan:

-- ANTI-PATTERN: biarkan tipe berbeda dan tambahkan CAST di query
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON CAST(o.user_id AS BIGINT) = u.id;
-- ✗ Ini menyembunyikan masalah dan tetap tidak pakai index

-- BENAR: perbaiki di schema, bukan di query
-- Langkah 1: tambahkan kolom baru dengan tipe yang benar
ALTER TABLE orders ADD COLUMN user_id_new BIGINT UNSIGNED;

-- Langkah 2: isi data kolom baru
UPDATE orders SET user_id_new = CAST(user_id AS UNSIGNED);

-- Langkah 3: tambahkan index di kolom baru
CREATE INDEX idx_orders_user_id_new ON orders(user_id_new);

-- Langkah 4: update query ke kolom baru (deploy aplikasi)
-- Langkah 5: drop kolom lama setelah yakin tidak ada referensi
ALTER TABLE orders DROP COLUMN user_id;
ALTER TABLE orders RENAME COLUMN user_id_new TO user_id;

Pendekatan 2: Shadow Column untuk Tabel Besar (Zero Downtime) #

Untuk tabel dengan ratusan juta baris yang tidak bisa dimigrasi sekaligus:

-- Tambahkan generated column sebagai bridge sementara
ALTER TABLE orders
ADD COLUMN user_id_bigint BIGINT UNSIGNED
GENERATED ALWAYS AS (CAST(user_id AS UNSIGNED)) STORED,
ADD INDEX idx_orders_user_id_bigint (user_id_bigint);

-- JOIN sementara pakai kolom shadow
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id_bigint = u.id;
-- ✓ Index terpakai, performa membaik sementara migrasi berjalan
Generated column sebagai shadow adalah solusi transisi yang valid untuk zero-downtime migration, tapi harus ada rencana untuk menghapusnya setelah migrasi selesai. Kolom redundan yang tidak dibersihkan akan menambah overhead write di masa depan.

Pendekatan 3: Perbaiki Collation #

-- Cek collation database dan tabel saat ini
SHOW CREATE DATABASE nama_database;
SHOW CREATE TABLE users;

-- Ubah collation kolom yang bermasalah
ALTER TABLE sessions
MODIFY COLUMN user_id CHAR(36)
CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

-- Pastikan index di-rebuild setelah perubahan collation
-- (ALTER MODIFY otomatis rebuild index di kolom tersebut)

-- Verifikasi dengan EXPLAIN setelah perubahan
EXPLAIN SELECT s.id, u.name
FROM sessions s
JOIN users u ON s.user_id = u.id;

Mencegah dari Awal: Konvensi Schema yang Ketat #

Memperbaiki masalah tipe data setelah production jauh lebih mahal daripada mencegahnya. Berikut konvensi yang harus diterapkan sejak awal sebagai standar tim.

ID Type Registry: Satu Standar untuk Semua Tabel #

-- ✓ Standar yang harus didokumentasikan dan diikuti semua developer:

-- Primary key: auto-increment integer
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT

-- Foreign key reference ke tabel lain
user_id    BIGINT UNSIGNED NOT NULL
order_id   BIGINT UNSIGNED NOT NULL
product_id BIGINT UNSIGNED NOT NULL

-- UUID / GUID sebagai identifier eksternal
external_id CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL

-- ✗ Yang harus dilarang:
-- INT untuk FK ke tabel yang primary key-nya BIGINT
-- VARCHAR untuk FK ke tabel yang primary key-nya INT/BIGINT
-- CHAR(36) dengan collation berbeda di tabel yang saling join

Template Migration yang Aman #

Setiap kali membuat tabel baru yang punya relasi ke tabel lain, gunakan template ini sebagai checklist:

-- Template migration tabel baru dengan FK ke tabel lain
CREATE TABLE order_items (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id   BIGINT UNSIGNED NOT NULL,  -- ✓ identik dengan orders.id
    product_id BIGINT UNSIGNED NOT NULL,  -- ✓ identik dengan products.id
    quantity   INT UNSIGNED NOT NULL DEFAULT 1,
    price      DECIMAL(15, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_order_items_order_id (order_id),
    INDEX idx_order_items_product_id (product_id)

    -- Jika FK dipakai:
    -- FOREIGN KEY (order_id) REFERENCES orders(id),
    -- FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Sebelum CREATE:
-- □ Tipe order_id identik dengan orders.id?
-- □ Tipe product_id identik dengan products.id?
-- □ Collation konsisten dengan tabel yang di-join?
-- □ Index sudah dibuat di kolom FK?

Perlakukan Schema sebagai API Contract #

Prinsip: Schema adalah kontrak, bukan detail implementasi
──────────────────────────────────────────────────────────────
  API contract yang berubah = breaking change untuk consumer.
  Schema yang berubah = breaking change untuk semua query.

  Perubahan tipe kolom ID di production:
    → Semua query yang join ke tabel ini terpengaruh
    → Semua index di kolom FK harus di-rebuild
    → Potensi downtime atau degradasi performa saat migrasi

  Konsekuensinya:
    → Desain tipe data yang benar di awal jauh lebih murah
    → Schema review harus mencakup tipe kolom join
    → "Nanti diperbaiki" artinya "nanti sangat mahal"
──────────────────────────────────────────────────────────────

Anti-Pattern yang Harus Dihindari #

Setelah memahami akar masalahnya, berikut anti-pattern konkret yang paling sering ditemukan saat code review:

-- ✗ Anti-pattern 1: CAST eksplisit di kondisi JOIN
SELECT * FROM orders o
JOIN users u ON CAST(o.user_id AS SIGNED) = u.id;
-- Solusi: perbaiki tipe kolom di schema, bukan cast di query

-- ✗ Anti-pattern 2: type mismatch yang samar (INT vs BIGINT)
-- orders.user_id INT, users.id BIGINT
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- Terlihat benar, tapi implicit casting terjadi → index tidak terpakai
-- Solusi: ALTER TABLE orders MODIFY user_id BIGINT UNSIGNED NOT NULL;

-- ✗ Anti-pattern 3: join UUID sebagai VARCHAR ke CHAR
-- sessions.user_id VARCHAR(36), users.id CHAR(36)
SELECT * FROM sessions s JOIN users u ON s.user_id = u.id;
-- VARCHAR dan CHAR punya comparison rule berbeda
-- Solusi: gunakan tipe yang sama (keduanya CHAR(36) atau keduanya VARCHAR(36))

-- ✗ Anti-pattern 4: join string ke angka (paling buruk)
-- logs.user_id VARCHAR(20), users.id INT
SELECT * FROM logs l JOIN users u ON l.user_id = u.id;
-- Database convert semua angka ke string → full scan + konversi
-- Solusi: ubah logs.user_id ke INT NOT NULL

-- ✗ Anti-pattern 5: shadow column dibiarkan permanen
ALTER TABLE orders ADD user_id_bigint BIGINT GENERATED ALWAYS AS (...);
-- Lalu tidak pernah dimigrasi dan kolom lama tidak dihapus
-- Solusi: buat tiket teknis eksplisit untuk menyelesaikan migrasi

Checklist Review Schema untuk JOIN #

Gunakan checklist ini saat code review migration baru atau saat audit performa:

SAAT MEMBUAT TABEL BARU:
  □ Semua kolom FK tipe-nya identik dengan kolom PK yang direferensikan?
  □ BIGINT vs BIGINT (bukan INT vs BIGINT)?
  □ UNSIGNED vs UNSIGNED (konsisten)?
  □ Collation sama di semua kolom yang akan di-JOIN?
  □ Index sudah dibuat di semua kolom FK?

SAAT REVIEW QUERY JOIN:
  □ Sudah jalankan EXPLAIN untuk query ini?
  □ Tidak ada CAST di kondisi ON atau WHERE?
  □ type di EXPLAIN bukan ALL untuk kedua tabel (kecuali tabel kecil)?
  □ key di EXPLAIN menunjukkan index yang tepat (bukan NULL)?

SAAT AUDIT PERFORMA:
  □ Cek INFORMATION_SCHEMA.COLUMNS untuk kolom yang di-JOIN
  □ Pastikan CHARACTER_SET_NAME dan COLLATION_NAME identik
  □ Pastikan DATA_TYPE identik (bukan hanya "mirip")
  □ Cek NUMERIC_PRECISION untuk tipe numerik

Ringkasan #

  • Perbedaan tipe data di kolom JOIN membunuh index secara diam-diam — query tetap berjalan dan hasilnya benar, tapi optimizer melakukan implicit casting yang membuat index tidak bisa dipakai, berujung full table scan.
  • Perbedaan collation sama berbahayanya dengan perbedaan tipe — dua kolom CHAR(36) dengan collation berbeda akan menyebabkan konversi runtime yang mencegah index dipakai saat JOIN.
  • Jangan pernah pakai CAST di kondisi JOIN sebagai “solusi” — itu hanya menyembunyikan masalah desain sambil memastikan index permanen tidak terpakai. Perbaiki di schema.
  • BIGINT dan INT adalah tipe yang berbeda — begitu juga BIGINT dan BIGINT UNSIGNED. Pastikan tipe FK identik secara eksak dengan tipe PK yang direferensikan, termasuk signed/unsigned-nya.
  • Shadow column adalah solusi transisi yang valid — untuk tabel besar yang tidak bisa dimigrasi langsung, generated column dengan tipe yang benar bisa menjadi bridge sementara, tapi harus ada rencana untuk menyelesaikan migrasinya.
  • Schema adalah API contract — perubahan tipe ID di production adalah breaking change yang mahal. Desain yang benar sejak awal jauh lebih murah daripada migrasi di kemudian hari.
  • Buat dan ikuti ID Type Registry — standarkan tipe untuk setiap kategori identifier (PK, FK, UUID) dan jadikan ini bagian dari template migration dan checklist review.
  • Selalu verifikasi dengan EXPLAIN setelah perubahan schema — pastikan type di EXPLAIN berubah dari ALL ke ref atau eq_ref, dan key menunjukkan index yang tepat.

← Sebelumnya: Use EXPLAIN   Berikutnya: RAND() →

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