# Performance Impact of Optimization Methods on MySQL Document-Based and Relational Databases
## 4 Database Optimization Methods  
### Field Optimization-Based Methods  
Considering the database structure, the following field optimizations were applied:  
* Reducing the possible values for a field by changing its type from _object_ to _primitive_;
* Minimizing the length of the fields;
* Replacing recurring values with an _enum_ and storing only the associated _id_ saved as _tinyint_;
* Reducing the possibility of string fields being null. The first optimization proposed by us is to reduce the values that a field can take by changing its type in the application from the object to the primitive type. In the case of a field of types integer, Boolean, long, and not only, defined as object, the field can be null, and it is not necessary to have a default value. By changing these fields to primitive types, we reduce the possibility that these fields will be null, and we must have a default value for them. In our case, this optimization was applied to the _deleted_ field, which became, by default, false. By this optimization, the filter conditions that use these types of fields can be simplified, and there is no longer the need to check for null, in our case, reducing the condition from (_deleted is null or deleted = false_) to (_deleted = false_). These optimizations reduce the possibility of obtaining a wrong result (not all possibilities have been checked), reduce the length of the query, and help to make it run faster, and the field becomes cheap considering the memory expense. Another proposed optimization is the one related to the length of the fields. The length of the fields will be defined in correlation with the maximum value stored in that field. When creating a new table or a new object, the length of each field in the table/class up to the maximum that the type of respective field can take is usually set. However, a shorter length can be defined for certain fields, for which we know the maximum value of the field that will be entered in that field. For example, in the case of the database described in Figure 1, for the phone field, we know that its length cannot exceed 13-14 characters; therefore, the column of this length should be defined and not be left longer. The same optimization can be conducted in the case of email, name, address, and profession columns. By this optimization, the storage space is reduced because the length of the fields is fixed, and no larger space that will never be used is allocated, thus helping to improve response times because they are saved in memory after the first operation on that table, with the performance being affected by this aspect. The length of the fields is strictly defined with the maximum value of the field. In this case, if we want to insert or update a column that has a fixed length with a longer field, an exception may occur, and the value is no longer inserted or updated. This exception can be avoided by modifying the setter so that it makes a substring of maximum x characters. The frontend application should address this issue and, if the length is longer, display an error to the user, but we must ensure that this length is respected. We can handle this issue by calling an exception to avoid data loss. The fields from tables that save an _enum_ type, a string that is repeated, or store values that can be changed into an _enum_ type need to be saved in databases as numbers, each number meaning something, and not as a string, which is stored as an entire value. For values from 0 to 128, _tinyint_ can be used, being the smallest type of integer, stored on one byte. In the structure described in Figure 1, the profession field is an important one if we want to perform certain statistics based on the professions of users, in order to see which professions dominate the application. Therefore, an optimization that can be conducted is to create an _enum_ type that can contain the most common professions, but there are also other professions, which would include users who are not in one of the predefined professions.