Optimizing MySQL Performance

Навигация

• 3.1 General optimization provisions at the database level
• 3.2 Database structure optimization
• 3.3 Efficient construction of SQL queries
• 3.4 Optimizing data access

3.1 General optimization provisions at the database level

The database is an important part of a web application. Today it is difficult to imagine a web application that does not use a database. Even Single Page Sites (SPAs) are often used by databases, for example to store user reviews. At the same time, it is the performance of the database that often becomes one of the key elements that determines the performance of the entire system. Modern databases provide the software engineer with a wide range of tools to optimize performance.

Well-optimized code (both on the server and client sides of the application), using the capabilities of the Yii2 framework (in terms of optimization), as well as a well-optimized infrastructure is often not enough to ensure high application performance. This may be due to the fact that inefficient queries or incorrect database structure can lead to a significant slowdown of the entire system, especially when the application traffic increases. In this case, optimization efforts at other levels can be minimized. For this reason, database performance optimization is an important part of the overall performance optimization system for a web application developed in PHP using the Yii2 framework.

According to the results of a developer survey conducted as part of the Stack Overflow project in 2025, the most popular database is PostgreSQL, the second most popular is MySQL¹. A detailed graph of the most common databases in the world according to developers is presented in Figure 4.

Figure 4 ¾ Results of the 2025 Developer Survey on Database Use²

However, it is MySQL that has traditionally historically been part of the most common technology sets for developing applications in PHP. At first, the acronym LAMP (Linux, Apache, MySQL, PHP) was used to denote this set of technologies, and then LEMP (Linux, Nginx, MySQL, PHP) after Nginx became more widely used compared to Apache.

According to the official documentation for Yii2, the specified framework natively supports the following databases: MySQL; MariaDB; SQLite; PostgreSQL (version 8.4 and higher); CUBRID (version 9.3 or higher); Oracle; MSSQL (version 2008 or higher)³. Even though the Yii2 framework supports both of the most common databases, PHP developers often choose MySQL. Within the framework of this work, the most common methods of optimizing MySQL using the Yii2 framework will be considered.

MySQL provides extensive performance optimization capabilities. The literature discusses in detail many ways to optimize MySQL performance, including the following: schema optimization and indexing; query optimization; optimization of server parameters; optimization of the operating system and hardware; replication; scaling and high availability; application-level optimization⁴.

In applications developed using the Yii2 framework, interaction with the database is carried out primarily through the Active Record class. In general, an Active Record is an object that acts as a wrapper for a row in a table or database view. It encapsulates database access and adds domain⁵ logic to the data. When developing applications using the Yii2 framework, Active Record is used as an ORM and is a way to manipulate data. Therefore, optimizing the performance of an application developed using the Yii2 framework at the database level should take into account both the features of MySQL and the specifics of the ORM framework.

In this chapter of the VKR we will consider the main techniques for optimizing the performance of web applications at the MySQL database level. It is proposed to classify the corresponding techniques into three groups: 1) optimization of the database structure (features of database design); 2) efficient construction of SQL queries and indexing; 3) optimization of data access (mainly in terms of minimizing the number of calls to the database).

3.2 Database structure optimization

The literature suggests that optimizing a poorly designed schema can significantly improve the performance of a web application. The development of table schemas and installation of indexes is carried out for specific queries that will be executed. In addition, you should determine the performance requirements for different types of queries, since changes to one of them or to one part of the schema can have consequences in other places. For this reason, optimizing performance often requires trade-offs. For example, adding indexes to speed up data retrieval (SELECT) slows down data modification (UPDATE). Likewise, a denormalized scheme speeds up some types of queries but slows down others. Adding counter tables (tables or fields that store values that have already been counted) and pivot tables (tables that store aggregated data from other tables) are good ways to optimize queries, but maintaining them can require a lot of software engineering time⁶.

One of the ways to optimize the database structure is to make an informed choice of data types. Using data types of the required size allows you to reduce the amount of information stored and increase the speed of query execution. For example, using INT (each value is 4 bytes) instead of BIGINT (each value is 8 bytes) when you do not need to store large numeric values can reduce the size of tables and improve data processing efficiency.

Similarly, using a VARCHAR with a limited length is more efficient than using large text types (such as TEXT). Another performance optimization technique is to use the UNSIGNED attribute for numeric fields that do not expect negative values ​​(for example, identifiers or counters may be stored as INT UNSIGNED). This will allow you to increase the range of positive values without increasing the size of the data type and will allow for more efficient data storage and processing.

When designing a database structure, it is necessary to take into account the features of storing string and numeric data. Excessive use of string fields increases the size of tables and reduces the efficiency of information processing. To store fixed values in literature, it is recommended to use enumerations (ENUM) or numeric identifiers associated with lookup tables[^7].

An important technique for optimizing a database structure is normalization. Normalization eliminates duplication and ensures data integrity by dividing information into interrelated tables. This approach allows you to reduce the amount of data stored and simplify the maintenance of the database structure (for example, indexes will be added faster).

However, when developing highly loaded systems, database denormalization can be used. This approach involves partial duplication of data in order to reduce the number of table join operations and speed up reading information. Denormalization improves the performance of individual operations, but it makes it more difficult to keep data up to date and can increase the amount of stored information.

The structure of primary (PRIMARY KEY) and foreign (FOREIGN KEY) keys has a significant impact on database performance. Reasonable use of short numeric primary keys (for example, TINYINT, SMALLINT, MEDIUMINT, INT) allows you to reduce the size of tables and increase the efficiency of data storage and data access. It is recommended to use auto-incrementing integer values as primary keys, as they provide a more efficient organization of data in MySQL (for example, each INT value will require 4 bytes, while a UUID in a string will require about 36 bytes).

The literature also recommends avoiding the use of NULL when defining columns. This recommendation is because a nullable column takes up more disk space and requires special handling within MySQL. When such a column is indexed, it requires an additional byte for each entry⁷.

The use of indexes should also be considered as a technique for optimizing the structure of the database. Indexes can speed up data search and processing, but their use must be justified, since an excessive number of indexes increases the volume of stored information and complicates data modification operations. Features of indexing and the impact of indexes on the execution of SQL queries are discussed in section 3.3 of this WRC.

Optimizing database design includes sound definition of data types, table design, proper use of normalization and denormalization, and appropriate setting of keys and indexes. The integrated application of these approaches will improve the performance of the web application and ensure efficient operation of the system as the volume of data and the number of users increases.

3.3 Efficient construction of SQL queries

One of the basic optimization rules for effectively building SQL queries is to select only the required fields instead of using the SELECT * construct. Receiving redundant data increases the amount of information transferred and slows down query execution. It is also recommended in the literature to limit the number of rows returned using the LIMIT operator, which reduces the load on the database server when processing large amounts of data⁸.

The presence of unoptimized queries can lead to full table scans, as well as unreasonably high resource consumption. Basic guidelines for building effective SQL queries are as follows. You should select only the required fields instead of using SELECT *, limit the number of rows returned using LIMIT, avoid nested subqueries when you can use JOIN, use database-side filtering (not in the application), avoid functions on indexed fields in WHERE clauses. An example of a non-optimal query: SELECT * FROM orders;. Optimized version of the query: SELECT id, created_at, status FROM orders WHERE status = 'active' LIMIT 100;

To improve query efficiency, it is recommended to use JOIN operations instead of a large number of nested subqueries. Joining tables allows you to retrieve related data in a single query and allows you to process data more efficiently. In addition, an important element of optimization is the use of data filtering on the database side using WHERE conditions.

The use of indexes has a significant impact on database performance. Indexes allow you to speed up data search (without a full scan). This is especially true for large tables. The following types of indexes are distinguished in the literature: primary key (PRIMARY KEY); unique index (UNIQUE); composite index (across multiple fields); index by foreign key⁹.

Indexation is especially important for fields used in WHERE conditions, JOIN operations, sorting (ORDER BY), and grouping (GROUP BY). An example of creating an index: CREATE INDEX idx_orders_status ON orders(status);. It is important to consider that an excessive number of indexes can also reduce the performance of insert (INSERT) and update (UPDATE) operations, so their use should be justified. The main types of indexes are: primary key (PRIMARY KEY); unique index (UNIQUE); composite index; index by foreign key. Indexing is especially effective for fields used: in WHERE conditions; in JOIN operations; when sorting ORDER BY; and also when grouping GROUP BY.

When using indexes, be aware that having too many indexes can reduce the performance of INSERT and UPDATE operations because the system needs to keep each index structure up to date. Therefore, index creation should only be performed on fields that are actively used in queries. Effective construction of SQL queries and correct use of indexes will improve the performance of the web application (at the database level), as well as reduce data processing time and reduce the load on the database server.

3.4 Optimizing data access

In this topic, we are mainly interested in minimizing the number of calls to the application database. Let's consider only the application level of data access: problem N+1; the relationship and performance impact of greedy and lazy loading; caching; and page navigation. The main problem is the excessive number of database calls. This results in increased application response time, increased server load, and decreased overall web application performance.

One of the most common problems is the N+1 query problem. It occurs when an application first runs one query to retrieve a main set of data, and then additionally runs a separate query for each item retrieved. For example, after receiving a list of users, the application can separately query each user's orders. As a result, the number of requests increases significantly, which negatively affects system performance.

To eliminate the problem of N+1 queries, greedy data loading mechanisms (Eager Loading) are used. Greedy loading allows you to fetch related entities along with the underlying data set in advance, reducing the number of database calls. Unlike greedy loading, lazy loading loads related data only at the moment it is accessed, which, with a large number of objects, can lead to the formation of many additional requests.

An important method for optimizing data access is the use of caching. When frequently accessed information is repeatedly accessed, data can be retrieved from the cache without making repeated queries to the database. This reduces the load on the database server and reduces query processing time. However, the use of caching requires control in terms of data relevance and the organization of cache update mechanisms.

An additional optimization method is to limit the amount of simultaneously downloaded data. To do this, page-by-page data output (page-by-page navigation) is used, which allows you to receive information in small parts. This approach reduces the amount of data transferred and reduces the load on the server when processing user requests. Reasonable configuration of data loading mechanisms allows you to increase the efficiency of application interaction with the database and reduce the consumption of server resources.

Thus, optimizing the performance of a web application in terms of providing access to data includes eliminating the problem of N+1 requests, using greedy and lazy loading mechanisms, caching and paging. The integrated use of these approaches will reduce the number of database calls, reduce the load on the MySQL server and increase the performance of the web application.