A query that ran in two milliseconds last week now takes nine seconds, and EXPLAIN shows a full scan on a table whose join column is indexed. The index exists. The statistics are current. The plan still refuses it.
The usual cause is a collation mismatch: the two sides of the join predicate are stored with different collations, so MySQL cannot compare them directly. It converts one side, the converted side is no longer the indexed expression, and the index drops out of the plan. Nothing is logged. The only symptom is the plan.
This is the most common performance bug we find that has nothing to do with query shape, and it is almost always the residue of a half-finished character set migration.
How a schema ends up with three collations
MySQL's defaults changed, and tables remember the default that was in force when they were created:
- Old tables created under 5.x default to
utf8mb3(historically namedutf8) withutf8mb3_general_ci. - Tables converted to utf8mb4 in the 5.7 era usually got
utf8mb4_general_ci, orutf8mb4_unicode_ciif someone was paying attention. - Tables created on 8.0 default to
utf8mb4_0900_ai_ci, because the server default changed.
All three can coexist happily until a query joins across them. Add one ORM that emits CREATE TABLE without an explicit collation, one restored dump from an older server, and one new microservice's table, and you have a schema with three rules for comparing strings.
utf8mb3 is also a correctness problem before it is a performance problem: it stores at most three bytes per character, so it cannot represent emoji or any character outside the Basic Multilingual Plane. Those inserts either error with Incorrect string value or, under a loose SQL mode, get truncated. utf8mb4 is the only sensible target.
Prove the mismatch before you change anything
Start with the schema, not the query. This lists every character column that is not on your intended collation:
SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND collation_name IS NOT NULL
AND collation_name <> 'utf8mb4_0900_ai_ci'
ORDER BY table_name, column_name;
Then find the joins that actually cross a boundary. The cheapest confirmation is EXPLAIN plus the optimizer's own warning:
EXPLAIN SELECT o.id FROM orders o JOIN customers c ON c.email = o.email WHERE o.created_at > '2026-01-01';
SHOW WARNINGS;
The rewritten query in the warning shows the conversion explicitly — a convert(...using utf8mb4) wrapped around one side of the predicate. That wrapper is the index being thrown away. Two further details worth knowing:
- The conversion follows coercibility rules, so it is not always the side you expect. A literal, a column, and a user variable all have different coercibility, and the lower-coercibility side wins.
- The same mechanism bites with a mismatch of character set or collation. Two
utf8mb4columns withgeneral_ciand0900_ai_cicannot use each other's indexes either.
A quick and honest check on a suspect pair is to force the comparison and watch the plan change:
EXPLAIN SELECT o.id FROM orders o
JOIN customers c ON c.email = o.email COLLATE utf8mb4_0900_ai_ci;
If the plan flips to a ref lookup on the index, you have your answer. Do not ship that COLLATE clause as the fix. It works, it is invisible to the next engineer, and it only papers over one of the queries that will hit the same wall.
Picking the target collation
For most new work on 8.0 and later, utf8mb4_0900_ai_ci is the right target: it implements a current Unicode collation, it is the server default, and it is measurably faster than the older collations because of a no-pad, optimized comparison path.
Three cases argue otherwise:
- You still have 5.7 servers in the topology, or replicate to one.
utf8mb4_0900_ai_cidoes not exist before 8.0, and replicating DDL that names it will break the replica. Useutf8mb4_unicode_ciuntil the whole topology is on 8.0, then convert once. - You depend on PAD SPACE comparison semantics. The
0900collations are NO PAD, so'a'and'a 'compare as different where they previously compared equal. This changes uniqueness and lookup behaviour for any column with trailing spaces. Check before, not after. - You need case-sensitive or accent-sensitive comparison.
utf8mb4_0900_as_csexists; pick it deliberately and apply it to the whole related column set.
Whatever you choose, write it down and enforce it. The migration is worth doing once.
The index length ceiling
utf8mb4 reserves four bytes per character instead of three, so the declared index length of a VARCHAR column grows by a third. On modern InnoDB with DYNAMIC row format, the per-column index limit is 3072 bytes, which means a VARCHAR(768) is fine. The pain is on older tables still in COMPACT row format with a 767-byte limit: a VARCHAR(255) that fit under utf8mb3 (765 bytes) will not fit under utf8mb4 (1020 bytes).
The options, in order of preference: confirm the table is DYNAMIC (SELECT row_format FROM information_schema.innodb_tables) and convert it if not; shorten the column to what the data actually needs; or use a prefix index, accepting that it cannot serve ordering or covering reads. A quick SELECT MAX(CHAR_LENGTH(col)) usually shows that a VARCHAR(255) email column holds nothing over 60 characters, and the honest fix is to shorten the declaration.
Migrating without a maintenance window
ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 rewrites the whole table. It is not an INSTANT operation and it is not free. On a small table, run it. On a large one, treat it like any other full rebuild:
- Set the database and server defaults first so new tables stop adding to the problem:
ALTER DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;pluscharacter_set_serverandcollation_serverin the configuration. This changes defaults only — existing tables are untouched. - Fix the connection layer at the same time. A client connecting with
utf8mb3re-introduces a conversion at the session level no matter how the columns are declared. Set the driver's charset explicitly, and confirm withSHOW VARIABLES LIKE 'character_set_%'on a live connection rather than trusting configuration. - Convert large tables with an online schema change tool, the same way you would any other rebuild — a binlog-tailing copy tool where there are no foreign keys, a trigger-based one where there are. Both throttle on replica lag; a full character set conversion of a large table will otherwise stall replication for the whole rebuild.
- Convert both sides of every join together, in one change window. A schema half-converted is worse than one not converted at all: you have all the rebuild cost and you have introduced new mismatches on the pairs you have not reached yet. Group the work by join graph, not by table size.
- Re-check the plans afterwards. Compare
EXPLAINfor the queries you captured in step one. Conversion changes plans; almost always for the better, occasionally not, and you want to know which within the hour rather than at the next traffic peak.
What to verify before you call it done
- No column in the schema reports a collation other than the target (the
information_schema.columnsquery above should return zero rows). SHOW WARNINGSafterEXPLAINon your top join queries shows noconvert(... using ...)wrappers.- A round-trip insert and select of a four-byte character — an emoji is the easiest test — succeeds through the application, not just through the client.
- Row counts and checksums match on the converted tables, and replicas are caught up.
The whole exercise is unglamorous and it tends to be deferred indefinitely, because the schema is not visibly broken. It gets found during an upgrade, when the 8.0 default collation meets a 5.7-era schema and a join that used to be fast is not. Doing it deliberately, ahead of the upgrade, is a few hours of rebuild time. Doing it reactively is an incident.
If you have a schema with mixed collations and a set of joins you are not sure about, we will read the schema and the plans and tell you which conversions actually matter and in what order. Most of that work is read-only.