SQL Query Optimization: Techniques Every Developer Should Know

Table of Contents

• Use EXPLAIN to See What's Happening
• Indexes: The Most Impactful Tool
• Avoid SELECT *
• The N+1 Query Problem
• Efficient Pagination
• Common Anti-Patterns

Use EXPLAIN to See What's Happening

Before optimizing, understand what the database is doing.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;

Watch for: Seq Scan (full table scan — bad on large tables), Index Scan (good), and the actual time values.

Indexes: The Most Impactful Tool

An index lets the database find rows without scanning the entire table — like a book's index vs reading every page.

-- Without index: full table scan on 10M rows = slow
SELECT * FROM orders WHERE user_id = 123;

-- Create index
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Now the same query uses an index scan = fast

-- Composite index: column order matters
-- Best for: WHERE status = 'paid' AND created_at > '2025-01-01'
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

When NOT to index: High-write tables (every write updates indexes), small tables (full scan is often faster), low-cardinality columns like boolean flags.

Avoid SELECT *

-- Bad: fetches all columns, including large TEXT/JSON blobs
SELECT * FROM posts;

-- Good: only what you need
SELECT id, title, created_at FROM posts;

This reduces I/O, network transfer, and prevents accidentally loading large columns.

The N+1 Query Problem

The most common ORM pitfall: fetching a list, then querying for related data one row at a time.

// N+1: 1 query for users + N queries for their posts
const users = await db.query('SELECT * FROM users LIMIT 50');
for (const user of users) {
  user.posts = await db.query('SELECT * FROM posts WHERE user_id = $1', [user.id]);
  // This fires 50 separate queries!
}

// Fix: use a JOIN or IN clause
const rows = await db.query(`
  SELECT u.id, u.name, p.title
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  LIMIT 50
`);

Efficient Pagination

-- Bad: OFFSET gets slower as the page number increases
-- Page 1000 requires scanning 20,000 rows to skip
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 20000;

-- Good: cursor-based pagination stays O(1)
-- Client sends the last seen id/timestamp
SELECT * FROM posts
WHERE created_at < '2025-01-01T10:00:00Z'
ORDER BY created_at DESC
LIMIT 20;

Common Anti-Patterns

-- Avoid functions on indexed columns in WHERE (defeats the index)
WHERE LOWER(email) = 'user@example.com'  -- bad
WHERE email = 'user@example.com'          -- good (store lowercase in DB)

-- Avoid implicit type conversion
WHERE user_id = '123'   -- bad if user_id is INTEGER
WHERE user_id = 123     -- good

-- Use EXISTS instead of COUNT when checking existence
SELECT COUNT(*) FROM likes WHERE post_id = 1   -- scans all rows
SELECT EXISTS(SELECT 1 FROM likes WHERE post_id = 1)  -- stops at first hit