How to Find Slow MySQL Queries: A Practical Guide to Diagnosing Database Performance

A slow MySQL query can make an otherwise fast application feel slow.

The difficult part is finding the actual cause. A query taking two seconds may run only a few times a day, while a 30-millisecond query executed thousands of times can consume far more database resources.

Before adding indexes, increasing server resources, or changing MySQL configuration, identify which queries are slow, how frequently they run, and why they are expensive.

This guide explains how to find slow MySQL queries using the slow query log, Performance Schema, EXPLAIN, and EXPLAIN ANALYZE.

Quick Answer

The most practical way to find slow MySQL queries is to use the slow query log and Performance Schema to identify expensive query patterns, then use EXPLAIN or EXPLAIN ANALYZE to determine why those queries are slow.

Look at more than execution time. Query frequency, rows examined, index usage, locking, and total database workload are equally important.

TL;DR

• Use the MySQL slow query log to find queries exceeding your chosen threshold.
• Use Performance Schema to identify frequently executed query patterns.
• Check both execution time and query frequency.
• Use EXPLAIN to inspect the execution plan.
• Use EXPLAIN ANALYZE to compare estimates with actual execution.
• Check indexes, rows examined, joins, sorting, and locking.
• Fix the underlying bottleneck before scaling infrastructure.
• Measure performance again after making changes.

Why Do MySQL Queries Become Slow?

Common causes include:

Missing or ineffective indexes

A query filtering millions of rows without a suitable index may require MySQL to inspect a large part of the table.

SELECT *
FROM orders
WHERE customer_id = 1001;

An index may help, but the correct index depends on the complete query pattern.

Too many rows examined

A query returning 20 rows may still examine hundreds of thousands of rows.

For example:

Rows sent:      20
Rows examined:  850,000

That is a strong reason to investigate the execution plan.

Inefficient joins

Large joins without suitable indexes can become expensive as tables grow.

Sorting and temporary operations

ORDER BY, GROUP BY, DISTINCT, and complex joins can require additional processing.

Lock contention

A query can be slow because it is waiting for another transaction rather than because the SQL itself is computationally expensive.

Application-level problems

The database may also be overloaded because the application sends too many queries.

For example, an N+1 query problem can generate hundreds of queries during a single request.

How to Find Slow MySQL Queries

1. Check the Slow Query Log

MySQL provides a slow query log specifically for identifying queries that take longer than a configured threshold.

Check the current settings:

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

You can enable the log when appropriate:

SET GLOBAL slow_query_log = 'ON';

For investigation, you can configure the threshold:

SET GLOBAL long_query_time = 1;

This allows queries taking approximately one second or longer to be logged.

The correct threshold depends on the application. Setting it too high can miss useful queries, while setting it extremely low can generate excessive logging.

MySQL documents long_query_time as the threshold used by the slow query log. Reference

2. Analyze the Slow Query Log

A slow-query entry may contain information such as:

# Query_time: 2.431
# Lock_time: 0.001
# Rows_sent: 20
# Rows_examined: 850000

Pay particular attention to:

Query_time: How long the query took.

Lock_time: Time associated with waiting for locks.

Rows_sent: Number of rows returned.

Rows_examined: Number of rows MySQL examined while processing the query.

For example:

Rows sent:      10
Rows examined:  900,000

The query returns very little data but performs a large amount of work.

That makes it a strong candidate for optimization.

3. Use Performance Schema

The slow query log shows individual slow statements, but Performance Schema can help you understand query patterns and frequency.

MySQL’s events_statements_summary_by_digest table aggregates structurally similar statements.

For example:

SELECT *
FROM users
WHERE id = 10;

and:

SELECT *
FROM users
WHERE id = 20;

can be grouped as the same statement pattern.

A useful query is:

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

This helps identify statement patterns consuming significant database time.

You can also find frequently executed statements:

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC
LIMIT 20;

This is important because the slowest query is not always the biggest problem.

4. Consider Query Frequency

Suppose you find:

Query A
Average time: 3 seconds
Executions: 10/day

and:

Query B
Average time: 30 ms
Executions: 500,000/day

Query A is much slower individually.

However, Query B may create more total database work because it executes so frequently.

A useful way to prioritize investigations is:

Total workload ≈ execution time × execution frequency

It is not a database performance formula, but it is a useful diagnostic model.

5. Use EXPLAIN

Once you’ve identified an expensive query, inspect its execution plan.

For example:

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 1001;

EXPLAIN helps show how MySQL plans to access tables and indexes.

Look for:

• Full table scans
• Large row estimates
• Unexpected indexes
• Inefficient joins
• Poor filtering
• Sorting operations
• Temporary tables

Do not interpret one execution-plan field in isolation. Consider the complete query and the size and distribution of the data.

For more detail, see our supporting article:

EXPLAIN vs EXPLAIN ANALYZE

6. Use EXPLAIN ANALYZE

EXPLAIN provides the optimizer’s plan and estimates.

EXPLAIN ANALYZE goes further by executing the statement and reporting actual execution information.

For example:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 1001;

This helps answer:

Did MySQL’s estimates match what actually happened?

If estimated and actual behavior differ significantly, further investigation may be necessary.

This can be particularly useful when diagnosing unexpected query performance.

7. Check Indexes and Application Query Count

Once you identify a slow query, inspect the existing indexes:

SHOW INDEX FROM orders;

Don’t immediately create a new index.

First determine whether the existing index supports the query efficiently.

For example:

SELECT id, status, created_at
FROM orders
WHERE customer_id = 1001
ORDER BY created_at DESC
LIMIT 20;

Depending on the workload, a composite index could be useful:

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at);

But the index should be validated using the execution plan and actual performance.

See:

MySQL Indexing Mistakes

You should also check how many queries the application executes.

For example:

One HTTP request
      ↓
100 product queries
      ↓
100 category queries
      ↓
100 inventory queries

Even if each query is fast, hundreds of queries per request can create a serious database bottleneck.

This is particularly common with Laravel N+1 queries.

For Laravel-specific techniques, see:

Laravel Database Optimization

8. Check Locking

A slow query is not necessarily doing expensive computation.

It may be waiting for another transaction.

For example:

Transaction A
     ↓
Locks row
     ↓
Long-running transaction

Transaction B
     ↓
Attempts update
     ↓
Waits

If query latency is high but CPU usage does not explain it, investigate:

• Long-running transactions
• Lock waits
• Deadlocks
• Large updates
• Large deletes
• Transactions left open too long

This is especially important for write-heavy applications.

Real-World Example

Consider an e-commerce application running:

SELECT *
FROM orders
WHERE customer_id = 5021
ORDER BY created_at DESC
LIMIT 20;

Monitoring shows:

Average execution time: 850 ms
Rows returned: 20
Rows examined: 1,200,000

The query returns only 20 rows but examines more than a million.

Instead of immediately increasing server resources, inspect the execution plan:

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 5021
ORDER BY created_at DESC
LIMIT 20;

Then review the available indexes and test an appropriate optimization.

The general workflow is:

Slow query
    ↓
Measure
    ↓
Check rows examined
    ↓
EXPLAIN
    ↓
Review indexes
    ↓
Optimize
    ↓
Measure again

Production Considerations

Choose a sensible logging threshold

Don’t configure an unnecessarily aggressive threshold on a high-volume production database.

Establish a baseline

Record metrics before making changes:

• Query latency
• Query count
• Rows examined
• Database CPU
• P95/P99 latency

Test with realistic data

A query that works well with 10,000 rows may behave very differently with 100 million rows.

Consider the wider workload

An index can improve reads while increasing the cost of writes.

Measure after every significant change

Don’t assume an optimization worked. Compare the before-and-after results.

Slow MySQL Query Investigation Checklist

• Identify the affected endpoint

• Check application latency

• Inspect the slow query log

• Check query frequency

• Check rows examined

• Run EXPLAIN

• Use EXPLAIN ANALYZE where appropriate

• Review indexes

• Check application query count

• Check lock waits

• Make one controlled change

• Measure again