Why Does the Same SQL Run Fast on MariaDB but Slow on MySQL 5.7?

Problem Description: A production database was migrated from MariaDB 10.4 to MySQL 5.7, and a business‑critical SQL query that ran quickly on MariaDB became very slow on MySQL, affecting normal operations.

Environment Check: Execution plans from both systems were captured (images omitted). The test environment showed that the MariaDB plan was optimal, while the MySQL 5.7 plan produced warnings about the id column type or collation conversion, preventing index usage.

Analysis: The sbtest1 table’s id column uses char(32) with collation utf8_bin , whereas sbtest2 uses utf8_general_ci . The collation mismatch causes implicit conversion in MySQL 5.7, breaking the index.

Solution: 1) Change the collation of sbtest1.id to utf8_general_ci , which restores the expected execution plan. 2) Alternatively, apply CONVERT(... USING utf8) on the column in the query to force the correct collation.

Conclusion: MySQL 5.7 detects the collation difference between the two tables and fails to use the index, leading to slow queries. Adjusting the collation or using explicit conversion resolves the issue, while MariaDB’s handling of collations does not impact performance in this case.