How does a collation or character-set mismatch between two joined VARCHAR keys hurt the join?

## A string comparison needs one collation Character data carries two properties beyond its length: the character set that says how the bytes encode characters, and the collation that says how two values compare and sort. `=` on strings is a collation-sensitive operation, so a predicate like `o.customer_ref = c.customer_ref` is only well defined once the engine knows which collation to apply. If both columns declare the same one, there is nothing to decide. If they disagree, the engine has to resolve the conflict. ## What engines do when the two sides disagree PostgreSQL is strict about it: when two operands carry conflicting collations, it reports a collation mismatch rather than quietly picking one, so you find out at development time. MySQL takes both routes depending on the case. Two different collations of the same character set with equal coercibility produce `Illegal mix of collations`. Different character sets — the common one being a `latin1` legacy table joined to a `utf8mb4` newer table — are resolved by converting one side to the other's character set, and the statement runs. That is the dangerous outcome, because nothing fails and nothing warns; only the plan and the clock change. Because engines genuinely diverge here, treat the exact behaviour as something to verify against your own engine's documentation rather than something to assume. ## Why the join loses its index A join between two tables typically works by taking a value from one side and looking it up on the other. That lookup uses an index over the second column's stored values, in that column's collation order. Once the engine has to convert or re-collate that column, the predicate is testing a derived value: not the bytes the index stores, and not in the order the index stores them. There is nothing to search, so the engine must produce all the candidate rows and compare them after conversion. On tables where the join was previously a per-row index lookup, this is exactly the "worked in dev, crawls in production" profile — the mismatch costs nothing on a thousand rows and everything on ten million. ## Character set versus collation They fail differently and it is worth keeping them apart. A **character-set** mismatch is a re-encoding of the value and always requires conversion. A **collation** mismatch may leave the bytes alone but changes what counts as equal — a case-insensitive collation matches `'ABC'` with `'abc'`, a case-sensitive one does not. So beyond performance, a collation difference can change the *result*: rows that match under one collation do not match under the other, and a mis-resolved join can silently return the wrong number of rows. That makes "just pick one and cast" a semantic decision, not only a performance one. ## The fix is DDL The repair belongs in the schema. Align the joined columns: same declared type and length, same character set, same collation. `ALTER TABLE` performs it, with the exact syntax varying by engine, and on a large table it rewrites data, so plan it like any other migration — and remember the indexes on that column are rebuilt in the new collation. The tempting shortcut is to write the collation into the query: ```sql -- diagnosis, not a fix: the COLLATE lands on a column, -- so that side's index cannot drive the join SELECT o.id FROM orders o JOIN customers c ON o.customer_ref = c.customer_ref COLLATE utf8mb4_0900_ai_ci; ``` It makes the statement run and it proves the diagnosis, but the expression is on a column, so you have simply chosen which side loses its index. Use it to confirm the cause, then fix the definitions. ## Where mismatches come from They are almost always historical. A table created years earlier under an older server default meets a table created after an upgrade; MySQL 8.0's default collation for `utf8mb4` differs from 5.7's, so tables created on either side of that upgrade disagree by default. Or a column is added by a migration that spells out a character set while its neighbours inherit the database default. Or two systems are merged. Because the divergence is in the DDL and not in any query, code review never sees it. ## Detecting it Read the catalog: the information schema exposes each character column's character set and collation, and one query across the columns you join on will list every disagreement in the schema. Do that as a standing check rather than waiting for a slow join to report it.