Skip to content
← All articlesEngineering

Fixing Slow Database Queries: A Guide for Product Teams

Why apps slow down as data grows and how to find and fix slow database queries with indexes, query plans and caching, without a costly rewrite.

Many applications feel fast for the first six months and then start to crawl. Pages that loaded instantly now take several seconds, reports time out, and the team’s first instinct is to buy a bigger server. Sometimes that helps for a while. More often the real cause is a handful of slow database queries that were fine with a thousand rows and are painful with ten million.

This guide explains, in plain terms, how to find those queries, what usually causes them, and how to fix them in a sensible order. It is written for CTOs, founders and product managers who need to ask the right questions of their engineering team or outsourcing partner.

Why queries get slow as data grows

A database can answer a question in two broad ways: read every row and check it, or jump straight to the matching rows using an index. With small tables the difference is invisible. As tables grow, a query that reads every row takes longer in proportion, while an indexed lookup stays almost flat.

Slowness also comes from how the application asks. A single page that triggers hundreds of small queries, or one query that pulls far more data than the screen needs, will hurt long before the database itself is stressed.

Step 1: Measure before you change anything

Guessing is the most expensive way to tune a database. Start by finding out which queries actually hurt.

  • Slow query log. Most databases, including PostgreSQL and MySQL, can log any query that runs longer than a threshold you choose.
  • Query statistics. PostgreSQL’s pg_stat_statements extension and similar tools in other engines show which queries consume the most total time, not just the slowest single run.
  • Application performance monitoring. Tracing tools show which user actions trigger which queries, so you fix what users actually feel.

Rank queries by total time spent (frequency multiplied by duration). A query that takes 50 milliseconds but runs 100,000 times a day matters more than one that takes five seconds once a week. Our guide to observability with logs, metrics and traces covers how to set this monitoring up.

Step 2: Read the query plan

Every mainstream database can explain how it intends to run a query. In PostgreSQL and MySQL this is the EXPLAIN command, and EXPLAIN ANALYZE in PostgreSQL also runs the query and reports real timings. You do not need to be an expert to spot the common warning signs:

  • A sequential or full table scan on a large table where you expected an index to be used.
  • A large gap between the estimated and actual row counts, which suggests outdated table statistics.
  • A sort or join spilling to disk because it needs more memory than is available.
  • A nested loop over a very large set, repeated for every row.

Step 3: Fix the usual suspects

Most slow-query problems fall into a short list. Work through it in this order, because the early items are cheap and the later ones are not.

Cause Typical symptom Typical fix
Missing index Full table scan on a filtered column Add an index on the columns used in WHERE, JOIN and ORDER BY
Wrong index order Index exists but is not used Reorder composite index columns to match the query
N+1 queries One list page runs hundreds of queries Fetch related data in one query or a batched load
Selecting too much Very wide rows returned for a small UI Select only the needed columns; paginate
Deep offset pagination Page 5,000 is slower than page 1 Use keyset (cursor) pagination
Functions on indexed columns Index ignored in the query plan Rewrite the condition or add an expression index
Heavy reporting on the live database Dashboards slow the whole app Use a read replica or a separate reporting store

A note on indexes

Indexes are not free. Each one speeds up reads but slows down writes and uses storage, so adding one for every column is a mistake. Create indexes for queries you have measured, and remove ones that are never used. On large live tables, build indexes using the database’s online or concurrent option so you do not lock writes; the same care applies to schema changes in general, as we describe in database migrations without downtime.

Step 4: Reduce how often you ask

Once the queries themselves are sensible, reduce the number of times the database is asked at all.

  • Caching. Store the results of expensive, frequently repeated reads in memory, with a clear expiry or invalidation rule. See our web application caching strategies guide for the trade-offs.
  • Connection pooling. Opening a new connection per request wastes time and can exhaust database limits under load.
  • Precomputed summaries. Counts, totals and leaderboards can be updated incrementally or refreshed on a schedule rather than recalculated on every page view.

What to avoid

  • Scaling hardware first. It hides the problem, raises the bill and the issue returns when data doubles.
  • Switching databases as a first response. The slow query usually moves with you.
  • Tuning without a test. Always compare timings on production-sized data, since a query that is quick on a development laptop with a thousand rows proves nothing.
  • One-off heroics. Without monitoring, the same problem will come back with the next feature.

A simple checklist for your team

  • Is the slow query log or query statistics tool switched on in production?
  • Do we know the top five queries by total time?
  • Have we looked at the query plan for each, not just the SQL?
  • Are list pages free of N+1 query patterns?
  • Are indexes based on measured queries, and are unused ones reviewed?
  • Do we load-test with realistic data volumes before big releases?

Getting help

Database performance work rewards a methodical approach: measure, read the plan, apply the smallest fix, and measure again. If your team lacks the time or the experience, an experienced partner can run this audit quickly and leave you with monitoring so the problem stays solved. Our web development and application support and maintenance teams handle exactly this kind of work for international clients. Contact us to discuss a performance review of your application.

Let’s build what’s next.

Tell us about your platform, your product idea or the systems you want to modernise. Someone from our Chennai engineering team will get back to you personally.