How to Enable MySQL Slow Query Log: Complete Setup Guide

Introduction

Imagine you're managing a critical database and suddenly users start complaining about slow performance. But where do you start looking? With thousands of queries running, how do you know which ones are the culprits?

This is exactly what the MySQL slow query log solves.

The slow query log is like a security camera for your database—it records every query that takes longer than your specified threshold. Instead of hunting blindly through all your queries, the slow query log shows you exactly which ones are causing problems.

In this guide, we'll walk through everything you need to know to set up, configure, and use the MySQL slow query log effectively.

TABLE OF CONTENTS

1. What Is the MySQL Slow Query Log?
2. Why You Need the Slow Query Log
3. How to Enable the Slow Query Log
4. Configure the Slow Query Log Settings
5. How to View and Analyze the Log
6. Best Practices for Slow Query Logging
7. Troubleshooting Common Issues
8. Key Takeaways

What Is the MySQL Slow Query Log?

The MySQL slow query log is a built-in feature that automatically records queries taking longer than a specified time threshold. By default, this threshold is 2 seconds, but you can adjust it based on your needs.

Think of it like this: every time a query runs longer than your threshold, MySQL writes it to a log file. This log file contains valuable information about each slow query:

• The exact query that ran
• When it ran (timestamp)
• How long it took (execution time)
• How many rows were examined
• How many rows were returned
• The database and user that executed it

This information becomes your roadmap for optimization. Instead of guessing which queries need attention, you have data showing exactly which ones are slow and how slow they are.

SECTION 2: Why You Need the Slow Query Log

Finding Performance Problems

Without the slow query log, you're flying blind. You might know your application is slow, but you won't know if it's one specific query or ten different queries causing the problem.

Measuring Impact

The slow query log shows you which queries are actually slow in your production environment. A query that takes 1 second might be fine for most applications, but for a high-traffic e-commerce site, it could be devastating.

Tracking Changes Over Time

As your database grows, queries that used to run fast might slow down. The slow query log helps you catch these degradations early before they impact users.

Justifying Optimization Work

When you need to convince your team that query optimization is important, the slow query log provides concrete data. "This query ran 500 times yesterday and averaged 5 seconds each time" is much more convincing than "I think something is slow."

Preventing Issues Before They Happen

By monitoring your slow query log regularly, you can identify problems in development or staging environments before they hit production.

SECTION 3: How to Enable the Slow Query Log

Step 1: Connect to Your MySQL Server

First, you need to connect to your MySQL server with administrative privileges:

mysql -u root -p

Enter your root password when prompted.

Step 2: Enable Slow Query Logging

Once connected, run these commands:

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

What these commands mean:

• slow_query_log = 'ON' — Turns on slow query logging immediately
• long_query_time = 2 — Logs queries taking longer than 2 seconds
• log_queries_not_using_indexes = 'ON' — Logs queries without indexes, even if fast

Step 3: Verify the Settings

Run these commands to confirm:

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

You should see output confirming:

• slow_query_log: ON
• slow_query_log_file: (path to log file)
• long_query_time: 2

Step 4: Make Changes Permanent

Edit your MySQL configuration file.

On Linux:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Or:

sudo nano /etc/mysql/my.cnf

Add these lines under [mysqld]:

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

On Windows:
Edit the file:

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Add the same settings under [mysqld].

Step 5: Restart MySQL

To apply the changes:

Linux:

sudo systemctl restart mysql

Windows:

net stop MySQL80
net start MySQL80

(Modify service name if needed)

SECTION 4: Configure the Slow Query Log Settings

Understanding long_query_time

This is the most important setting. It defines what "slow" means in your environment.

Common Thresholds:

• 0 seconds — Log all queries (huge logs, useful for full profiling)
• 0.1 seconds (100ms) — Aggressive profiling for high-traffic apps
• 1 second — Standard for fast-response systems
• 2 seconds — Default; recommended starting point
• 5 seconds — Only logs very slow queries
• 10+ seconds — Only catches major performance failures

Recommendation: Start with 2 seconds. Once optimized, lower it to 1 second.

Understanding log_queries_not_using_indexes

When enabled (ON), this logs queries that don't use indexes — even if they're fast.

Why?
Queries without indexes scale very poorly as data grows — they’re performance risks waiting to explode.

Best practice: Keep this ON during development and testing.

min_examined_row_limit

You can reduce noise by logging only queries that examine more than a certain number of rows:

SET GLOBAL min_examined_row_limit = 1000;

This helps eliminate harmless fast queries while keeping problematic ones visible.

SECTION 5: How to View and Analyze the Log

Locate Your Log File

The log file location depends on your operating system.

Linux:

/var/log/mysql/slow-query.log

macOS:

/usr/local/var/mysql/slow-query.log

Windows:

C:\ProgramData\MySQL\MySQL Server 8.0\Data\slow-query.log

View the Log File

View recent entries:

tail -f /var/log/mysql/slow-query.log

The -f flag means "follow" — shows new entries live.

View the entire file:

cat /var/log/mysql/slow-query.log

Understanding Log Entries

# Time: 2025-01-20T10:30:45.123456Z
# User@Host: webapp@[192.168.1.100]
# Query_time: 5.234567  Lock_time: 0.000456  Rows_sent: 100  Rows_examined: 50000
SET timestamp=1705756245;
SELECT * FROM orders WHERE status = 'pending' AND created_date > '2025-01-01';

What this means:

• Time — When the query ran
• User@Host — Which user and client executed it
• Query_time — Total execution time
• Lock_time — Time spent waiting for locks
• Rows_sent — Returned rows
• Rows_examined — Rows scanned (big number = bad)

Key metrics to watch:

1. Query_time — Much higher than threshold means trouble
2. Rows_examined vs Rows_sent — If huge difference, likely missing an index

Analyze Using mysqldumpslow

mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

Useful flags:

• -s t — Sort by query time (slowest first)
• -s c — Sort by count (most frequent)
• -t 10 — Limit output to top 10 results

Clear the Log File

TRUNCATE TABLE mysql.slow_log;

Or manually:

sudo rm /var/log/mysql/slow-query.log
sudo touch /var/log/mysql/slow-query.log
sudo chown mysql:mysql /var/log/mysql/slow-query.log

SECTION 6: Best Practices for Slow Query Logging

1. Review Logs Regularly

Don’t just enable logging and forget it. Review your slow query log at least weekly. Create a reminder so it becomes part of your routine performance checks.

2. Monitor Log File Size

The slow query log can grow large over time. Check its size periodically:

ls -lh /var/log/mysql/slow-query.log

If the file becomes very large (e.g., over 1GB), consider archiving or clearing it to prevent disk issues.

3. Use in Development First

Enable slow query logging in development with a lower threshold (e.g., 0.1 seconds) to catch inefficient queries early—before they hit production.

4. Set Appropriate Thresholds

Different applications have different performance needs:

• Analytics or reporting systems may allow long-running queries
• APIs often require sub-second response times

Adjust your threshold based on system performance goals.

5. Document Your Setup

Always document the following so your team stays aligned:

• Why slow_query_log is enabled
• Current time threshold
• Log file location
• How frequently logs are reviewed

6. Combine with Monitoring Tools

Pair slow query logs with observability platforms like Percona Monitoring and Management (PMM) for deeper insights into database performance.

SECTION 7: Troubleshooting Common Issues

Problem: “Permission denied” when trying to view log

Solution: Use sudo or contact your hosting provider:

sudo tail -f /var/log/mysql/slow-query.log

Problem: Log file not being created

Solution: Fix ownership and permissions:

sudo chown mysql:mysql /var/log/mysql/
sudo chmod 750 /var/log/mysql/

Problem: Changes don’t persist after restart

Solution: Ensure you modified the correct configuration file and restart MySQL properly:

mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log%';"

Problem: Log file is empty

Solution: Lower the slow query threshold temporarily to force entries:

SET GLOBAL long_query_time = 0.1;

Then run queries again and check the log.

KEY TAKEAWAYS

• Enable the slow query log — to identify which queries are actually slow in your system
• Set an appropriate threshold — 2 seconds for most apps, 1 second for high-traffic, 0.1 seconds for aggressive profiling
• Enable log_queries_not_using_indexes — catches potential issues before they become performance problems
• Make changes permanent — update MySQL configuration file and restart MySQL
• Review logs regularly — at least weekly to catch trends and degraded performance
• Use mysqldumpslow — find worst queries by runtime or frequency
• Monitor log file size — clear or rotate to avoid storage issues
• Combine with other tools — use EXPLAIN and monitoring dashboards for full insight

RELATED ARTICLES

Want to dive deeper into query optimization?

• How to Fix Slow MySQL Queries: 7 Proven Strategies — Main guide covering all optimization techniques
• EXPLAIN Statement: Complete Guide to Query Analysis — Understand why queries are slow
• MySQL Indexing Strategy: How to Add Missing Indexes — Fix slow queries with proper indexing

Need help optimizing your MySQL queries?

Once you've identified slow queries using the slow query log, the next step is fixing them. Our database administrators can:

• Analyze your slow query log and identify patterns
• Determine root causes (missing indexes, poor schema design, etc.)
• Create an optimization roadmap
• Implement fixes and monitor improvements

Request Your MySQL Performance Audit to get started, or contact us at [+254 720 100 744] to discuss your specific situation.

AUTHOR BIO

Contact Us | Technoroots Website | LinkedIn