...
In version 8.0 of MySQL and version 10.2 of MariaDB, Common Table Expressions (CTEs) were introduced, allowing users to create named temporary result sets that only exist within the scope of a single statement. This advanced feature enhances query readability and enables the use of CTEs in a recursive manner, which has proven ideal for building recursive closure trees—a trees - a common need for representing organizational hierarchies within theCoreOne Suite. Starting with CoreOne Suite 8.0, we leveraged CTEs for various purposes, including determining a user's access rights based on their organizational assignments. Security roles, such as the https://itsense.atlassian.net/wiki/x/AYDNk, the https://itsense.atlassian.net/wiki/x/AYDNkas well as many features of the https://itsense.atlassian.net/wiki/x/CABGFw, were built using views that utilize CTEs.
...
Further investigation revealed that the query execution plan for these views changed randomly on MySQL 8.x instances. For example, with 1,000 organizational records, the primary SELECT statement in the view initially returned 1,000 rows, which was correct. However, during periods of degraded performance, the same query returned tens of thousands of rows, leading to a significant increase in execution time—from time - from approximately 100 ms to 10 seconds.
...