0

Hello MySQL Community,

I wanted to share some observations regarding the performance differences between MySQL 5.7 and MySQL 8.0, specifically related to join algorithms.

In a recent project, I noticed a significant performance difference when running a complex query involving multiple joins using regular expressions on MySQL 5.7 and MySQL 8.0. So the join is not like Join TableA ON (TableX.table_a_id = TableA.id) instead it is like Join TableA ON (TableX.details REGEXP CONCAT("\\table_a_id[[:space:]]*:[[:space:]]*", TableA.id, "\\b")) The same query ran noticeably faster on MySQL 5.7 compared to MySQL 8.0.

Upon investigating, I found that the EXPLAIN output showed that MySQL 5.7 was using the Block Nested Loop algorithm for joins, while MySQL 8.0 was using the hash join algorithm. This difference in join algorithms seems to be the primary reason for the performance difference.

While MySQL 8.0 has many improvements and new features, including security enhancements and roles for better access control, it appears that for this specific type of query, MySQL 5.7's Block Nested Loop algorithm performs better.

It's important to note that the performance of SQL queries can be highly dependent on the specific data and query structure, and this observation may not apply to all scenarios. However, for those who are running complex queries involving multiple joins and regular expressions, it may be worth testing the performance on both MySQL 5.7 and MySQL 8.0.

I'm sharing this in the hope that it might be helpful to others who are considering upgrading to MySQL 8.0 or who are experiencing performance issues after upgrading. As always, thorough testing and performance analysis is recommended before making any major changes to your database system.

If anyone has experienced similar issues or has any insights into the differences in join algorithms between MySQL 5.7 and MySQL 8.0, I would love to hear your thoughts.

Best Regards,

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.