Performance Benchmarking on
Many-to-Many (M:N) Relationships
with Variations in Indexing and
Sizes of Object Databases

RQL Queries with Variations in Indexing and Object Database Sizes

12 Object Databases Tested in MySQL

The search conditions used for performance testing of the object databases described above are shown in the image below.

In all tests, the same search conditions are used, which searches M1 class objects from the database. The red X marks indicate the classes N3, M4, and S4, where search conditions are applied to the value fields within the data structure of class M1. The paths from the root class M1 to the search condition classes are shown with red dashed lines.

The found M1 objects are returned as complete data structures, including all objects that are reachable from the M1 root class. This of course includes classes N3, M4, and S4, which have the value search conditions, but also objects from classes M2, M3, and N4, which are reachable from class M1. All reachable classes from class M1 are marked with blue X marks.

Blue arrow relationships between classes are M:N (many-to-many). All M:N relationships have 1-3 child objects at random.

Black arrow relationships between classes are M:1 (many-to-one).

The search conditons for the three value fields in classes N3 (value=976), S4 (value=50) and M4 (value=770) are represented as a RQL query expression below.

(m2.n3.value = 976, m2.m3.s4.value = 50, m2.n3.m4.value = 770)                        

The Java source code that executes the RQL query expression for the class M1 in the object database is shown below.

//Variable rdb is the opened RootDB object database.
//RootStore object is created for the M1 class.
RootStore r2 = rdb.getRootStore("fi.rootrql.ex100Knx.M1");
Query query =  Query.createQuery()
        		.addQ1Query(r2,
                    "(m2.n3.value = 976," +
                    " m2.m3.s4.value = 50," +
                    " m2.n3.m4.value = 770"
                    + ")");
List<M1> list = query.queryQ1Objects();
rdb.close();

An example of query results is shown below, containing the complete data structures of the retrieved four M1 objects. Note that, for example, the expanded [0]=M2 object has a list of three M3 objects in its field m3 and only the expanded object [2]=M3 has the searched value 50 in its s4=S4 object. Thus, the lists may contain objects that do not match the search criteria, but are included in the results because the returned M1 objects contain their complete data structures.

Query Result Example: The four found M1 objects are returned in a list, with the first M1 object expanded to display the value fields where the search conditions were applied.

The twelve object databases with variations in indexing and object counts were all tested using the same query described above. The results categorized by database size and indexing strategy are presented in the table below.

Note the column 'MariaDB/MySQL Search Coeff.,' which compares the values in the 'Search' column to those provided by the MariaDB database.

The number of M1 objects found is 4 in the smallest databases with 12 x 100K objects. The number of objects found increases with the size of the database, so that a database with 12 x 2M objects has 80 matches for the query conditions.

The focus of the results is of course on execution times and their changes depending on the size of the database and the indexing used. Three execution times are shown, which are the search time of the query (Search), the creation time of found M1 objects (Create Objects), and the total time (Total) summing up these two times. The results show that the performance can be evaluated solely on the basis of the search time of the query (Search), since the object creation time (Create Objects) per result object seems to be approximately the same in all cases.

However, MySQL's performance can be assessed and compared to the results of the same tests conducted on MariaDB, which are presented in a similar table at this link. To facilitate comparison, the MySQL results table includes an additional column, MariaDB/MySQL Search Coeff., which quantifies the relative performance of searches in MySQL compared to MariaDB. A value greater than 1 indicates that MySQL performs better, while a value less than 1 signifies that MariaDB is faster. For example, a coefficient of 2.0 means that MySQL executes the same search twice as fast as MariaDB, whereas a coefficient of 0.5 indicates that MariaDB is twice as fast as MySQL.

According to the results, MySQL matches or slightly exceeds MariaDB's performance in only three tests. In all other cases, MariaDB is significantly more efficient, delivering more than twice the performance in several tests. This disparity may be partly due to MariaDB being a significantly newer database compared to MySQL.

Summary

MySQL appears to be a less efficient platform for RootDB compared to MariaDB, with this advantage remaining consistent regardless of database size. MariaDB's advantage is particularly evident in its ability to write objects at approximately twice the speed of MySQL when no indexing or only single-field indexing (on value fields) is applied. However, when only relationships are indexed, MySQL outperforms MariaDB with approximately 1.25× better performance. Overall, considering writing speed alone, it is evident that MariaDB generally offers better performance than MySQL.

MariaDB also excels in database queries, with its performance often being nearly double that of MySQL. MySQL outperforms MariaDB in only three out of twelve tests, but the differences are negligible. When combined with MariaDB's overall superior data writing speed, this supports the conclusion that MariaDB is undoubtedly a better choice for storing and retrieving RootDB objects.

RootDB demonstrates a significant advantage in that, even without indexing, query execution times scale roughly linearly with database size, even when queries traverse multiple many-to-many relationships. This near-linear growth ensures that object database performance remains predictable and reliable without indexing. Although query times in large databases can become considerably longer, there is no risk of queries freezing due to excessive complexity.

Naturally, the complexity of the object model and the number of relationships within the database influence execution time. However, indexing relationships alone already yields significant performance improvements. By additionally indexing the fields referenced in queries, RootDB can meet even the most demanding requirements for fast query execution in large databases.

Based on publicly available information prior to the test results presented here, it is generally known that MySQL and MariaDB differ in performance due to their design focus and optimization. MariaDB often outperforms MySQL in write-intensive operations, particularly when no indexing or minimal single-field indexing is applied, achieving speeds up to twice as fast. For database queries, MariaDB generally exhibits better performance, with query execution times often being nearly double those of MySQL. However, MySQL occasionally performs better in specific scenarios, such as when indexing relationships, though these differences are typically minor. MariaDB's optimizations for modern hardware and its advanced storage engines contribute to its superior efficiency, making it the preferred choice for high-performance writing and querying, especially with complex or large datasets.

The RootDB test results presented above support and reinforce this general perception of the performance differences between MariaDB and MySQL. Notably, the relationship between the performance differences of these database products remains consistent when storing and retrieving objects, further validating this established understanding.