Type on Join #

There’s a category of performance bugs that’s the hardest to diagnose: the query doesn’t error, the data is correct, but it gets slower and slower over time for no clear reason. One of the most common causes of this category is a data type or collation mismatch between the columns used in a JOIN. The database will still run the query — it just silently ignores the index you painstakingly created, then does a full scan on every request. In small-data systems this isn’t felt, but in production with millions of rows and hundreds of concurrent users, it can become a very serious bottleneck. This article covers why this happens, how to detect it, and how to prevent it from the start.

Why This Problem Often Goes Undetected #

Data type mismatches on JOIN columns are a unique problem because they don’t produce any errors. The query still returns correct data. No warnings in the logs. No exceptions in the application. The only signal is performance — and poor performance is usually blamed on something else first: the server lacking resources, slow network, or increased user load.

This problem most often appears in a few scenarios:

Common Scenarios for Type Mismatches in JOINs:
──────────────────────────────────────────────────────────────
  1. Databases without enforced foreign keys
     → No mechanism forces type consistency

  2. Legacy systems that grew organically
     → Old tables use INT, new tables use BIGINT
     → Nobody notices the difference until the data is large

  3. Microservices architectures
     → Each service designs its own schema
     → No shared standard for ID types

  4. Fast-growing teams
     → New developers don't know the existing conventions
     → Schema reviews don't cover join column data types

  5. ORM-to-raw-SQL migrations
     → ORMs hide column types
     → When writing manual queries, type differences aren't visible
──────────────────────────────────────────────────────────────

What makes this dangerous isn’t just the performance impact, but also the way it appears: slowly, progressively, and only significantly felt after the data is already very large — when fixing it is far more expensive.

How Implicit Type Casting Kills Indexes #

When you JOIN two columns with different data types, the database doesn’t reject the query. Instead, it performs an automatic type conversion — called implicit type casting — so the comparison can be done.

Example Case: INT vs BIGINT #

-- Table structure (different types on the join columns)
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,  -- ✗ different: INT not BIGINT
    INDEX idx_orders_user_id (user_id)
);

-- A JOIN query that looks normal
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;

The surprising EXPLAIN output:

+----+-------+-------+------+---------------------+------+------+-------------+
| 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 is in possible_keys but not used (key = NULL). The database chooses a full scan over 1.2 million order rows.

Why the Index Can’t Be Used #

What happens in the database when types differ:
──────────────────────────────────────────────────────────────
  Columns compared: o.user_id (INT) vs u.id (BIGINT)

  The database can't compare directly — it needs casting:

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

  The index on orders.user_id stores the original INT values.
  But the join condition now evaluates on CAST(user_id).
  The index has no information about the post-cast values.

  Result: the index is ignored, a full scan is done.

  ┌──────────────────────────────────────────────────────┐
  │  The fundamental rule:                               │
  │  An index can ONLY be used if the column is compared │
  │  DIRECTLY, without any transformation.               │
  │  Casting = transformation = index unused.            │
  └──────────────────────────────────────────────────────┘

Type Pairs That Often Cause Problems #

-- ✗ Common problematic combinations:

-- 1. INT vs BIGINT (most frequent)
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 (worst)
users.id       INT
sessions.user_id VARCHAR(20)

-- 4. BIGINT vs INT with different signed/unsigned
users.id       BIGINT UNSIGNED
logs.user_id   BIGINT  -- without UNSIGNED

-- ✓ Correct: identical in every aspect
users.id       BIGINT UNSIGNED NOT NULL
orders.user_id BIGINT UNSIGNED NOT NULL
The SIGNED vs UNSIGNED difference also causes implicit casting even when the base type is the same. BIGINT and BIGINT UNSIGNED are different types in the query optimizer’s eyes.

Collation: The Subtler Problem #

Collation mismatches are a harder-to-detect form of the problem than numeric type mismatches, because both columns look identical on the surface — both VARCHAR(36) or CHAR(36) — but differ at the collation level.

What Collation Is and Why It Matters #

Collation is the set of rules determining how the database compares and sorts strings. Two strings that are visually “the same” can be considered different by the database depending on the collation used.

Common collations in MySQL:
──────────────────────────────────────────────────────────────
  utf8mb4_general_ci
    → Case insensitive, fast but less accurate comparisons
    → 'a' = 'A', 'e' = 'é' (considered the same)

  utf8mb4_unicode_ci
    → Case insensitive, follows the full Unicode standard
    → More accurate, slightly slower than general_ci
    → 'a' = 'A', but 'e' ≠ 'é'

  utf8mb4_bin
    → Binary comparison, case sensitive
    → Byte-by-byte comparison
    → Most deterministic, good for identifiers/UUIDs
    → 'a' ≠ 'A'
──────────────────────────────────────────────────────────────

Why JOINs with Different Collations Break Indexes #

-- Table structure with different collations
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)
);

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

What happens in the database:

The collation problem in JOINs:
──────────────────────────────────────────────────────────────
  s.user_id uses: utf8mb4_unicode_ci
  u.id      uses: utf8mb4_general_ci

  The database must decide: which collation is used for comparison?

  MySQL picks the collation with higher precedence,
  then converts one column to that collation.

  Conversion = runtime transformation = index can't be used.

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

The difference from numeric type mismatches: this is even less visible because there’s no explicit casting. The query is written correctly, the data is correct, but the optimizer silently performs a conversion behind the scenes.

How to Detect Collation Problems #

-- Check column collations in specific tables
SELECT
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'database_name'
  AND TABLE_NAME IN ('users', 'sessions')
  AND COLUMN_NAME IN ('id', 'user_id');

-- Problematic output:
-- +-------------+-----------+--------------------+--------------------+
-- | COLUMN_NAME | DATA_TYPE | CHARACTER_SET_NAME | COLLATION_NAME     |
-- +-------------+-----------+--------------------+--------------------+
-- | id          | char      | utf8mb4            | utf8mb4_general_ci |
-- | user_id     | char      | utf8mb4            | utf8mb4_unicode_ci |
-- +-------------+-----------+--------------------+--------------------+
--
-- Different collations = JOIN can't use the optimal index

Impact on the Query Planner: Evidence from EXPLAIN #

To understand how big the impact is, look at the EXPLAIN output comparison directly.

Scenario: JOIN Between VARCHAR and INT #

-- Simulated data setup
-- users.id = BIGINT (1 million rows)
-- orders.user_id = VARCHAR(20) (5 million rows)

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

EXPLAIN result (different types):
+----+-------+------+------+---------------------+------+------+------------------+
| 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 scans on both tables. 5 million × 1 million operations in the worst case.

-- After the fix: align the types
-- orders.user_id changed to 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

EXPLAIN result (identical types):
+----+-------+--------+---------------------+---------------------+------+------+-------+
| 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 on the orders table — the index is used, 5 rows read per user instead of 5 million.

Solutions: Fixing Already-Different Data Types #

If the problem already exists in production, there are several approaches depending on how big the migration impact is.

Approach 1: Direct Migration (Small Tables) #

For tables where the data isn’t too large and downtime can be scheduled:

-- ANTI-PATTERN: leave the types different and add CAST in the query
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON CAST(o.user_id AS BIGINT) = u.id;
-- ✗ This hides the problem and still doesn't use the index

-- CORRECT: fix in the schema, not in the query
-- Step 1: add a new column with the correct type
ALTER TABLE orders ADD COLUMN user_id_new BIGINT UNSIGNED;

-- Step 2: populate the new column
UPDATE orders SET user_id_new = CAST(user_id AS UNSIGNED);

-- Step 3: add an index on the new column
CREATE INDEX idx_orders_user_id_new ON orders(user_id_new);

-- Step 4: update queries to the new column (deploy the app)
-- Step 5: drop the old column once sure there are no references
ALTER TABLE orders DROP COLUMN user_id;
ALTER TABLE orders RENAME COLUMN user_id_new TO user_id;

Approach 2: Shadow Columns for Large Tables (Zero Downtime) #

For tables with hundreds of millions of rows that can’t be migrated all at once:

-- Add a generated column as a temporary bridge
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);

-- Temporary JOIN using the shadow column
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id_bigint = u.id;
-- ✓ Index used, performance improves while the migration runs
A generated column as a shadow is a valid transitional solution for zero-downtime migrations, but there must be a plan to remove it after the migration finishes. Redundant columns left uncleaned will add write overhead in the future.

Approach 3: Fix the Collation #

-- Check the current database and table collations
SHOW CREATE DATABASE database_name;
SHOW CREATE TABLE users;

-- Change the collation of the problematic column
ALTER TABLE sessions
MODIFY COLUMN user_id CHAR(36)
CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

-- Make sure the index is rebuilt after the collation change
-- (ALTER MODIFY automatically rebuilds indexes on that column)

-- Verify with EXPLAIN after the change
EXPLAIN SELECT s.id, u.name
FROM sessions s
JOIN users u ON s.user_id = u.id;

Preventing It from the Start: Strict Schema Conventions #

Fixing data type problems after production is far more expensive than preventing them. Here are conventions that should be applied from the start as team standards.

An ID Type Registry: One Standard for All Tables #

-- ✓ The standard that should be documented and followed by all developers:

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

-- Foreign key references to other tables
user_id    BIGINT UNSIGNED NOT NULL
order_id   BIGINT UNSIGNED NOT NULL
product_id BIGINT UNSIGNED NOT NULL

-- UUID / GUID as external identifiers
external_id CHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL

-- ✗ What must be forbidden:
-- INT for FKs referencing tables whose primary key is BIGINT
-- VARCHAR for FKs referencing tables whose primary key is INT/BIGINT
-- CHAR(36) with different collations in tables joined together

A Safe Migration Template #

Every time you create a new table with relationships to other tables, use this template as a checklist:

-- Migration template for a new table with FKs to other tables
CREATE TABLE order_items (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id   BIGINT UNSIGNED NOT NULL,  -- ✓ identical to orders.id
    product_id BIGINT UNSIGNED NOT NULL,  -- ✓ identical to 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)

    -- If FKs are used:
    -- FOREIGN KEY (order_id) REFERENCES orders(id),
    -- FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- Before CREATE:
-- □ Is order_id's type identical to orders.id?
-- □ Is product_id's type identical to products.id?
-- □ Is the collation consistent with the joined tables?
-- □ Are indexes created on the FK columns?

Treat the Schema as an API Contract #

Principle: The schema is a contract, not an implementation detail
──────────────────────────────────────────────────────────────
  An API contract that changes = a breaking change for consumers.
  A schema that changes = a breaking change for all queries.

  Changing an ID column type in production:
    → All queries joining this table are affected
    → All indexes on FK columns must be rebuilt
    → Potential downtime or performance degradation during migration

  The consequences:
    → Designing correct data types up front is far cheaper
    → Schema reviews must cover join column types
    → "We'll fix it later" means "later will be very expensive"
──────────────────────────────────────────────────────────────

Anti-Patterns to Avoid #

After understanding the root cause, here are the concrete anti-patterns most often found during code reviews:

-- ✗ Anti-pattern 1: explicit CAST in JOIN conditions
SELECT * FROM orders o
JOIN users u ON CAST(o.user_id AS SIGNED) = u.id;
-- Solution: fix the column type in the schema, not cast in the query

-- ✗ Anti-pattern 2: subtle type mismatch (INT vs BIGINT)
-- orders.user_id INT, users.id BIGINT
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- Looks correct, but implicit casting happens → index unused
-- Solution: ALTER TABLE orders MODIFY user_id BIGINT UNSIGNED NOT NULL;

-- ✗ Anti-pattern 3: joining UUID as VARCHAR to CHAR
-- sessions.user_id VARCHAR(36), users.id CHAR(36)
SELECT * FROM sessions s JOIN users u ON s.user_id = u.id;
-- VARCHAR and CHAR have different comparison rules
-- Solution: use the same type (both CHAR(36) or both VARCHAR(36))

-- ✗ Anti-pattern 4: joining strings to numbers (the worst)
-- logs.user_id VARCHAR(20), users.id INT
SELECT * FROM logs l JOIN users u ON l.user_id = u.id;
-- The database converts all numbers to strings → full scan + conversion
-- Solution: change logs.user_id to INT NOT NULL

-- ✗ Anti-pattern 5: shadow columns left permanently
ALTER TABLE orders ADD user_id_bigint BIGINT GENERATED ALWAYS AS (...);
-- Then never migrated and the old column never dropped
-- Solution: create an explicit technical ticket to complete the migration

Schema Review Checklist for JOINs #

Use this checklist during new migration code reviews or performance audits:

WHEN CREATING NEW TABLES:
  □ Are all FK columns' types identical to the referenced PK columns?
  □ BIGINT vs BIGINT (not INT vs BIGINT)?
  □ UNSIGNED vs UNSIGNED (consistent)?
  □ Same collation across all columns to be JOINed?
  □ Indexes created on all FK columns?

WHEN REVIEWING JOIN QUERIES:
  □ EXPLAIN already run for this query?
  □ No CAST in ON or WHERE conditions?
  □ EXPLAIN type isn't ALL for both tables (except small tables)?
  □ EXPLAIN key shows the right index (not NULL)?

WHEN PERFORMING PERFORMANCE AUDITS:
  □ INFORMATION_SCHEMA.COLUMNS checked for JOINed columns
  □ CHARACTER_SET_NAME and COLLATION_NAME confirmed identical
  □ DATA_TYPE confirmed identical (not just "similar")
  □ NUMERIC_PRECISION checked for numeric types

Summary #

  • Data type mismatches on JOIN columns silently kill indexes — the query still runs and results are correct, but the optimizer performs implicit casting that makes the index unusable, ending in full table scans.
  • Collation mismatches are as dangerous as type mismatches — two CHAR(36) columns with different collations cause runtime conversions preventing index use during JOINs.
  • Never use CAST in JOIN conditions as a “solution” — it only hides the design problem while ensuring the index stays permanently unused. Fix the schema.
  • BIGINT and INT are different types — so are BIGINT and BIGINT UNSIGNED. Make sure FK types are exactly identical to the referenced PK types, including signedness.
  • Shadow columns are a valid transitional solution — for large tables that can’t be migrated directly, a generated column with the correct type can be a temporary bridge, but there must be a plan to complete the migration.
  • The schema is an API contract — changing ID types in production is an expensive breaking change. Correct design up front is far cheaper than later migrations.
  • Create and follow an ID Type Registry — standardize the types for every identifier category (PK, FK, UUID) and make it part of migration templates and review checklists.
  • Always verify with EXPLAIN after schema changes — make sure the type in EXPLAIN changes from ALL to ref or eq_ref, and key shows the right index.

← Previous: Use EXPLAIN   Next: RAND() →

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