Why MySQL 8.0 Queries Fail After Upgrading from 5.7: Charset & Collation Pitfalls

Background

Many projects still run on MySQL 5.6/5.7 where most tables use utf8 (default charset). After migrating to MySQL 8.0, new tables are created with utf8mb4 by default, leading to a mixture of character sets within the same database.

Problem Description

When a database contains tables with different character sets ( utf8 vs utf8mb4) and collations, joins between them may fail to use indexes, causing the optimizer to fall back to hash joins or full scans. This is especially evident when the driver (left‑most) table uses a charset that differs from the joined table.

Typical Table Definitions

CREATE TABLE `orders` (
  `ordernumber` varchar(200) CHARACTER SET utf8 NOT NULL,
  `orderDate` date NOT NULL,
  `requiredDate` date NOT NULL,
  `shippedDate` date DEFAULT NULL,
  `status` varchar(15) CHARACTER SET utf8 NOT NULL,
  `comments` text CHARACTER SET utf8,
  `customernumber` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`ordernumber`),
  KEY `customerNumber` (`customernumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `payments` (
  `customerNumber` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `checkNumber` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `paymentDate` date NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`customerNumber`,`checkNumber`),
  KEY `idx_payment` (`paymentDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

The orders table is stored as utf8mb4, while payments remains utf8mb3.

Query Experiments

Two left‑join queries were executed, swapping the driver table:

EXPLAIN SELECT *
FROM payments AS p
LEFT JOIN (SELECT * FROM orders) AS o ON p.customerNumber = o.customerNumber
WHERE paymentdate > '2005-01-01';

EXPLAIN SELECT *
FROM orders AS o
LEFT JOIN (SELECT * FROM payments) AS p ON p.customerNumber = o.customerNumber
WHERE paymentdate > '2005-01-01';

When payments (utf8mb3) is the driver, the optimizer cannot use the index because the collations differ, so it resorts to a hash join. Swapping the driver to orders (utf8mb4) changes the execution plan and improves performance.

Profiling Results

Execution counts showed a clear difference: with payments as driver, executing was ~700; with orders as driver, it rose to ~1742, indicating higher cost.

Charset Conversion Fix

Converting both tables to the same charset ( utf8 or preferably utf8mb4) resolves the index‑loss issue.

ALTER TABLE orders CONVERT TO CHARACTER SET utf8;

ALTER TABLE payments CONVERT TO CHARACTER SET utf8mb4;

After conversion, subsequent profiling showed that queries run efficiently regardless of which table is the driver.

Conclusion

The migration from MySQL 5.7 to 8.0 often leaves many tables in utf8mb3 while new tables default to utf8mb4. This charset mismatch can break index usage in joins, leading to slower queries. The safest approach is to standardize all tables to utf8mb4 (or at least the same charset) before or immediately after migration, ensuring consistent collations and optimal query performance.