Issue(s)

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 common need for representing organizational hierarchies within the CoreOne 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 CoreOne Suite Organization Unit Employee Manager, the CoreOne Suite Organization Unit Employee Manageras well as many features of the https://itsense.atlassian.net/wiki/x/CABGFw, were built using views that utilize CTEs.

The data returned by these views depends on the data stored in the system. Since the views are recursive, several factors affect performance, such as the number of organizational units, the structure of the hierarchy (whether it is a flat list or a complex tree), among others.

In the production environment, a significant drop in overall system performance was observed intermittently. This performance degradation could not be consistently reproduced but occurred frequently enough to impact operations.

Cause(s)

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 approximately 100 ms to 10 seconds.

As it turns out, MySQL's implementation of CTEs is subject to ongoing improvements and performance fixes, as highlighted in multiple release notes:

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-19.html

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-23.html

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-25.html

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-27.html

These release notes indicate continuous improvements and adjustments, which may have caused variations in query performance.

Solution

Migrate the database to MariaDB 10.6.x or higher. MariaDB's implementation of CTEs appears to be both measurably faster and, more importantly, consistent in its performance. This stability makes MariaDB a more suitable choice for applications requiring predictable query execution times, especially for recursive queries such as organizational hierarchy lookups.