# Performance Impact of Optimization Methods on MySQL Document-Based and Relational Databases
## 5 Performance Tests--Results and Analysis
### Select Operation
#### 5.3.4 Different Selects with Multiple Joins and Different Functions  
In the case of relational MySQL, some functions (_group by_ or _order by_) can have an important impact on the execution time for a query. To better see the impact that these functions can have, how much they increase the execution times, and how optimizations can improve the database performance, two select operations with multiple joins that use these functions were created. Through these select operations, we present how these functions are used in document-based MySQL and their impact on this database. A select operation that uses multiple joins and different functions by which we obtain all the names of a user's clients ordered after their creation date is presented in Table 11. \begin{table}
\begin{tabular}{c} \hline \hline \multicolumn{1}{c}{**Relational MySQL: Select with Multiple joins and different functions**} \\ \hline @Query(value = “select group\_concat(c.name SEPARATOR,’) from user u inner join \\ client c on u.id = c.user\_id where u.id = “userId and (u.deleted is null or u.deleted = false) \\ and (c.deleted is null or c.deleted = false) order by c.created\_at”, nativeQuery = true) \\ String findClientsByUserName(@Param(“userId”) long userId); \\ \hline \multicolumn{1}{c}{**Document-based MySQL: Select with multiple joins and different functions**} \\ \hline String res = collection.find(“user.id = 10,001 and user.client.name is not null and deleted \\ is null or deleted is false”).orderBy(“created\_at”).fields(“user.client.name as \\ clientName”).execute()(.fetchAll)(.stream)(.map(dbDoc -> dbDoc.get(“clientName”) \\.toFormattedString)().collect(Collectors.joining(”,”)); \\ \hline \hline \end{tabular}
\end{table} 
Table 11: Select operation with multiple joins and different functions. Figure 10: Performance time (ms) for the select operation with two joins. In relational MySQL, there is the _group_concat()_ function that groups and concatenates the given field as a parameter, using the specified separator, finally returning a string, being a way to obtain the names of all clients. In document-based MySQL, there is no such a function; therefore, using the _fields_ method, we specify only the fields we want to take from the whole object; in this case, the client name and the list are formatted as a string, separating the fields with the same separator as in the case of the other type of database. In the end, the two operations will return exactly the same result, in exactly the same format. To select the names of a user's clients, in relational MySQL, we used the _group_concat()_ function which groups the clients and concatenates, at the same time, the specified field, in this case, their names. In the case of relational MySQL, the response times increase significantly with the number of elements when optimization methods are not applied, as shown in Figure 11. This is largely due to the order by which the elements are filtered, with grouping and concatenation being relatively fast. In document-based MySQL, this function does not exist; therefore, a selection was conducted, and using the _fields_ method, we selected only the desired field from the whole object, in this case, the client's name, finally obtaining a string identical to the one in relational MySQL. The use of _order by_ clause in this case does not significantly increase the execution times because the elements were already filtered when their order was established, the order being established by the number of elements found. There are significant differences between the response times before and after optimization, especially in the case of relational MySQL, as can be seen in Figure 11. In the case of document-based MySQL, the differences between response times before and after optimization are not very large. By applying optimization methods, we obtained an improvement of over 50% in the case of using document-based MySQL, and over 80% in the case of relational MySQL, when the number of items increased over one million.