How to Identify Slow SQL Queries

A slow database request is not always caused by a poorly written SQL statement. It may be waiting for a lock, reading data from storage, spilling a sort to disk, using an inaccurate execution plan, or running far more frequently than expected.

Finding the cause requires more than looking for queries that take over an arbitrary number of seconds. You need to examine latency, frequency, resource consumption, execution plans, and concurrency together.

This guide presents a practical workflow for identifying slow SQL queries in MySQL, PostgreSQL, SQL Server, and Laravel applications.

What Makes a SQL Query “Slow”?

There is no universal threshold.

A 500-millisecond query might be acceptable for a monthly report but unacceptable for an API endpoint expected to respond in 200 milliseconds. A query that takes only 20 milliseconds can still become a serious problem if it runs thousands of times per second.

Evaluate queries using several measurements:

Average execution time
Median and percentile latency
Execution frequency
Total cumulative execution time
Rows examined or read
Rows returned
CPU consumption
Logical and physical reads
Temporary files or disk spills
Lock and wait time
Application impact

A useful first approximation is:

Cumulative database time =
average execution time × execution count

Consider two queries:

Query	Average duration	Calls per day	Approximate cumulative time
Daily report	5 seconds	1	5 seconds
Product lookup	100 ms	500,000	50,000 seconds

The report has higher individual latency, but the lookup creates far more cumulative database work.

Start with the Application Symptom

Before changing SQL, identify where users experience the delay.

Record:

Affected endpoint, job, command, or report
Date and time of the incident
Application instance
Database instance
Request or trace identifier
Tenant or customer scope
Query count for the request
Total database time
Time spent outside the database

This distinction matters. A page that takes three seconds might spend only 50 milliseconds running SQL and the rest waiting for an external API.

Distributed tracing or application-performance monitoring can show whether the delay occurs in application code, the database, a cache, a queue, or an external service.

Step 1: Capture Real Query Data

Avoid optimizing a query only because it looks inefficient. Capture evidence from the environment where the problem occurs.

Useful sources include:

Slow-query logs
Statement statistics
Application traces
Database wait statistics
Query Store
Performance Schema
PostgreSQL statistics extensions
Laravel query listeners
Production-safe monitoring platforms

Capture enough information to answer:

Which query pattern is responsible?
How often does it run?
What is its latency distribution?
Which application action triggers it?
Is it actively running or waiting?
Did its behavior change after a deployment?

Sensitive SQL parameters may appear in logs. Restrict access, define retention limits, and redact passwords, tokens, personal information, and payment data.

Find Slow Queries in MySQL

Enable the slow-query log carefully

MySQL’s slow-query log can record statements that take longer than long_query_time. It is disabled by default in many installations.⁠⁠

A temporary diagnostic configuration might look like:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;

This example logs queries exceeding approximately 500 milliseconds. It is not a recommended threshold for every application.

Important considerations:

Global configuration changes may require administrative privileges.
Some global changes affect only new database sessions.
A restart can remove nonpersistent settings.
Managed database services may require parameter groups or provider settings.
Logging too aggressively can increase storage and processing overhead.
SQL text can contain sensitive values.

Confirm the current configuration:

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

Do not enable the general query log as a substitute in a busy production environment without understanding its overhead.

Aggregate similar MySQL queries

Examining individual log lines is inefficient. Group queries by a normalized fingerprint so statements with different parameter values are analyzed together.

MySQL provides mysqldumpslow for summarizing slow-query logs. Monitoring products can also group statements and calculate frequency, mean latency, and total time.

The priority should usually be query patterns with:

High total execution time
High frequency
High rows examined relative to rows returned
High variance or poor tail latency
Recent regressions

Find Slow Queries in PostgreSQL

PostgreSQL can log statements that exceed log_min_duration_statement.

For persistent configuration, set the value through the database configuration or your managed provider rather than relying on an undocumented one-time change:

log_min_duration_statement = 500ms

Choose a threshold based on the workload and logging capacity.

For aggregate statistics, PostgreSQL provides the pg_stat_statements extension. After it has been correctly enabled, a useful report is:

SELECT
    queryid,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This identifies statements responsible for the greatest cumulative execution time.

Other useful orderings include:

ORDER BY mean_exec_time DESC

and:

ORDER BY calls DESC

pg_stat_statements usually requires server configuration and may require a restart before the extension can collect data. Follow the documentation for your PostgreSQL version and hosting provider.⁠⁠

Reset statistics only when you intentionally want to establish a new measurement window. Resetting them removes evidence that may be useful to other operators.

Find Slow Queries in SQL Server

SQL Server Query Store records query text, plans, and runtime statistics over time. It is particularly useful for identifying:

Regressed execution plans
Queries with high resource consumption
Changes after deployment
Queries with unstable performance
Differences between historical and current plans

Query Store is more valuable than examining one isolated execution because it preserves performance history.⁠⁠

Also examine SQL Server wait statistics and blocking information. A query can appear slow because it is waiting for another transaction rather than consuming CPU.

Step 2: Rank Queries by Impact

Do not sort only by maximum duration. A single outlier may be less important than a query that continuously consumes resources.

Create a shortlist using:

Total cumulative execution time
Execution frequency
High percentile latency
CPU time
Logical reads
Physical reads
Rows examined versus rows returned
Lock or wait time
Business importance

A practical priority score might combine frequency and average cost:

impact score =
calls × average duration

This is only a starting point. A checkout query may deserve immediate attention even if its total resource usage is lower than a background reporting job.

Step 3: Reproduce the Query Safely

Obtain:

Exact SQL or its normalized form
Representative parameter values
Table definitions
Existing indexes
Approximate row counts
Data distribution
Database version
Current execution plan

Parameter values matter. A query may be fast for one customer and slow for another because the number and distribution of matching rows differ.

Prefer reproducing the issue on:

A staging environment with representative data
A recent sanitized database copy
A read replica approved for diagnostics
A controlled production session when necessary

Do not run expensive experiments against production without considering load and locking.

Step 4: Read the Execution Plan

A normal EXPLAIN displays the optimizer’s estimated strategy.

MySQL:

EXPLAIN
SELECT id, email
FROM customers
WHERE email = 'john@example.com';

PostgreSQL:

EXPLAIN
SELECT id, email
FROM customers
WHERE email = 'john@example.com';

Estimated plans are safe for initial investigation because they do not normally execute the SELECT.

Actual execution provides much stronger evidence.

MySQL:

EXPLAIN ANALYZE
SELECT id, email
FROM customers
WHERE email = 'john@example.com';

PostgreSQL:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email
FROM customers
WHERE email = 'john@example.com';

Important warning

EXPLAIN ANALYZE executes the statement. A slow query can therefore remain slow and consume real resources during analysis.

Do not run it casually on:

Expensive production queries
Statements that may modify data
Queries that can lock large tables
Workloads with unknown parameters
Systems already under heavy load

Start with an estimated plan. Use actual execution only in a controlled environment or with an approved, safe query.

What to Look for in an Execution Plan

Estimated rows versus actual rows

Large differences suggest the optimizer misunderstood the data distribution.

For example:

Estimated rows: 10
Actual rows: 500,000

This can cause the optimizer to choose a join or access method that works for ten rows but performs poorly for half a million.

Possible causes include:

Outdated statistics
Skewed data
Correlated columns
Parameter-sensitive plans
Expressions that are difficult to estimate

Repeated operations

Check both the cost of an operation and how many times it runs.

A fast lookup repeated 100,000 times can dominate the plan:

Actual time per loop: 0.1 ms
Loops: 100,000

Rows read versus rows returned

A query that examines one million rows to return ten is a strong optimization candidate.

However, the ratio must be interpreted in context. Analytical queries may legitimately scan large datasets.

Sorts and temporary storage

Large sorting, grouping, or hashing operations may exceed memory and spill to disk.

Look for:

Temporary files
External merge sorts
Disk-based hash operations
Large intermediate result sets

Join methods

Common join algorithms include:

Nested-loop joins
Hash joins
Merge joins

No method is universally best. A nested loop can be excellent for a small outer result and indexed lookup, but expensive when repeated for millions of rows.

Waits and blocking

A query can show low CPU use while taking a long time because it is waiting for:

A row lock
A table or metadata lock
Storage
Memory
Network transfer
A worker thread
Another transaction

Separate active execution time from wait time before rewriting SQL.

A Full Table Scan Is Not Always Bad

A scan can be the correct plan when:

The table is small
The query returns most rows
An index lookup would require many random reads
The operation is an analytical aggregation
No selective filter exists
The index would not cover enough of the query

The question is not “Did the database scan a table?”

The better questions are:

How many rows were read?
How many were returned?
How often does the query run?
Is the table expected to grow?
Would a suitable index reduce total work?
What write and storage costs would that index introduce?

Do not force index usage merely to eliminate the word “scan” from a plan.

Missing Indexes

Suppose this query runs frequently:

SELECT id, email
FROM customers
WHERE email = ?;

If email is selective and the application frequently searches by it, an index may help:

CREATE INDEX idx_customers_email
    ON customers (email);

If email addresses must be unique, the data model may require a unique constraint instead:

CREATE UNIQUE INDEX uq_customers_email
    ON customers (email);

Before creating it, confirm:

Whether duplicate values already exist
Whether searches are case-sensitive
Whether values are normalized
Whether a suitable index already exists
Whether write overhead is acceptable
Whether the index fits the actual query pattern

An index is a data-model and workload decision, not an automatic fix.

Composite Indexes and Column Order

Consider:

SELECT id, created_at
FROM orders
WHERE customer_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 50;

A possible index is:

CREATE INDEX idx_orders_customer_status_created
    ON orders (customer_id, status, created_at);

The index order is based on the query’s equality filters and ordering requirements. But the best order depends on:

Selectivity
Other queries
Database engine
Sort direction support
Data distribution
Range conditions
Existing indexes

Do not create every possible column combination. Each additional index consumes storage and adds work to inserts, updates, deletes, backups, and maintenance.

Non-Sargable Predicates

A predicate is commonly called sargable when the database can use an index to narrow the search efficiently.

This pattern may prevent efficient use of a normal index on created_at:

SELECT id
FROM orders
WHERE DATE(created_at) = '2026-09-28';

A range preserves the original column:

SELECT id
FROM orders
WHERE created_at >= '2026-09-28 00:00:00'
  AND created_at <  '2026-09-29 00:00:00';

Other patterns that may reduce index usefulness include:

WHERE LOWER(email) = 'john@example.com'

WHERE amount + 10 > 100

WHERE name LIKE '%smith%'

Possible solutions include:

Rewriting the predicate
Normalizing data when it is stored
Using an expression or functional index when supported
Using full-text search for appropriate text-search requirements

Verify the resulting plan instead of assuming the rewrite helped.

Selecting Only Required Columns

Instead of:

SELECT *
FROM customers
WHERE email = ?;

use:

SELECT id, email, status
FROM customers
WHERE email = ?;

This can reduce:

Data read from storage
Memory use
Network transfer
Application serialization
Accidental coupling to schema changes

It can also make a covering-index strategy possible.

However, replacing SELECT * will not automatically fix a poor join, lock wait, or missing filter.

`LIMIT` Does Not Automatically Make a Query Fast

This query returns only 100 rows:

SELECT id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 100;

Without a useful index, the database may still scan and sort a large table before returning those rows.

A matching index may help:

CREATE INDEX idx_orders_created_at
    ON orders (created_at);

Also use deterministic ordering:

SELECT id, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 100;

For deep pagination, this can become expensive:

LIMIT 100 OFFSET 500000;

Keyset pagination is often more efficient:

SELECT id, created_at
FROM orders
WHERE created_at < ?
   OR (created_at = ? AND id < ?)
ORDER BY created_at DESC, id DESC
LIMIT 100;

The exact condition must match the ordering and cursor values.

Detect N+1 Queries

Sometimes the main SQL statement is fast, but the application runs hundreds of additional queries.

Example:

$orders = Order::latest()->take(100)->get();

foreach ($orders as $order) {
    echo $order->customer->name;
}

If customer is loaded lazily, this can trigger one customer query for each order.

Eager-load the relation:

$orders = Order::query()
    ->with('customer:id,name')
    ->latest()
    ->take(100)
    ->get();

The exact select list must retain any foreign keys required by the relationship.

N+1 problems are easy to miss in a slow-query log because each individual query may be fast. Application tracing and query-count monitoring are better tools for detecting them.

Monitor Queries in Laravel

Laravel provides database query events:

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\DB;

DB::listen(function (QueryExecuted $query) {
    logger()->debug('Database query', [
        'sql' => $query->sql,
        'time_ms' => $query->time,
    ]);
});

Do not log bindings or full SQL indiscriminately in production; they may contain confidential information and create large log volumes.

Laravel can also notify the application when cumulative query time during a request exceeds a threshold:

use Illuminate\Database\Connection;
use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\DB;

DB::whenQueryingForLongerThan(
    500,
    function (
        Connection $connection,
        QueryExecuted $event
    ) {
        // Report or sample the slow request.
    }
);

These capabilities are documented in Laravel’s database guide.⁠⁠

Useful application-level measurements include:

Total query count per request
Cumulative database time
Slowest individual statement
Repeated query fingerprints
Endpoint and job name
Trace identifier
N+1 detection

Locking and Long Transactions

When a normally fast query becomes intermittently slow, investigate blocking.

Common causes include:

Transactions left open
User interaction performed inside a transaction
Network calls made before commit
Batch updates touching many rows
Inconsistent update order
Missing indexes on updated or referenced rows
Schema changes waiting on metadata locks

Keep transactions focused:

DB::transaction(function () use ($order) {
    $order->update([
        'status' => 'paid',
    ]);

    Payment::create([
        'order_id' => $order->id,
    ]);
});

Avoid performing slow external HTTP requests inside the transaction unless the workflow explicitly requires the database locks to remain held.

A query blocked for five seconds does not necessarily need a new index. The blocking transaction may be the actual problem.

Benchmark Changes Correctly

Before changing the query, record a baseline:

Database version
Query and parameters
Row counts
Existing indexes
Execution plan
Execution time distribution
Buffers or logical reads
CPU time
Temporary storage
Concurrency
Cache state

After the change:

Run the same representative workload.
Compare actual plans.
Check both average and tail latency.
Measure resource use.
Test concurrent activity.
Check write performance.
Monitor after deployment.

A query may become faster in isolation while increasing lock contention or slowing writes.

A Reliable Slow-Query Workflow

Use this investigation sequence:

1. Confirm the user-facing symptom

Identify the endpoint, report, job, or command and its latency budget.

2. Capture production evidence

Use statement statistics, traces, logs, and wait information.

3. Group by query fingerprint

Analyze query patterns rather than individual parameter values.

4. Rank by total impact

Consider frequency, latency, resource use, and business importance.

5. Separate execution from waiting

Check locks, I/O waits, memory pressure, and network delays.

6. Inspect estimated and actual plans

Compare estimates with real rows and loops in a controlled environment.

7. Form one specific hypothesis

Examples:

Missing composite index
Incorrect row estimate
N+1 application behavior
Locking transaction
Disk-based sort
Non-sargable predicate

8. Make the smallest targeted change

Avoid combining a query rewrite, three indexes, and server configuration changes in one test.

9. Benchmark under representative load

Measure read performance, write cost, and concurrency.

10. Deploy gradually and monitor

Keep rollback instructions and compare performance with the baseline.

Common Investigation Mistakes

Avoid these common errors:

Defining “slow” with one universal threshold
Optimizing SQL without confirming the database is the bottleneck
Looking only at average latency
Ignoring execution frequency
Treating every table scan as a failure
Adding indexes without measuring write cost
Running EXPLAIN ANALYZE carelessly in production
Testing with unrepresentative data
Ignoring parameter-sensitive behavior
Using LIMIT without deterministic ordering
Missing application-level N+1 queries
Logging sensitive SQL parameters
Measuring only warm-cache performance
Declaring success after one execution

Final Thoughts

Identifying slow SQL queries is an evidence-gathering process, not a hunt for one suspicious line of SQL.

Start with the affected application operation. Capture real workload data. Rank normalized query patterns by total impact. Determine whether the time is spent executing, reading, sorting, or waiting. Then compare estimated and actual execution plans before making a targeted change.

The most useful principles are:

Measure duration and frequency together.
Inspect rows read, not only rows returned.
Treat indexes as workload trade-offs.
Investigate locks and N+1 behavior.
Use actual plans carefully.
Benchmark before and after every change.
Monitor the result after deployment.

A successful optimization is not merely a faster query in a development console. It is a measurable improvement to application latency and database capacity under representative production conditions.