# Performance Impact of Optimization Methods on MySQL Document-Based and Relational Databases
## 5 Performance Tests--Results and Analysis  
For each structure of the database, the CRUD operations were performed before and after they were optimized in order to be able to analyze and compare the improvements brought to them by applying optimization. The optimization methods described in Chapter 4 were applied for the existing fields, for the structure of the tables, and in the indexes part. The field optimization-based methods involve optimization in terms of storage, the space occupied by data storage, and how clean the data are when stored and used. The \begin{table}
\begin{tabular}{c c} \hline \hline \multicolumn{2}{c}{**Relational MySQL: Create Indexes**} \\ \hline Version 1: by adding them by alter table add index: \\ ALTER TABLE ‘user’ ADD INDEX’ user\_created\_at\_index’ (‘created\_at’) \\ Version 2: by adding them directly to entities: \\ @Table (indexes = @Index (columnList = “created\_at”))) \\ \hline \multicolumn{2}{c}{**Document-based MySQL: Create indexes**} \\ \hline col.createIndex(“createdAtt Index”, ”\(\backslash\)”fields\(\backslash\)”: \\ [\(\backslash\) ‘field\(\backslash\)’: \(\backslash\)’s\_created\_at\(\backslash\)’, \(\backslash\)’type\(\backslash\)’: \(\backslash\)’INTEGER’, \(\backslash\) ’required\(\backslash\)’: true]]]”) \\ \hline \hline \end{tabular}
\end{table} 
Table 1: Index creation. \begin{table}
\begin{tabular}{c c} \hline \hline \multicolumn{2}{c}{**Relational MySQL**} & **Document-Based MySQL** \\ \hline _deleted_ field on each table (3 indexes) & _deleted_ field on each document \\ _created\_at_ field in user and client table & _created\_at_ field on each document \\ _client name_ & _client name_ \\ _business name_ & _business name_ \\ user table: _id_, _deleted_, _created\_at_, and _business\_id_ & compound index: _id_, _deleted_, _created\_at_, and _user.id_ \\ user table: _id_, _deleted_, and _business\_id_ & compound index: _id_, _deleted_, and _user.id_ \\  & on _id_ field \\  & on _user.id_ field \\ \hline \hline \end{tabular}
\end{table} 
Table 2: Added indexes. index-based optimization methods have an impact on improving the response times within an application. Both methods are very important to achieve in order to obtain a stable and fast database. As optimizations' impact can best be seen on a large number of items, operations were applied on a range between 10,000 and 5,000,000,000 entries. These data were generated absolutely randomly, with a _for_ statement with which the desired number of elements was generated (i.e., _for(int a = 1; a\(\leq\)10000; a++)//generate entries)_, the _id_ being the value from the _for_ statement. Additionally, the values of the fields in the entities were generated randomly, except for the _created_at_ field, which was generated as follows: for each quarter of the elements, we set it as the current date--x days, \(x\) being the value of for; the rest of the elements either remained null or were generated with a random string (i.e., _RandomStringUtils.randomAlphabetic(10)_--generates a 10-letter random string). Creating a session within which a schema is created and then a collection where the data are to be saved and processed using document-based MySQL is conducted as follows:  
_SessionFactory sFact = new SessionFactory ();_  
_Session session = sFact.getSession (\"mysqlx://name:password@localhost:33060\");_  
_Schema schema = session.createSchema (\"application\", true);_  
_Collection collection = schema.createCollection (\"business\", true);_  
In relational MySQL, in the _application.yml_ of the application, a file containing the application settings, we have the following configuration:  
_datasource:_  
_url:jdbc:mysql://127.0.0.1:3306/databaseName?useUnicode=_  
_true@use|DBCCompliantTimezoneShift=_  
_true@useLegacyDatetimeCode = false@serverTimezone = UTC_  
_username: username_  
_password: password_  
All the tests presented further were conducted on a computer with the following configuration: Windows 10 Home 64-bit, processor Intel Core i7-1065G7 CPU @1.50 GHz, 16 GB RAM, and a 512 GB SSD.