A collation mismatch stops a join using an index

8.0 defaults to utf8mb4_0900_ai_ci and a table converted from 5.7 is almost certainly utf8mb4_general_ci — and joining two columns with different collations cannot use an index on either.

SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'shop' AND collation_name IS NOT NULL
GROUP BY collation_name;

-- the symptom in EXPLAIN:
-- Using where; Using join buffer (Block Nested Loop)

The block nested loop in a plan that used to use an index is the tell, and the cause is a table created after an upgrade joining one converted before it. Setting the collation explicitly in every CREATE TABLE rather than inheriting the server default is the discipline that prevents it. Auditing the whole schema afterwards is one query, and doing it as part of the 8.0 upgrade rather than six months later saves the investigation.