The slowest query I debugged last year had perfect indexes on both sides of its JOIN. The join column was indexed in table A, indexed in table B, statistics fresh, table sizes moderate. EXPLAIN still showed a full scan on the inner table with the index sitting unused in possible_keys. The two columns were both VARCHAR(64). One was utf8mb3. The other was utf8mb4.
That single-word difference forced MySQL to convert every inner-table value before comparing, and an index on the original bytes is useless for finding converted values. No error, no warning in most drivers, no deprecation note in the slow log. Just a query that got 200x slower than it should have been, on a schema that looked healthy in every review.
This failure mode deserves the name silent index killer, because unlike a missing index it does not show up in schema review — every column involved is indexed. These notes cover how the coercion rules actually work, why upgraded fleets are full of this problem, the numeric variant that is even nastier, and the information_schema queries I run to find every landmine before it detonates.
How a comparison chooses a collation
When MySQL compares two string expressions, it has to pick one collation to compare with. The rules are coercibility-based: an explicit COLLATE clause beats a column collation, a column collation beats a literal, a literal beats NULL, and so on. The case that matters for JOINs is column versus column, where both sides have the same coercibility and neither wins by rank.
Two outcomes are possible, and they fail very differently:
- Same character set, different collation — say utf8mb4_general_ci against utf8mb4_0900_ai_ci. MySQL cannot pick a winner and throws an error: Illegal mix of collations. Painful, but loud. You find out on the first query.
- Different character sets — utf8mb3 against utf8mb4, or latin1 against utf8mb4. Here the repertoire rules allow a fix-up: the side with the narrower character set is converted to the wider one, and the comparison proceeds. Silently.
The silent case is the killer. Conversion is applied to the column, and a B-tree stores the column's original encoded values in the column's own collation order. Once the join predicate becomes a function of the column — CONVERT(inner_col USING utf8mb4) — the index on that column can no longer be used for lookups. Whether the scan lands on your big table or your small one depends on which side has the narrower charset and which side the optimizer wanted to probe, which is why the same schema mismatch can be invisible for months and then catastrophic after a join order change.
Why upgraded fleets are full of it
Almost every instance of this I have found traces back to an upgrade or a migration seam. MySQL 5.7's default character set was latin1, and the utf8 alias meant the three-byte utf8mb3. MySQL 8.0 switched the default to utf8mb4 with utf8mb4_0900_ai_ci. So the standard fleet history — schema created on 5.5 or 5.7, upgraded in place, new tables added after the upgrade — yields old tables in latin1 or utf8mb3 with utf8_general_ci and new tables in utf8mb4 with 0900 collations. Every JOIN across that generational boundary is either erroring or silently converting.
The dumps make it worse. A logical dump-and-restore preserves per-table and per-column charsets, so the mix survives every migration unless someone deliberately converts. And because column-level charset overrides table defaults, a table can report utf8mb4 in SHOW CREATE TABLE's trailer while one legacy column inside it is still utf8mb3. You cannot audit this by eyeballing table defaults; you have to query columns. Choosing which utf8mb4 collation to standardize on — general_ci, 0900_ai_ci, 0900_as_cs — is its own decision with real semantics attached, and I wrote that up separately in the utf8mb4 collation choices notes.
The numeric cousin: string-to-number coercion
The same class of bug exists without any charset involved. Compare a string column to a numeric value — a VARCHAR order reference joined to a BIGINT id, or WHERE external_id = 12345 against a VARCHAR external_id — and MySQL converts both sides to double-precision floats. The index on the string column is dead, exactly as with charset conversion, and you collect two bonus hazards: values like '12345' and '12345.0' and ' 12345' all compare equal to 12345, and above 2^53 the double rounds, so distinct large numeric strings can silently match the same number.
This one is at least visible if you look: the numeric coercion usually produces per-row truncation warnings that show up in Handler stats and in the slow log with warning counts. The fix is never a cast in the query — it is fixing the type at the schema level, or at minimum quoting the literal so the comparison stays in string territory when the column side is the indexed one.
Finding every mismatch before it finds you
The first query is the inventory: what charset and collation combinations exist per schema, and which tables carry more than one.
SELECT c.table_schema,
c.character_set_name,
c.collation_name,
COUNT(*) AS columns_affected,
COUNT(DISTINCT c.table_name) AS tables_affected
FROM information_schema.columns AS c
WHERE c.table_schema NOT IN
('mysql', 'information_schema', 'performance_schema', 'sys')
AND c.character_set_name IS NOT NULL
GROUP BY c.table_schema, c.character_set_name, c.collation_name
ORDER BY c.table_schema, columns_affected DESC;A healthy schema returns one row per schema. More than one row means the boundary exists somewhere; the next query finds the likely join seams by pairing string columns that share a name — which is how implicit application-level joins are usually spelled — but disagree on collation.
SELECT a.table_name AS table_a,
a.column_name,
a.collation_name AS collation_a,
b.table_name AS table_b,
b.collation_name AS collation_b
FROM information_schema.columns AS a
JOIN information_schema.columns AS b
ON a.table_schema = b.table_schema
AND a.column_name = b.column_name
AND a.table_name < b.table_name
WHERE a.table_schema = 'app'
AND a.collation_name IS NOT NULL
AND b.collation_name IS NOT NULL
AND a.collation_name <> b.collation_name
ORDER BY a.column_name, a.table_name;Name-matching is a heuristic — it misses joins between user_id and owner_id — so I supplement it two ways. Columns joined through actual foreign keys are safe by construction, since InnoDB requires matching charset and collation to create the FK; the risk lives entirely in FK-less joins. And for the type-coercion variant, pair the same query's logic against DATA_TYPE instead of COLLATION_NAME to find same-named columns where one side is a string type and the other is numeric.
Fixing it without an outage
The tactical tourniquet is an explicit COLLATE or a CONVERT placed on the side that is not the index you need, which at least moves the conversion off the indexed column while you schedule the real fix. It is a patch per query, it rots, and I treat it as strictly temporary.
The real fix is ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE your_standard, table by table, oldest first. That rewrite takes locks and time proportional to table size, so on anything that matters I run it through an online schema change tool instead of a raw ALTER; the operational tradeoffs are in the gh-ost vs pt-osc field notes. Two pre-flight checks that have saved me: utf8mb3-to-utf8mb4 conversion grows the maximum byte length of every string column, which can overflow index prefix limits on tables still using the old compact row formats, and any application still setting a connection charset of utf8 will keep writing three-byte-max data into your converted columns until you fix the client configuration too.
Keeping it fixed — and where MonPG fits
Schema convergence decays. One new service scaffolded with the wrong default, one table copied from a legacy dump, and the mix is back. The durable defense is running the inventory queries in CI or a scheduled check, plus watching the runtime signal: a query family whose examined-rows count jumps orders of magnitude after a deploy that only touched a JOIN is this bug until proven otherwise.
On the tooling side, the honest status: MonPG is a PostgreSQL monitoring platform, and MySQL support is under construction — it does not watch MySQL databases today. Statement-digest history and examined-versus-returned row tracking for MySQL, the exact evidence that catches a silently killed index, is what we are building; the MySQL monitoring (coming soon) page tracks progress and takes requests. If you also run PostgreSQL — where the equivalent trap is mismatched types and implicit casts across join columns — that side of MonPG is live and you can start there today.