If your website or application feels sluggish and you suspect the database is the bottleneck, enabling the Slow Query Log in MySQL or MariaDB is one of the best ways to identify problematic queries.
The slow query log records SQL statements that take longer than a specified time to execute allowing you to detect inefficient queries that need optimization.
What Is the Slow Query Log?
The Slow Query Log is a feature in MySQL and MariaDB that logs:
Queries that exceed a defined execution time (e.g., 2 seconds)
Queries that don’t use indexes
Information on query time, lock time, rows examined, etc.
This log is invaluable for:
Performance optimization
Debugging slow websites or apps
Monitoring database efficiency
How to Enable the Slow Query Log (Step-by-Step)
You can enable the slow query log either temporarily (without restarting the server) or permanently (via config file).
Method 1: Temporary (Using SQL Commands)
Log into MySQL or MariaDB:
Enable the slow query log:
Set the long query time threshold (e.g., 2 seconds):
Optional: Log queries not using indexes:
Check current log file path:
Exit MySQL:
Note: These changes are temporary and will be lost after a MySQL restart.
Method 2: Permanent (Edit my.cnf or my.ini File)
Locate and open the MySQL configuration file:
On Linux: /etc/my.cnf or /etc/mysql/my.cnf
On Windows: C:\ProgramData\MySQL\MySQL Server X.X\my.ini
Add or edit the following under [mysqld] section:
Save the file and restart MySQL:
or
Ensure logging is active:
Where to Find the Slow Query Log
Default locations:
Linux: /var/log/mysql/slow-query.log or /var/lib/mysql/hostname-slow.log
Windows: As specified in the my.ini file
How to Analyze the Slow Query Log
Use tools like:
Mysqldumpslow
pt-query-digest from Percona Toolkit (for advanced analysis)
Or manually open the log file with a text editor
Best Practices
Set long_query_time to 2–5 seconds to catch only real performance concerns.
Don’t forget to rotate the log file regularly to avoid large log sizes.
Use pt-query-digest to summarize and prioritize query optimizations.
Summary
Setting | Description |
slow_query_log | Enables slow query logging |
slow_query_log_file | Path to log file |
long_query_time | Threshold in seconds |
log_queries_not_using_indexes | Logs non-index queries |
The slow query log is a powerful tool for identifying performance bottlenecks in your MySQL or MariaDB database. Whether you enable it temporarily or permanently, it gives you the insights you need to fine-tune queries, speed up your app, and improve user experience.