MySQL Slow Query Log: Enable and Fix in 7 Steps

This guide shows you how to turn on the MySQL slow query log, interpret the entries it records, and use that information to fix the queries that are slowing down your database. You’ll learn the exact configuration settings, how to verify the log is working, and practical steps to optimize the slow queries you find.

What You’ll Need

• Access to the MySQL server with privileges to edit the configuration file (usually my.cnf or my.ini) and restart the MySQL service.
• Ability to run SQL commands as a user with SUPER or SYSTEM_VARIABLES_ADMIN privileges (typically root).
• Basic familiarity with the command line and text editors.

Step-by-Step

1. Check current slow query log settings. Connect to MySQL and run:

   SHOW VARIABLES LIKE 'slow_query_log';
   SHOW VARIABLES LIKE 'slow_query_log_file';
   SHOW VARIABLES LIKE 'long_query_time';
   SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

   This tells you if the log is on, where it writes, and the current threshold (in seconds).

2. Enable the slow query log dynamically (no restart needed). While connected, run:

   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 2;
   SET GLOBAL log_queries_not_using_indexes = 'ON';

   The long_query_time value is in seconds; 2 is a common starting point. Setting it to 0 logs every query (use only for debugging).

3. Make the change permanent. Edit your MySQL configuration file (typically /etc/my.cnf or /etc/mysql/my.cnf on Linux, or my.ini on Windows). Add these lines under the [mysqld] section:

   [mysqld]
   slow_query_log = 1
   slow_query_log_file = /var/log/mysql/mysql-slow.log
   long_query_time = 2
   log_queries_not_using_indexes = 1

   Adjust the file path to a location the MySQL user can write to. Restart MySQL for the changes to take effect.

4. Verify the log is active. After restarting, run the SHOW VARIABLES commands again to confirm the settings. Then run a deliberately slow query, like SELECT SLEEP(3);, and check that the log file contains an entry for it.
5. Read the log entries. Open the log file. Each entry shows the query time, lock time, rows sent, rows examined, and the full query text. The key number is Rows_examined — if it’s much higher than Rows_sent, the query is scanning too much data.
6. Analyze the slow queries. Use EXPLAIN on the worst offending query to see how MySQL executes it. Look for full table scans (type ALL) or missing indexes (key NULL).
7. Fix the query. Common fixes include:
   • Add an index on columns used in WHERE, JOIN, and ORDER BY clauses.
   • Rewrite the query to avoid functions on indexed columns (e.g., WHERE DATE(created_at) = '2024-01-01' prevents index use; use a range instead).
   • Limit the result set with LIMIT if you only need a few rows.
   • Break up complex queries into simpler steps.

   After making changes, rerun the query and check if it still appears in the slow log.

Common Mistakes

• Setting long_query_time too low. A value of 0 or 0.1 will flood the log with thousands of entries, making it hard to find real problems. Start at 2 seconds and adjust down only if needed.
• Forgetting to make the change permanent. Dynamic settings reset when MySQL restarts. Always update the config file.
• Using the wrong file path. The MySQL user must have write permission to the log file location. If the path doesn’t exist, MySQL may silently fail to write.
• Ignoring queries that don’t use indexes. Even fast queries can be slow under load. log_queries_not_using_indexes helps catch those.
• Not checking the log after a restart. A typo in the config file can silently disable the log. Always verify with SHOW VARIABLES.

Tips

• Use mysqldumpslow (comes with MySQL) to summarize the log: mysqldumpslow -s t /var/log/mysql/mysql-slow.log sorts by query time.
• Rotate the log file regularly to prevent it from filling the disk. MySQL has a built-in log_output option; you can also use logrotate.
• Combine the slow log with EXPLAIN to see the execution plan. The output shows which index is used and how many rows are scanned.
• Monitor the log size and prune old entries. A huge log slows down MySQL itself.
• Test your changes on a staging server first, especially if you add indexes to large tables — they can lock the table during creation.

Troubleshooting

• Log is not being written. Check the file path and permissions. Run SHOW VARIABLES LIKE 'slow_query_log_file'; to see the actual path. Ensure the MySQL user can write to that directory. Also check if log_output is set to TABLE — then the log goes to the mysql.slow_log table instead of a file.
• Queries are not appearing even though they exceed long_query_time. If log_queries_not_using_indexes is OFF, only queries that exceed the time are logged. If you set long_query_time to 0, all queries are logged, but you may have missed the change due to a typo. Also, administrative statements (like SHOW) are not logged by default.
• MySQL won’t start after editing the config. Check the error log (usually in /var/log/mysql/error.log). Common issues: incorrect file path or missing [mysqld] section header. Remove your changes and restart, then add them back one by one.
• Log file grows too fast. Increase long_query_time or turn off log_queries_not_using_indexes. Also consider using log_queries_not_using_indexes only temporarily.
• Changes don’t persist after restart. You likely edited the wrong config file. Run mysqld --verbose --help | grep -A 1 'Default options' to see which files are read. Also check if the settings are overridden by a later config file.

Frequently Asked Questions

What is a good value for long_query_time?

Start with 2 seconds. If your application is mostly small queries, you may lower it to 1. For high-traffic systems, 5 or 10 seconds might be better to avoid log noise.

Does the slow query log affect performance?

Yes, but minimally. Writing to the log adds I/O overhead, especially if the log is large. On very busy servers, consider logging to a separate disk or using the TABLE output.

Can I enable the slow query log without restarting MySQL?

Yes, use SET GLOBAL slow_query_log = 'ON'; — it takes effect immediately. But to make it permanent, you must edit the config file.

Why do I see queries that are faster than long_query_time?

If log_queries_not_using_indexes is ON, any query that doesn’t use an index is logged regardless of time. That’s often desirable, but you can turn it off to reduce noise.

How do I read the log entries?

Each entry has a header with the time, user, host, and query stats. The Query_time is the total execution time, Lock_time is time waiting for locks, Rows_sent is the result size, and Rows_examined is how many rows were scanned. A high Rows_examined ratio indicates a missing index.

What if I don’t have root access to MySQL?

You need at least SUPER or SYSTEM_VARIABLES_ADMIN to change global variables. If you don’t have it, ask your DBA to enable the log for you.

Related

• Decision Scoring Models vs Language Models: Which to Use?
• Paper Mario: The Thousand-Year Door Beyond the Remake
• Apple’s Limited Access to OpenAI Evidence Explained