Why Is My Database Slow? How to Find and Fix Slow SQL Queries

Every database performance story starts the same way. The application launches, everything is snappy, the team is happy. Six months later a page that took 200 milliseconds takes two seconds. A year later, someone is apologizing to a client while a report loads. Nobody changed anything "big" — the data just grew, and queries that were harmless on 1,000 rows became expensive on 1,000,000.

I do database optimization work for a living, and the comforting truth is this: a slow database is almost never slow everywhere. It's usually five to ten queries doing 90% of the damage. Find them, fix them properly, and the whole application feels new again. Here's the process I actually follow.

Step 1: Stop guessing — measure

The worst way to fix a slow database is to start adding indexes at random based on intuition. The first job is evidence:

• Turn on slow-query logging. PostgreSQL's log_min_duration_statement (I usually start at 500ms) or MySQL's slow query log records every query that crosses the threshold, with its real duration. Within a day you'll have a suspect list, ranked by pain.

• Aggregate before you optimize. Tools like pg_stat_statements (PostgreSQL) show total time per query pattern — a mediocre query running 50,000 times a day hurts more than a terrible one that runs twice.

• Check the vital signs. Connection count, cache hit ratio, table sizes and the biggest indexes. Sometimes the "slow query" is a symptom of something dumber, like every application server opening its own flood of connections with no pool in front.

Only when you know which queries, how often, and at what cost do you touch anything.

Step 2: Read the execution plan like a detective

For each suspect query, EXPLAIN ANALYZE is the interrogation room. It runs the query and shows what the database actually did — which is frequently not what you assumed. The phrases I hunt for:

• Seq Scan on a large table — the database read the whole table because no usable index existed for the filter. Not always wrong (small tables, most rows matching), but on a big table with a selective filter, it's suspect number one.

• Rows removed by filter in the thousands or millions — the database is fetching mountains of rows to throw nearly all of them away. An index that matches the filter (and sort) ends this.

• External merge / disk sorts — sorting or joining spilled to disk because the working memory ran out or the row estimates were wildly off (often a sign of stale statistics).

• Nested loops with huge row counts — usually the N+1 problem in disguise, or a join missing its index on the inner side.

The plan doesn't just reveal the problem; it tells you which fix belongs to it.

Step 3: Apply the fixes that actually move the needle

In rough order of how often they save the day in my work:

The right index — not just an index. An index on (status, created_at) serves the query filtering by status and ordering by date; an index on (created_at, status) often doesn't. Composite indexes follow the leftmost-prefix rule, partial indexes (WHERE status = 'pending') can be tiny and surgical, and an index that no query uses is pure write overhead — remove those too. I go deeper on this in my guide to PostgreSQL indexes.

Kill the N+1. One query for a list, then one query per row for its details: fine at 10 rows, catastrophic at 1,000. Eager loading or a proper join collapses hundreds of round trips into one query.

Stop fetching what you don't display. SELECT * over wide tables, loading 10,000 rows to paginate in the application, returning full text columns for a list view — the database and the network both pay for data nobody sees. Select the columns you need; paginate in SQL.

Fix the query's shape. Functions wrapped around indexed columns (WHERE DATE(created_at) = ...) defeat indexes; rewriting to a range condition resurrects them. OR chains, leading-wildcard LIKE '%term%' searches and implicit type casts are the other usual suspects.

Then, and only then, tune the server. Shared buffers, work memory, connection pooling (PgBouncer), autovacuum behavior. Configuration multiplies good queries; it cannot rescue bad ones, which is why it comes last and not first.

Step 4: Prove it, then keep it fast

Every fix gets measured: the same query, before and after, real numbers in a table. "Feels faster" is not a result; "2,300ms → 14ms at the same data volume" is. This before/after discipline is the core of my database architecture and optimization service, because it also tells you when to stop — there's a point where another hour of tuning buys 3 milliseconds nobody will ever feel.

Then prevent the sequel: keep slow-query logging on permanently, alert on the thresholds, and review the top queries monthly. Databases rarely get slow in a day; they get slow in a hundred small commits, and the log is how you catch the trend while it's still cheap.

When the answer is "it's not (only) the database"

Honesty section: sometimes the profile shows the database is innocent — the time goes to a chatty API design, an uncached page re-rendering everything, or a server on the other side of the planet from its users. The audit tells you which layer is guilty before you spend money on the wrong one. My piece on why your Next.js site is slow covers the front-end half of that diagnosis.

If your application has been getting slower month after month, don't buy a bigger server yet. Send me your symptoms — a proper audit takes days, and in most cases the fix is a handful of queries, not a rebuild.