3v-Hosting Blog
14 min read
Website performance is one of the key factors affecting not only user experience but also business metrics such as conversion, audience retention, and even search engine rankings. Modern users are not willing to wait more than a second for a page to load, and search engines are increasingly taking real speed metrics into account when ranking websites in search results. In recent years, Google has explicitly stated in its documentation that the faster a website works, the higher it ranks, and vice versa - if a website is extremely slow, it may not even appear in search results for the most relevant queries.
At the same time, it is a common mistake to assume that the entire problem lies solely in the power of the server or the bandwidth of the communication channel. In practice, even a powerful server with SSD and a large amount of RAM will not save a website if the database is inefficient. Slow and unoptimized SQL queries can “kill” application performance, cause an avalanche-like increase in load, and ultimately lead to a complete service outage.
In this article, we will discuss a step-by-step and safe approach to finding, analyzing, and optimizing slow MySQL queries that is suitable for real production projects and relevant for modern versions of MySQL and MariaDB.
Before you start optimizing database performance, you need to understand exactly which version of the DBMS is used on the server and what capabilities it provides. The version of MySQL or MariaDB directly determines the set of available diagnostic tools, the behavior of the query optimizer, support for certain caching mechanisms, as well as the correctness and security of configuration parameters. Recommendations that are relevant for older versions may be ineffective or completely absent in modern releases, so taking the DBMS version into account is a mandatory step before starting any analysis and optimization work.
In the table below, we have compiled a visual overview of the main DBMS components used to search for and optimize slow queries and their support by different DBMS versions.
Table - Component support by different DBMS versions
| Component | MySQL 5.7 | MySQL 8.0 | MariaDB 10.x |
|---|---|---|---|
| Slow Query Log | Yes | Yes | Yes |
| Performance Schema | Yes | Yes | Partially |
| sys schema | Yes | Yes | Partially |
| Query Cache | Deprecated | Removed | Available (not recommended) |
For example, starting with MySQL 8.0, Query Cache has been completely removed because it did not scale well and created locks. Therefore, any recommendations to enable it in modern systems are outdated and potentially harmful.
The first and mandatory step in troubleshooting database performance issues is to enable the slow query log. This is a MySQL mechanism that records SQL queries that take longer than a specified time to execute. It allows you to move from subjective feelings (“the site is slow”) to concrete facts.
To enable this log, open the configuration file for your MySQL instance. Most often, this is either /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf. Add the following lines to the end of this file:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
Additional parameters that may be useful:
log_queries_not_using_indexes = 0
min_examined_row_limit = 1000
Explanations:
long_query_timeis a time threshold in seconds. Queries faster than this value will not be logged;log_queries_not_using_indexes -enables logging only those SQL queries that are executed without using indexes, i.e., that perform a full table scan. Therefore, this parameter should be enabled only temporarily, otherwise the log will quickly become cluttered;min_examined_row_limit - allows you to ignore queries that work with a small number of rows.
After making changes, restart MySQL with the following command:
systemctl restart mysql
On active and highly loaded sites, the slow query log file can grow very quickly, especially with a low long_query_time threshold or a large number of similar queries. Without configured rotation, the slow log can take up gigabytes of disk space in a matter of days, leading not only to logging problems, but also to much more serious consequences, such as file system overflow, inability to write new data, and, as a result, an emergency shutdown of MySQL or the entire service.
That is why slow log should be considered a temporary diagnostic tool, i.e., it must be rotated regularly, file sizes must be monitored, and outdated data must be cleaned up in a timely manner, especially in a production environment with constant user traffic.
Therefore, it is recommended to:
It is important to understand that slow log is a diagnostic tool, not a permanent archive.
It is also important to understand that slow query log is not an SQL file, it cannot be “fed” to a MySQL client. Special utilities are used for analysis, which can aggregate and interpret the collected data. These utilities are:
mysqldumpslow is a built-in MySQL tool that is suitable for a quick initial assessment of slow queries. For example, by executing the command:
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log
You will get:
This allows you to quickly understand which queries should be analyzed first.
Once you have a clear picture of the overall situation, you need to conduct a detailed in-depth analysis of a specific query. To do this, use pt-query-digest. It shows:
This is especially important when a query is executed quickly but very often, for example, thousands of times per second.
Once the problematic query has been found and confirmed by data from the slow query log or Performance Schema, the question naturally arises: why is this particular query running slowly? The fact that it takes a long time to execute does not in itself explain the reason, as it could be due to a lack of indexes, an unsuccessful table join order, sorting outside the index, working with large amounts of data, or a suboptimal query structure.
The answer to this question is provided by the EXPLAINoperator, which allows you to look inside the query execution mechanism and see what action plan the MySQL optimizer chooses. With its help, you can understand which tables are processed and in what order, whether indexes are used, how many rows the server expects to scan, and what additional operations are performed in the process. It is the analysis of the execution plan that is the key step in optimizing slow queries, as it allows you to make decisions based on facts rather than assumptions.
Let's look at this example:
EXPLAIN SELECT * FROM customers WHERE age > 30;
When analyzing a query execution plan, special attention should be paid to several fields of the EXPLAINoutput. These fields provide the most accurate picture of how MySQL retrieves and processes data and where bottlenecks occur.
type - data access method
The type field shows how MySQL reads data from the table. This is one of the most important indicators of query performance. The “smarter” the access method, the less data the server has to process.
The worst option is the value ALL, which means a complete table scan. In this case, MySQL is forced to read every row, regardless of the query conditions. With large amounts of data, this leads to a sharp increase in execution time and load on the disk and CPU. More preferable values are ref, range, const, as they indicate the use of indexes and working with a limited set of rows.
key - index used
The key field shows which index was selected by the optimizer to execute the query. If the value is NULL, it means that no index was used, even if one exists. There can be various reasons for this: inappropriate query structure, mismatched data types, use of functions in WHERE, or incorrect order of conditions.
The absence of an index almost always means an increase in query execution time, especially on large tables. If an index exists but is not used, this is a clear signal to review the query or indexing scheme.
rows - estimate of the number of rows processed
The rows field shows how many rows MySQL plans to scan to get the result. This is an estimate, not an exact value, but it gives an idea of the scale of the operation. Large rows values indicate that the server has to process a significant amount of data, which directly affects performance.
Even with an index, a high rows value may indicate low selectivity of conditions or poor index selection. In such cases, optimizing the query or creating a more suitable index can significantly reduce the load.
Extra - additional operations
The Extra field contains additional information about the actions that MySQL performs while processing the query. This is where the most unpleasant surprises are often hidden.
Entries such as Using filesort mean that sorting is performed outside the index, which can lead to the use of temporary files and slow down the query. The value Using temporary indicates the creation of temporary tables, which are especially dangerous when working with large amounts of data and complex GROUP BY or ORDER BY. Such operations increase the load on memory and disk and often cause a sharp drop in performance.
When analyzing EXPLAIN, there are signs that almost always indicate potential problems and require further investigation:
Finding one or more of these signs does not always mean disaster, but it almost always serves as a signal that the query needs to be optimized or the data structure revised.
Indexes are a powerful but not universal tool. Incorrect indexing can degrade the performance of your database.
Basic recommendations for building indexes:
WHERE, JOIN, ORDER BY;EXPLAIN.Important! After adding an index, we strongly recommend that you repeat the tests.
Performance Schema is designed for in-depth and detailed analysis of database behavior at the level of MySQL's internal mechanisms. Unlike the slow query log, which only records individual slow queries after the fact, Performance Schema works in real time and continuously collects statistics on query execution, resource usage, lock waits, and other key metrics.
A distinctive feature of Performance Schema is that it aggregates data by query types and patterns rather than by specific parameter values. This allows you to see not isolated problems, but systemic load patterns, such as which classes of queries create the main load on the server, which operations most often consume CPU or wait time, and which parts of the application require optimization first. This approach is especially useful for high-load projects, where many identical queries individually seem harmless, but collectively have a serious negative impact on database performance.
Example of a query to Performance Schema:
SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10;
This query shows:
It is also important to remember that data in Performance Schema is aggregated by query patterns, not by specific values.
In modern versions of MySQL, performance improvements are achieved not through the built-in Query Cache, which is outdated and was removed from MySQL 8.0, but through multi-level caching at different levels of the application architecture. This approach significantly reduces the load on the database and speeds up the site's response without complicating SQL queries.
At the application level, caching using Redis or Memcached is most often used. These systems allow you to store the results of frequently executed queries or calculations in memory and return them without accessing the database. This works especially well for data that rarely changes but is frequently requested, such as lists, settings, or reference information.
At the web server level, dynamic content caching is used, for example with fastcgi_cache in Nginx. This approach allows you to serve ready-made HTML pages without running PHP or other backend applications, which significantly reduces the load on both the web server and MySQL. This is especially useful for pages with high traffic and identical content for most users.
On the database side, the InnoDB Buffer Pool plays a key role. This is an area of memory where MySQL stores frequently used data and indexes. With a sufficiently large buffer pool, most read operations are performed from RAM rather than from disk, which speeds up performance many times over. Proper configuration of this parameter often provides a greater performance gain than any point-specific query optimizations.
Finally, at the application code level, it is important to use prepared statements. They allow query execution plans to be reused, reducing the overhead of parsing and optimizing them, and also improve application security.
Taken together, this multi-level approach to caching in most cases provides a more tangible and stable effect than attempts to speed up the database solely by rewriting complex SQL queries.
Any database optimization only makes sense if its result can be clearly recorded and compared. Changes to indexes, queries, or MySQL configuration should lead to a measurable effect, otherwise it is impossible to understand whether the actions were really useful or whether the changes did not produce the expected result.
That is why, after each optimization, it is necessary to go through a standard verification cycle that allows you to objectively assess the impact of the changes made.
After making changes, it is recommended to perform the following set of actions:
Update table statistics
Executing the ANALYZE TABLE command forces MySQL to recalculate data distribution statistics. This is especially important after adding or changing indexes, as without up-to-date statistics, the optimizer may continue to use a suboptimal query execution plan.
Compare the slow query log
It is necessary to compare the slow query log before and after optimization. It is important to analyze not only the maximum query execution time, but also:
Re-analyze EXPLAIN
For optimized queries, you should re-run EXPLAIN and make sure that:
rows) has decreased;Using temporary and Using filesort have disappeared, if they were present before.
Measure the actual response time of the site
Even if the database performance has improved, it is important to check how this has affected the actual user experience. To do this, you can use:
For clarity, it is convenient to summarize the optimization results in a comparative table like this one:
| Metric | Before optimization | After optimization | Comment |
|---|---|---|---|
| Number of slow queries | |||
| Maximum query execution time | |||
| Average execution time | |||
| Index usage | No / Partial | Yes | |
| Presence of temporary/filesort | Yes / No | Yes / No | |
| Average page response time |
This approach allows you not only to record the result, but also to document the changes made, which is especially important for teamwork, future project support, and repeated optimizations.
Without structured “before and after” testing, database optimization can easily turn into a series of random actions that do not produce stable and reproducible results.
Yes, the slow query log can and should be used on production servers if it is configured correctly. It is important to set a reasonable threshold for long_query_time, enable log rotation, and monitor file sizes. Under these conditions, the slow log has virtually no impact on performance and allows you to identify real problems without risking system stability.
long_query_time is best to use?For most sites, the optimal value is in the range of 1–2 seconds. For high-load projects, the threshold can be lowered, but this should be done temporarily and under control, otherwise the log will be overflowed with insignificant queries.
The presence of an index does not guarantee its use. MySQL may ignore the index due to an inappropriate query structure, the use of functions in conditions, data type mismatches, low index selectivity, or outdated statistics. In such cases, it is necessary to analyze EXPLAIN and, if necessary, update the statistics or revise the query itself.
Not always. Sometimes adding an index does speed up the query, but in other cases the problem lies in the query logic, unnecessary table joins, or excessive data selection. In addition, each new index increases the load on write operations, so indexing should be used judiciously.
These tools solve different problems and work best together. Slow query log is suitable for analyzing specific slow queries and problem history, while Performance Schema allows you to see the overall load picture and identify system patterns in real time.
Partially, but the effectiveness will be lower. Without real data on load, query frequency, and user behavior, it is difficult to make accurate conclusions. For high-quality optimization, it is desirable to have access to the slow log or Performance Schema statistics from the production environment.
Signs of successful optimization include a reduction in the number of slow queries, a decrease in average and maximum execution times, improved EXPLAIN execution plans, and a reduction in website or application response times for end users.
Finding and optimizing slow MySQL queries is not a one-time task, but rather an ongoing process that should be part of the regular maintenance of any website or application. Even simple and basic steps, such as enabling slow query logging, analyzing query execution plans, and proper indexing, can identify and eliminate most of the causes of website slowdowns and increased server load.
It is important to remember that any changes to the database structure or MySQL configuration require a conscious approach and mandatory verification of the result. Erroneous actions performed without understanding the consequences may not only fail to increase performance, but also lead to serious problems, such as speed degradation, data loss, or even complete service unavailability.
Proper optimization is based on facts and measurements, careful changes, and regular monitoring of the database status. It is this methodical approach that ensures stable, predictable, and fast MySQL performance in the long term.
I think no one will argue with the fact that in our time any company should have maximum presence on the Internet. And of course, this applies to start-up busin...
One of the first tasks that a system administrator must perform when setting up the OS on a new server, along with setting up users and their permissions, is se...
Logical Volume Management (LVM) is a sophisticated disk management system for Linux that provides a flexible and scalable approach to storage management. It all...
Off-page SEO without myths: links, brand mentions, reputation, and behavioral factors. A practical checklist for evaluating external signals and increasing webs...
A practical introduction to grep for Linux: how the command works, which flags you really need, common mistakes, and real-world scenarios for using grep in admi...
Learn how IP addresses work: IPv4 vs IPv6, public and private IPs, DNS resolution, routing, security basics, and how IPs are used in real server and cloud infra...
