Database performance directly impacts the speed, scalability, and user experience of any web application. Whether you are working on a Laravel project, an e-commerce platform, or a microservices ecosystem, slow SQL queries can silently become the biggest bottleneck.
MySQL provides a powerful built-in feature called the Slow Query Log that helps developers detect performance issues at the database level with full accuracy.

This blog explains what Slow Query Log is, how to enable it, how to read it, and why it is essential for optimizing your application.

What Is MySQL Slow Query Log?

The Slow Query Log is a diagnostic feature in MySQL that records any SQL query that takes longer than a defined threshold to execute.
For example, if you set the threshold to 1 second, then any query taking more than 1 second will be recorded in the log file.

This helps you find:

• Queries missing indexes
• Queries doing full table scans
• Heavy joins
• Subqueries causing delays
• Functions applied on indexed columns
• N+1 query patterns in frameworks like Laravel

Why Do You Need the Slow Query Log?

Database slowness usually starts increasing as your application grows:

• More users
• More data
• More calculations
• More API requests

If you don’t monitor this, your pages, APIs, and reports start slowing down.

Slow Query Log helps you:

• Detect performance bottlenecks early
• Identify which queries are responsible
• Analyse execution time and rows scanned
• Improve indexing strategy
• Avoid unnecessary table scans
• Make your application fast and scalable

In short, it acts as a microscope for your database.

How to Check if Slow Query Log Is Enabled

Open phpMyAdmin or MySQL client and run:

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

If slow_query_log is OFF, you need to enable it.

How to Enable the MySQL Slow Query Log

MySQL configuration depends on the environment. Below are simple instructions for both Windows (XAMPP) and Linux servers.

Enable Slow Query Log in XAMPP (Windows)

1. Open file:
   C:\xampp\mysql\bin\my.ini
2. Add these lines under [mysqld]: slow_query_log = 1 slow_query_log_file = "C:/xampp/mysql/data/mysql-slow.log" long_query_time = 1 log_queries_not_using_indexes = 1
3. Restart MySQL from XAMPP.
4. Log file will appear here:
   C:\xampp\mysql\data\mysql-slow.log

Enable Slow Query Log in Linux / Ubuntu Server

1. Edit MySQL config: /etc/mysql/mysql.conf.d/mysqld.cnf
2. Add: slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = 1
3. Restart MySQL: sudo systemctl restart mysql
4. View log: sudo tail -f /var/log/mysql/slow.log

How to Read the Slow Query Log

A sample slow query entry looks like this:

# Query_time: 3.502  Lock_time: 0.000  Rows_sent: 10  Rows_examined: 125000
SELECT * FROM hospitals WHERE name LIKE '%care%';

Understanding the fields:

• Query_time: Total execution time
• Lock_time: Time waiting for locks
• Rows_sent: Records returned
• Rows_examined: How many rows MySQL had to scan
• Query: The SQL statement that caused the delay

A high Rows_examined value usually indicates a missing index.

How to Analyze Slow Queries

1. Using phpMyAdmin

Go to:

Status → Monitor → Slow Queries

You can see:

• Total slow queries
• Which queries took the most time
• Average execution time
• Table scans

2. Using Linux Tools

mysqldumpslow (built-in):

mysqldumpslow /var/log/mysql/slow.log

pt-query-digest (Percona Toolkit):

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

This gives extremely detailed analysis:

• Top slow queries
• Execution count
• Time distribution
• Recommendations

Common Causes of Slow Queries

1. Missing Indexes

If WHERE, JOIN, or ORDER BY columns are not indexed, MySQL will scan entire tables.

2. Large table scans

Queries like:

SELECT * FROM table

or using % wildcard at the beginning (LIKE '%text') cause full scans.

3. N+1 Problem (Eloquent)

Too many repeated queries triggered by bad relationships.

4. Heavy JOINS

Joining large tables without proper indexes.

5. Functions on indexed columns

Such as:

WHERE DATE(created_at) = '2024-06-01'

Breaks index usage.

Best Practices to Fix Slow Queries

• Add proper indexes
• Avoid SELECT *
• Use pagination
• Use EXPLAIN to inspect query plan
• Optimize JOIN conditions
• Use caching where possible
• Avoid subqueries if JOIN can work
• Break big queries into smaller batches

Recommended Slow Query Settings (For Real Projects)

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

These settings help you catch real-world performance issues accurately.

Conclusion

MySQL Slow Query Log is one of the most powerful ways to understand what is slowing down your application. Unlike basic monitoring tools, this gives a precise view of what’s happening inside the database engine.

By regularly reviewing this log and optimizing queries, you can significantly improve the speed, stability, and scalability of your Laravel or PHP application.

If you’re building systems like MotoShare, MyHospitalNow, or any high-traffic application, enabling the Slow Query Log is not optional — it is essential.