Ecommet
Start a project
Topic 34 of 75

SQL Tuning

Most slow databases are slow because of a handful of queries. Learn to find them, read query plans, and fix the usual suspects: missing indexes, N+1 queries, SELECT *, deep OFFSET pagination and too many connections.

Intermediate 5 min read 4 quiz questions

Jump to the quiz

In a nutshell

  • Measure first: find the queries that take the most total time, then read their plans with EXPLAIN ANALYZE.
  • Fix N+1 queries by loading related rows in one query (IN (…) or a join).
  • Select only the columns you need, and use keyset pagination instead of deep OFFSET.
  • Use a connection pool: opening a connection per request is slow and can exhaust the server.

Find the slow queries

Tune by evidence. Turn on the slow query log (MySQL) or pg_stat_statements (PostgreSQL) and sort by total time (calls × average time): a 5 ms query run a million times an hour matters more than a 2-second report run once a day.

Read the real plan, with timings

SQL
EXPLAIN ANALYZE
SELECT id, total FROM orders
WHERE customer_id = 7 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
-- Look for: sequential scans on big tables, a sort of many rows,
-- estimated rows far from actual rows (stale statistics).

The N+1 problem

An ORM loads 50 orders, then lazily loads each order's customer: 1 + 50 queries. Each is fast, but 51 network round trips add up to a slow page, and under load they hammer the database.

N+1 vs one batched query

N+1 vs one batched query App → Database: SELECT 50 orders App → Database: customer of order 1 App → Database: customer of order 2 App → App: … 48 more queries App → Database: fixed: customers WHERE id IN (…) AppDatabaseSELECT 50 orderscustomer of order 1customer of order 2… 48 more queriesfixed: customers WHERE id IN (…)

All steps Same data, 51 round trips vs 2.

Diagram as text
  1. One query loads the 50 orders.
  2. Then the code touches order 1's customer, triggering another query.
  3. And order 2's, and so on.
  4. 48 more round trips follow: 51 queries in total.
  5. The fix: collect the customer IDs and load them all in one query (or join).

Two queries instead of fifty-one

SQL
SELECT id, customer_id, total FROM orders WHERE status = 'paid' LIMIT 50;
SELECT id, name FROM customers WHERE id IN (7, 12, 31, 44);
-- Or one query with a JOIN. ORMs call this eager loading.

Pagination without OFFSET

OFFSET 100000 LIMIT 20 makes the database produce and throw away 100,000 rows to return 20, so page 5,000 is far slower than page 1. Keyset pagination remembers where the last page ended and seeks straight there with an index.

OFFSET vs keyset (seek) pagination

SQL
-- Slow on deep pages
SELECT id, title FROM posts ORDER BY id DESC LIMIT 20 OFFSET 100000;

-- Fast on every page: continue after the last id the client saw
SELECT id, title FROM posts WHERE id < 48213 ORDER BY id DESC LIMIT 20;

More quick wins

  • Select only needed columns: SELECT * drags wide text columns over the network and prevents covering indexes.
  • Batch writes: one INSERT with 500 rows beats 500 single inserts.
  • Connection pooling: reuse a small pool of connections (PgBouncer, HikariCP); each database connection costs memory on the server.
  • Right types: integers for IDs, proper date types, and the same type on both sides of a join so indexes can be used.
  • Keep statistics fresh: the planner chooses plans from table statistics (ANALYZE).
  • Archive old data: a table that only grows gets slower to scan, index and back up.
  • Cache repeated, rarely changing results, and move reporting to a replica.

Common mistakes

  • Optimising queries by guesswork instead of plans and measurements.
  • Lazy-loading relations in a loop (N+1).
  • Deep OFFSET pagination on large tables.
  • Opening a new database connection on every request.

Interview questions

Try answering out loud before you open each one.

A page makes hundreds of database queries. What is likely wrong?

An N+1 pattern: a list is loaded, then each item lazily loads related data. Fix it with eager loading (a JOIN or a second query with WHERE id IN (…)), and add a test or query counter so it does not come back.

How do you paginate a feed with millions of rows?

Keyset pagination: order by an indexed, unique key (or created_at plus id), and ask for rows after the last seen key. Each page is an index seek, so performance does not degrade with depth. Return an opaque cursor to clients.

Check yourself

1. Which query deserves attention first?
Show the answer

Answer: 5 ms, run a million times an hour. Total time is 5,000 s per hour: by far the biggest load.

2. A list page runs 1 query for 50 rows plus 50 more for their authors. Name the problem.
Show the answer

Answer: N+1 queries. Load the authors in one batched query instead.

3. Why is OFFSET 100000 slow?
Show the answer

Answer: The database must produce and skip 100,000 rows. Keyset pagination seeks directly to the start of the page.

4. What does a connection pool do?
Show the answer

Answer: Reuses a small set of open connections. Opening connections is slow and each one costs server memory.

Further reading

Your notes

Saved in this browser as you type.

Finished this topic?

Mark it read to track your progress, then carry on with the next one.

Next topic