How to Find and Fix Slow SQL Queries

A page that used to load instantly now takes eight seconds. A nightly report that once finished by 2 a.m. is still running at breakfast. Nine times out of ten, the cause is one or two SQL queries that have become slow as data grew. Slow query optimization is the skill of finding those queries, understanding why they are slow, and rewriting them or the schema around them. This guide covers the workflow and the patterns that cause most of the trouble.

Part 1: Finding the slow queries

You cannot fix what you have not found, and users' complaints rarely point at the exact query. Use the database's own tools.

MySQL and MariaDB

Enable the slow query log with a threshold such as one second (long_query_time = 1), then summarise it with mysqldumpslow or pt-query-digest. These group queries that differ only in their literal values, so you see "this query shape ran 40,000 times and took 3 hours in total".

PostgreSQL

The pg_stat_statements extension tracks every normalised query with its call count and timing:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time, 1) AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

The extension must also be listed in shared_preload_libraries, which requires a restart. Many managed cloud databases enable it by default.

Application monitoring

Application performance monitoring (APM) tools trace each web request and show which queries ran during it. This is the best way to connect "the checkout page is slow" to a specific statement.

Part 2: Reading the execution plan

An execution plan is the database's step-by-step recipe for running your query: which tables to read, in what order, using which indexes, and how to join them. Ask for it with EXPLAIN. Better still, use EXPLAIN ANALYZE, which runs the query and shows actual row counts and timings alongside the estimates.

EXPLAIN ANALYZE
SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2024-09-01'
GROUP BY c.name;

Be careful: EXPLAIN ANALYZE really executes the statement, so wrap data-changing queries in a transaction you roll back. When reading the output, look for:

Sequential or full scans on large tables where you expected an index lookup.
Large gaps between estimated and actual rows. If the planner expected 50 rows and got 500,000, its statistics are stale or the data is skewed. Running ANALYZE (PostgreSQL) or ANALYZE TABLE (MySQL) refreshes them.
Sorts and hash operations spilling to disk, reported as "external merge" or "Using temporary; Using filesort".
Nested loops over big inputs, where an inner step runs thousands of times.

Part 3: Common anti-patterns and their fixes

Most slow queries fall into a small number of recognisable patterns.

Functions wrapped around indexed columns

An index on created_at cannot be used if the column is transformed first:

-- Slow: the function hides the column from the index
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Fast: compare the raw column against a range
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

The same applies to LOWER(email) = .... Either store a normalised value or, in PostgreSQL and MySQL 8, create an index on the expression itself.

Implicit type conversion

If phone is a text column and you write WHERE phone = 9876543210 without quotes, MySQL converts every row's value to a number before comparing, and the index is ignored. Match the literal to the column's type.

SELECT * on wide tables

Fetching every column drags large text and JSON fields across the network and prevents covering indexes from helping. Ask only for the columns you use.

Deep OFFSET pagination

SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 200000;

The database must read and discard 200,000 rows before returning 20. Keyset pagination remembers where the last page ended instead:

SELECT * FROM products WHERE id > 200020 ORDER BY id LIMIT 20;

Correlated subqueries

A subquery that references the outer row can run once per row. Rewriting it as a join or a grouped derived table often helps, though modern optimisers sometimes do this automatically. Check the plan rather than assuming.

OR across different columns

WHERE email = ? OR phone = ? may force a scan even when both columns are indexed. Splitting it into two indexed queries joined by UNION can be much faster.

The N+1 problem

Not slow individually, but deadly in bulk: the application runs one query to load a list, then one query per item to load related data. The fix lives in the application code. Load related rows in one query with a join or an IN (...) list.

Part 4: Fixes beyond rewriting

Sometimes the query is reasonable and the surroundings need to change:

Add or adjust an index matching the filter and sort columns.
Pre-aggregate heavy report data into a summary table refreshed on a schedule, or a materialised view in PostgreSQL.
Archive old data that is rarely queried, or partition large tables by date.
Cache results that do not need to be real-time.
Move reporting to a read replica so long analytical queries do not compete with customer traffic.

Part 5: Make slow query optimization routine

After every change, re-run EXPLAIN ANALYZE with realistic parameters, and test with values that return many rows as well as few. Keep the slow query log or pg_stat_statements running permanently so new problems appear in monitoring before customers notice. For teams without in-house database expertise, our database management and software consulting services can audit the worst offenders.

Key takeaways

Find slow queries with the slow query log, pg_stat_statements or APM, and rank them by total time.
Use EXPLAIN ANALYZE to see what the database actually does, not what you assume.
Watch for functions on indexed columns, type mismatches, deep offsets, SELECT * and N+1 patterns.
Keep monitoring on so slow query optimization becomes routine rather than an emergency.

Databases articles