# Performance Impact of Optimization Methods on MySQL Document-Based and Relational Databases
## 3 Method and Testing Architecture  
The testing architecture implies the implementation of two applications in Java (Java, version 15.0.2, developed by Oracle Corporation, Canada), using Intellij IDEA 2020.2.4 (Community Edition) (Intellij IDEA, version 2020.2.4, build #IC-202.8194.7, built on 24 November 2020, JetBrains, Prague, Czech Republic, known as Intellij IDEA), with runtime version 11.0.9+11-b944.49 amd64 [13], one for relational MySQL, and one for document-based MySQL. The version of MySQL used was 8.0.25 (developed by Oracle Corporation, United States). Even if the same database engine was used, the structure of the two databases isdifferent. In relational MySQL, the database structure is based on tables representing the created entities that contain columns, representing their fields; in document-based MySQL, the structure is in the form of a document containing information in JSON format, in the key-value form. The structure of the relational database used in this paper is composed of 3 entities: business, user, and client, as shown in Figure 1.  
This structure is based on 3 entities and can be used to store clients of different small service providers. It can be developed to store both their services and customer appointments, becoming an agenda. In the case of relational MySQL, the application contains entities, their repositories where the queries are written, one service class for each entity, a class dealing with the logic of the application, and the main class where performance tests are conducted. In the case of document-based MySQL, the application contains only one class for each object and the main class where the tests are conducted. To respect the same structure in both applications, separate classes were created where the logic applied was written, and we just called them from the main class. In the case of relational MySQL, queries were executed from repositories that have the Repository annotation [23], and each query was written in the parameter query annotations applied to each method in this class. In the case of the application that uses document-based MySQL, there are predefined commands by the MySQL Connector Java version 8.0.25 library, and we called methods that take a query as a parameter, similar to that of relational MySQL. For document-based MySQL, the structure is described in Figure 2:  
Figure 1: Relational database structure. Each document can contain the information of a single customer, which also includes the information of the user to whom it belongs as well as the information of the business to which the user belongs. In this way, each document is independent, although the information is duplicated and takes up more storage space. In the case of this type of database, the structure can be dynamic, new fields can be added, and existing fields can be deleted without affecting the other documents in any way. To keep a structure as close as possible to that of relational-based MySQL, we used objects inside which we defined all the fields presented in Figure 1, except for the deleted and _created_at_ fields that were defined per document, only in the business object. The column _created_at_ tells us when the entity was created and can be used to order the items after their creation in ascending or descending order, and the column _deleted_ can be used to mark an entry as deleted before being permanently wiped. Datetime fields were saved in the database in UTC in both applications. The most important optimizations are those on the database structure and indexes. In this paper, the fields in the tables were optimized by trying to have as few fields as possible that can be null, with their length being as small as possible, depending on the needs. To improve the structure of the database, normalization was applied on it; thus, instead of storing the profession as a string for each user, a separate table was created with the most important professions, and in the user table, only the id column related to those professions was stored.