SQL Query Optimization: A Practical Guide for Developers

Most slow apps aren’t slow because the code is bad. They’re slow because the database is doing far more work than it needs to. The good news is that query optimization isn’t a dark art. It’s a handful of habits you can build into how you write and review queries.

Start with the query plan, not the query

Before you change a single line of SQL, look at what the database is actually doing. In PostgreSQL, that’s EXPLAIN ANALYZE. In MySQL, it’s EXPLAIN (and EXPLAIN ANALYZE on 8.0.18+).

EXPLAIN ANALYZE
SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
 AND o.created_at > now() - interval '7 days';

What you’re looking for: sequential scans on large tables, high row estimates versus actual rows, and nested loops that run thousands of times. A sequential scan on a 200-row table is fine. A sequential scan on a 20-million-row table that runs once per request is not.

Indexes: use them, but don’t overdo it

An index makes reads fast and writes slower. Every insert, update, and delete has to maintain it. So add indexes for the columns you filter and join on, and leave the rest alone.

The classic mistake is indexing each column separately when your query filters on both at once. A composite index on (status, created_at) will serve a query that filters on both columns, and it can also serve a query that filters on just status. The reverse isn’t true. Column order matters.

CREATE INDEX idx_orders_status_created
 ON orders (status, created_at DESC);

For partial indexes, you can shrink the index further by only covering the rows you actually query:

CREATE INDEX idx_orders_pending
 ON orders (created_at DESC)
 WHERE status = 'pending';

That index is a fraction of the size and gets updated only when pending orders change. If 95% of your orders are already shipped, this is a big win.

The N+1 problem still eats apps alive

This one survives every framework rewrite. You fetch a list of 50 orders, then loop over them and hit the database once per order to get the customer. That’s 51 queries. It works fine in dev with 5 rows and falls apart in production.

The fix depends on your stack. With an ORM, use eager loading. With raw SQL, use a join or a single IN query.

SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - interval '7 days';

If you’re working in a framework like Laravel or Rails, the tooling will tell you when you trip this. Turn on the query log in development and count the queries per request. If a page fires 80 queries, that’s your signal.

Only select what you need

SELECT * is convenient and expensive. It pulls columns you don’t use, blocks index-only scans, and breaks the moment someone adds a large text or JSON column to the table. Name your columns.

This matters most on wide tables. On a table with a 4KB JSON blob, selecting that column 10,000 times is 40MB of data crossing the wire for nothing.

Watch your pagination

OFFSET 100000 is a trap. The database still has to walk past 100,000 rows to find the ones you want, even with an index. For deep pagination, use keyset pagination instead:

-- Instead of: OFFSET 100000 LIMIT 20
SELECT id, created_at, total
FROM orders
WHERE created_at < $1 -- last seen timestamp
ORDER BY created_at DESC
LIMIT 20;

This stays fast no matter how deep you scroll. You keep a cursor (the last created_at and id from the previous page) and query from there. The tradeoff is you can’t jump to page 47, but most apps don’t need that anyway.

Connection pooling is not optional

Opening a new database connection per request is expensive. Each one costs a handshake, memory on the server, and a slot in the connection limit. Under load, you’ll exhaust the pool and start throwing errors.

Use a pooler in front of your database. PgBouncer for Postgres, ProxySQL for MySQL, or the built-in pooling that managed services provide. Set your pool size based on your database’s max_connections, not on how many app instances you run. If you have 10 app servers and each opens a pool of 20, that’s 200 connections before you’ve handled a single user.

Measure before and after

Every change you make should be measurable. Log slow queries. Set a threshold (say, 200ms) and actually look at what shows up. In Postgres, log_min_duration_statement = 200 gets you there. In MySQL, it’s slow_query_log with long_query_time = 0.2.

Optimization without measurement is just guessing. And guessing is how you end up with 14 indexes on a table that only ever gets queried two ways.

Where this fits in the bigger picture

Query performance is one layer of a web app, and it’s usually the first one to blame when a site feels slow. If you’re dealing with a legacy codebase that has these problems baked in, sometimes the cleanest path is a fresh build with the data layer designed correctly from the start. That’s the kind of work I do through custom web development for clients around Commack and the rest of Long Island. But whether you rewrite or refactor, the principles above are the same.

FAQ

How do I know if a query is slow enough to fix?

If it takes longer than your users notice, it’s worth looking at. In practice, anything over 100 to 200ms on a hot path deserves a look at the plan. Cold paths can be slower without hurting anyone.

Will adding an index always make things faster?

No. Indexes speed up reads and slow down writes. If a table gets far more inserts than selects, a new index can hurt overall performance. Check the query plan before and after, and drop indexes you don’t use.

Is an ORM slower than raw SQL?

Not inherently. A well-used ORM generates reasonable SQL. The problem is usually how it’s used: lazy loading inside loops, selecting every column, or missing indexes. Fix the access pattern before you blame the tool.