# Performance Impact of Optimization Methods on MySQL Document-Based and Relational Databases
## 5 Performance Tests--Results and Analysis
### Update Operation
#### 5.2.2 Updating Multiple Items  
In this case, we updated more clients, depending on the business _id_. In order to obtain the clients of a business, a join between user and client tables is necessary for relational MySQL because there is no direct connection between the business and client tables, and the user table contains a foreign key _business_id_ field. The _deleted_ field is null in all entities; in this case, when we want to update several elements, we add the condition of not updating the already deleted ones. Being nullable, we must add the condition (_deleted_ is null or deleted = false) because, under certain conditions, the deleted field may have been set to false, no longer remaining with the default value of null. Updating multiple elements with a single field and with multiple fields within the elements is presented in Tables 6 and 7, respectively. In relational MySQL, to obtain the entries that we want to modify, we need to establish a join between two tables, while in document-based MySQL, we use the same methods as in the other updates, the only difference being the parameters of the two used methods. In the case of update operations, where more elements are modified, the execution times are significantly improved when the proposed optimization methods are applied, in the case of relational MySQL, as we can see in Figures 6 and 7. The improvement in these times is due to the fact the search operations for the element are much faster due to the created indexes, and the deleted field is no longer nullable, thus escaping the \"or\" conditions. Additionally, in the case of using document-based MySQL, the proposed optimization methods bring in an improvement of the execution times, but not a very significant one, as in the case of relational MySQL. Figure 4: Performance time (ms) for the update one element and one field operation. Figure 5: Performance time (ms) for the update one element but multiple fields operation. The improvement in execution times is mostly due to the added indexes because they help to find the elements that correspond to the set conditions faster. The difference between updating a single field or several is not very big, as we can see in Figures 6 and 7, because the search is the same, and this operation takes most of the actual time of a query. \begin{table}
\begin{tabular}{c} \hline \hline
**Relational MySQL: Update Multiple Items and a Single Field Operation** \\ \hline @Query(value = “update business b inner join user u on b.id = u.business\_id \\ inner join client c on u.id = c.user\_id set c.notes = :notes \\ where b.id = büsinessId and (b.deleted is null or b.deleted is false) \\ and (u.deleted is null or u.deleted = false) and (c.deleted is null or c.deleted = false)”, \\ nativeQuery = true) \\ void updateByBusinessId(@Param(“businessId”) long businessId, @Param(“notes”) String notes); \\ @Param(“address”) String address); \\ \hline \multicolumn{2}{c}{**Document-based MySQL: Update multiple items, a single field operation**} \\ \hline collection.modify(“id = 10001 and (deleted is null or deleted is false)”) \\ and (u.deleted is null or u.deleted = false) and (c.deleted is null or c.deleted = false)”, \\ nativeQuery = true) \\ void updateByBusinessId(@Param(“businessId”) long businessId, \\ @Param(“notes”) String notes, @Param(“clientName”) String clientName); \\ \hline \multicolumn{2}{c}{**Document-based MySQL: Update multiple items, multiple fields operation**} \\ \hline collection.modify(“id = 10001 and (deleted is null or deleted is false)”) \\.set(“user.client.notes”, “plus 10 dollars”).set(“user.client.name”, “[PERSON]’s client”).execute(); \\ \hline \hline \end{tabular}
\end{table} 
Table 6: Update multiple items and a single field operation. Figure 6: Performance time (ms) for the update multiple elements and one field operation.