MySQL Slow Query Log: Finding Database Performance Issues

Your web application runs fine for hours, then suddenly slows to a crawl. Users complain about timeouts. Your VPS CPU spikes to 100%. You check application logs—nothing. You check system resources—plenty of free RAM. The culprit? A slow database query you never knew existed.

Most developers focus on optimizing application code, but database performance is often the real bottleneck. A single unoptimized SQL query can bring an entire application to its knees. The problem is: how do you find slow queries in the first place?

The answer is the MySQL slow query log. This is a built-in MySQL feature that logs every query slower than a configurable threshold. By analyzing these slow queries, you can identify exactly which database operations are dragging down your application, then optimize them for better performance.

For Indian startups and SMBs running PHP, Node.js, or Python applications on VPS servers, understanding MySQL optimization is critical. In this guide, we’ll walk through enabling the slow query log, analyzing slow queries, and using that data to improve database performance and accelerate your application.

Why Database Performance Matters

Before we dive into slow query logging, let’s understand why MySQL optimization is so critical.

Slow databases impact:

• User experience — Page loads take 5+ seconds instead of <1 second
• Server resources — Slow queries lock resources, affecting other operations
• Scalability — As traffic grows, slow queries compound exponentially
• Revenue — Users abandon slow sites (each 1-second delay = ~7% conversion loss)
• DevOps load — You spend nights debugging mysterious slowdowns instead of shipping features

Database query performance directly impacts:

• Application responsiveness — Is your API returning data in milliseconds or seconds?
• Resource utilization — CPU and disk I/O can be maxed by inefficient queries
• Concurrent user capacity — More slow queries = fewer users you can serve simultaneously
• Cost — Fewer resources needed for optimized databases = cheaper VPS hosting bills

Most slow queries are preventable through proper indexing, query optimization, and connection pooling. The first step is identifying them.

Understanding Query Execution Time

Not all queries need optimization. A query that takes 2 seconds to run 1,000 reports is fine. A query that takes 2 seconds to load a user profile on every page load is a disaster.

Slow query threshold guidelines:

• < 0.01 seconds (10ms) — Excellent (acceptable for all queries)
• 0.01 - 0.1 seconds (10-100ms) — Good (acceptable for most queries)
• 0.1 - 1 second — Slow (investigate and optimize)
• > 1 second — Very slow (critical to optimize)

You’ll configure the slow query log to capture anything above your threshold. For development: 0.1 seconds. For production: 0.5-1 second (adjust based on traffic).

Step 1: Enable the MySQL Slow Query Log

SSH into your VPS and open the MySQL configuration file.

On Ubuntu/Debian:

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

On CentOS/RHEL:

sudo nano /etc/my.cnf

Find the [mysqld] section and add these lines:

# Enable slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

What each setting does:

• slow_query_log = 1 — Enables the slow query log (0 = disabled, 1 = enabled)
• slow_query_log_file — Path to the log file (create this file if it doesn’t exist)
• long_query_time — Queries slower than this many seconds are logged (1 = 1 second threshold)
• log_queries_not_using_indexes — Also logs queries that don’t use indexes (risky on high-traffic sites; use with caution)

For development environments: set long_query_time = 0.1 (capture queries >100ms).

For production: set long_query_time = 1 (avoid logging noise from high-traffic operations).

Save the file (Ctrl+X, Y, Enter).

Step 2: Create the Slow Query Log File

Create and set permissions for the log file:

sudo mkdir -p /var/log/mysql
sudo touch /var/log/mysql/slow.log
sudo chown mysql:mysql /var/log/mysql/slow.log
sudo chmod 640 /var/log/mysql/slow.log

Step 3: Restart MySQL

Apply the configuration changes:

sudo systemctl restart mysql

Verify MySQL is running:

sudo systemctl status mysql

Step 4: Test the Slow Query Log

Generate some slow queries to verify logging works:

mysql -u root -p

Enter your MySQL password, then run a deliberate slow query:

SELECT * FROM large_table WHERE status = 'active' LIMIT 1;
-- Run this sleep command to simulate a slow query
SELECT SLEEP(2);

Exit MySQL:

exit;

Check the slow query log:

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

You should see your SLEEP query logged. Great! The slow query log is working.

Step 5: Analyze Your Slow Query Log

Once you’ve been running the slow query log for a few hours or days, analyze the results.

View recent slow queries:

sudo tail -n 50 /var/log/mysql/slow.log

Count occurrences of slow queries:

sudo grep -c "Query_time" /var/log/mysql/slow.log

Find the slowest query:

sudo grep "Query_time:" /var/log/mysql/slow.log | sort -t= -k2 -nr | head -1

Parse with MySQL tools. MySQL provides mysqldumpslow to analyze the log:

sudo mysqldumpslow /var/log/mysql/slow.log | head -20

Output:

Count: 2345  Time=2.34s (5493s)  Lock=0.01s (23s)  Rows_sent=15.2 (35671)  Rows_examined=45,000.0 (10576234)
SELECT * FROM orders WHERE customer_id = ? AND created_at > ?;

Count: 1200  Time=1.56s (1872s)  Lock=0.02s (24s)  Rows_sent=42.1 (50520)  Rows_examined=1,000,000.0 (1200000000)
SELECT * FROM products WHERE category = ? ORDER BY price DESC;

This tells you:

• How many times each query ran
• Total execution time
• Average time per execution
• Rows examined vs. rows returned (efficiency indicator)

Step 6: Optimize Your Slow Queries

Once you’ve identified the slow database queries, optimize them.

1. Add indexes

-- Slow query: SELECT * FROM orders WHERE customer_id = ?
-- Solution: Add an index
CREATE INDEX idx_customer_id ON orders(customer_id);

2. Fix the query

-- Inefficient: SELECT * (fetches all columns)
SELECT * FROM orders WHERE customer_id = 1;

-- Optimized: SELECT only needed columns
SELECT id, order_date, amount FROM orders WHERE customer_id = 1;

3. Use EXPLAIN to understand query execution

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

Look for:

• type = ALL — Full table scan (bad, needs index)
• type = ref — Using an index (good)
• rows — How many rows examined (lower is better)
• Extra = Using index — Uses only the index, no table access (excellent)

4. Add LIMIT if fetching many rows

-- Without LIMIT: Returns 1 million rows
SELECT * FROM logs WHERE timestamp > DATE_SUB(NOW(), INTERVAL 30 DAY);

-- With LIMIT: Returns only what you need
SELECT * FROM logs WHERE timestamp > DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 1000;

Step 7: Monitor Ongoing Performance

Disable slow query logging temporarily to reduce overhead:

sudo mysql -u root -p -e "SET GLOBAL slow_query_log = 0;"

Re-enable when needed:

sudo mysql -u root -p -e "SET GLOBAL slow_query_log = 1;"

Rotate the log file regularly (it can grow large):

sudo logrotate -f /etc/logrotate.d/mysql-server

Set up automated log rotation (add to MySQL config):

slow_query_log_file = /var/log/mysql/slow.log
max_slowlog_size = 10G

Hostzop Cloud: Accelerate Database Performance on Your VPS

Identifying and fixing slow database queries is one of the fastest ways to improve application performance, but most developers don’t have the database expertise to analyze MySQL slow query logs or implement database optimization strategies. Hostzop Cloud empowers Indian developers with VPS solutions optimized for MySQL performance, including detailed guides on slow query logging, database optimization, and query analysis. Whether you’re running a WordPress site, a Laravel application, or a Node.js backend, Hostzop Cloud’s transparent platform provides the tools, documentation, and expert support needed to identify database performance bottlenecks and accelerate your application’s query execution speed. With built-in database monitoring, pre-configured MySQL settings, and responsive technical support, Hostzop Cloud helps you achieve fast database performance and responsive applications at transparent, affordable INR pricing.

Advanced: Using Percona Toolkit for Deep Analysis

For sophisticated analysis, install Percona Toolkit (free, open-source):

sudo apt install percona-toolkit

Analyze your slow query log:

pt-query-digest /var/log/mysql/slow.log > analysis.txt
cat analysis.txt

Output includes:

• Query fingerprint (groups similar queries)
• Execution statistics
• Recommendations for optimization

Troubleshooting

“Permission denied” accessing slow.log: Fix permissions:

sudo chown mysql:mysql /var/log/mysql/slow.log
sudo chmod 640 /var/log/mysql/slow.log

Slow query log isn’t capturing queries: Verify settings:

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

Restart MySQL after config changes:

sudo systemctl restart mysql

Slow query log growing too large: Reduce long_query_time threshold or implement log rotation:

sudo logrotate -f /etc/logrotate.d/mysql-server

Frequently Asked Questions

Should I enable log_queries_not_using_indexes on production?

Use with caution. It generates a lot of noise on high-traffic sites. Instead, manually review key queries for missing indexes. Enable temporarily when investigating specific performance issues.

What’s the performance impact of slow query logging?

Minimal (< 1% overhead). Logging is fast; the bottleneck is the slow queries themselves, not the logging. However, disable it after analysis to reduce disk I/O.

Can I use the slow query log on older MySQL versions?

Yes, slow query logging exists in MySQL 5.x and all modern versions. Syntax varies slightly; check your version’s documentation.

How often should I review the slow query log?

After each major code deployment, and at least weekly in production. As your application grows, traffic patterns change—previously fast queries might become slow.

Should slow query analysis be automated?

Absolutely. Set up alerts (via Datadog, New Relic, or custom scripts) to notify you of new slow queries. Automate pt-query-digest analysis and send reports weekly.

What if my query is slow but indexes won’t help?

Sometimes you need architectural changes: caching (Redis), database replication, partitioning large tables, or denormalizing data. Complex queries might need redesign. Consider consulting a database specialist.

How do I know if a query is “slow enough” to optimize?

If it runs frequently and takes >0.5 seconds, optimize it. If it runs once per day and takes 10 seconds, it’s probably fine. Balance frequency × duration = optimization priority.

Can I log slow queries from application-level?

Yes, many frameworks (Laravel, Django, etc.) have query logging built-in. But MySQL’s slow query log is more comprehensive and catches queries you might not suspect in your code.