A slow website can often be traced back to inefficient database queries. The MySQL slow query log records which queries take longer than a set threshold, so you can see exactly where the delay occurs. In this article, you’ll learn how to enable the log, how to interpret its contents using tools like mysqldumpslow and pt-query-digest, and what steps to take to actually improve slow queries.
What is the slow query log and when do you use it
The slow query log is a log file in which MySQL (or MariaDB) records every query that takes longer than a threshold you set, known as long_query_time. This feature is disabled by default, since logging always introduces a small amount of overhead. Still, enabling it is well worth it as soon as you notice that pages load slowly, a CMS like WordPress or Magento responds slower than expected, or your server is under high CPU load while visitor numbers haven’t increased.
For each query, the log shows, among other things, the execution time, the number of rows examined, and the actual SQL statement. This lets you distinguish between occasional spikes and structurally poor-performing queries, for example due to a missing index or a query that scans an entire table instead of using an index.
Enabling and configuring the slow query log
On most servers, you adjust the MySQL configuration in the my.cnf file or via a management interface such as Plesk. The most important settings are:
slow_query_log = 1to enable loggingslow_query_log_file = /path/to/slow-query.logfor the location of the log filelong_query_time = 1to log queries longer than 1 second, adjust this to a value that suits your situationlog_queries_not_using_indexes = 1to also log queries that don’t use an index, even if they’re fast
After adjusting the configuration, restart the MySQL service. Keep in mind that the log can grow quickly on a busy server, so check the file size regularly or rotate the log automatically via logrotate.
Reading and analyzing the log
A raw slow query log is difficult to grasp at a glance, especially with heavy traffic. Fortunately, there are tools that summarize the log and put the most problematic queries at the top.
mysqldumpslow is included by default and groups similar queries, so you can see in one overview which query occurs most often and takes the longest on average. An example command is mysqldumpslow -s t -t 10 /path/to/slow-query.log, which shows the ten slowest query groups sorted by time.
pt-query-digest, part of Percona Toolkit, provides a more extensive analysis, including the percentage of total time a query type takes up and details about rows examined versus returned. This is useful if you want to dig deeper than mysqldumpslow offers.
When analyzing, pay particular attention to queries with a high number of rows examined compared to the number of rows returned. This often signals a missing index. Repeated identical queries from, for example, a CMS can also indicate a plugin or module that handles the database inefficiently.
Resolving slow queries
Once you know which queries are problematic, the optimization phase follows. This is often custom work, but the steps below form a good starting point.
Analyze the query with EXPLAIN
Place EXPLAIN before the query to see which execution plan MySQL chooses and whether an index is used.
Add missing indexes
Add indexes on columns that appear frequently in WHERE, JOIN, and ORDER BY clauses, but avoid over-indexing since this slows down write operations.
Rewrite inefficient queries
Avoid SELECT *, limit subqueries where possible, and use LIMIT for large result sets.
Check table maintenance
Regularly run OPTIMIZE TABLE on tables with many updates and deletions to limit fragmentation.
Monitor after adjustment
Keep the slow query log running after a change to check whether the adjustment actually had an effect.
At the server level, it also helps to ensure fast storage and sufficient resources. On a server with NVMe storage and LiteSpeed, you process queries faster by default, but you only solve structural query problems with proper indexing and efficient SQL. In Plesk, as used with Tandata’s web hosting, you’ll find database management tools, and if in doubt you can always ask for help interpreting a log via support.
Frequently asked questions
Where can I find the slow query log file?
The location is specified in the MySQL configuration under slow_query_log_file. By default, this is often in the MySQL data directory, but on shared hosting the location may differ.
Does the slow query log affect server performance?
Logging introduces a small amount of overhead, but with a reasonable threshold, this effect is generally negligible compared to the benefits of insight into slow queries.
What is a good value for long_query_time?
This depends on your application. For most websites, 1 to 2 seconds is a good starting point, after which you can refine based on what you see in the log.
Can I use the slow query log on shared hosting?
This depends on the permissions you have on the server. With managed hosting, the provider can often view or enable this for you via the control panel.
Does an index always help against slow queries?
Not always. An index helps find rows faster, but with complex joins or poorly written queries, rewriting the query is sometimes more effective than indexing alone.
Conclusion
The MySQL slow query log is a powerful tool for detecting slow queries before they noticeably slow down your website. By enabling the log correctly, analyzing it with tools like mysqldumpslow or pt-query-digest, and then adding targeted indexes or rewriting queries, you structurally improve database performance. Combine this with fast infrastructure for the best results.