SQL Query Optimization: A Practical Guide

Diagnosing Slow Queries

Before you can optimize a query, you need to know which queries are slow and why. Every major database has tools for this.

Finding Slow Queries

PostgreSQL: Enable the pg_stat_statements extension. It tracks every query, its total execution time, number of calls, and average time. Sort by total_exec_time to find your biggest offenders.

SELECT query,
       calls,
       total_exec_time / 1000 AS total_seconds,
       mean_exec_time AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

MySQL: Enable the slow query log with slow_query_log = 1 and long_query_time = 1 (in seconds) in your configuration. Every query that exceeds the threshold gets logged. Use mysqldumpslow to summarize the log.

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;  -- Log queries slower than 1 second
SET GLOBAL log_queries_not_using_indexes = 1;  -- Also log full table scans

Once you know which queries are slow, the next step is always the same: run EXPLAIN.

Reading EXPLAIN Plans

EXPLAIN shows you the database's execution plan — how it reads data, which indexes it uses, and the estimated cost of each operation. It is the single most important tool for SQL optimization.

PostgreSQL: EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

The output shows a tree of operations. Here is what to look for:

What You See	What It Means	Action
Seq Scan on large table	Full table scan (reading every row)	Add an index on the filtered/joined column
Index Scan or Index Only Scan	Using an index (good)	No action needed — Index Only Scan is the best case
Nested Loop with high row count	For each row in table A, scan table B	Check for missing index on the inner table
Hash Join	Build hash table from smaller table, probe with larger	Usually efficient for large joins
Sort with high cost	Sorting in memory or on disk	Add an index that matches the ORDER BY
Rows (estimated) far from Rows (actual)	Stale statistics	Run ANALYZE tablename;

MySQL: EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON
SELECT * FROM orders
WHERE customer_id = 42
  AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 10;

In MySQL, look for "type": "ALL" (full table scan), "type": "ref" or "type": "range" (index used), and "using_filesort": true (sorting without an index). Format your SQL for readability before analyzing it — the SQL Formatter handles indentation, keyword casing, and alignment for complex queries.

Indexing Strategies

An index is a separate data structure (usually a B-tree) that lets the database find rows without scanning the entire table. Think of it as the index at the back of a textbook — instead of reading every page, you look up the term and jump to the right page.

When to Add an Index

• WHERE clauses — columns filtered with =, <, >, BETWEEN, IN, LIKE 'prefix%'
• JOIN conditions — the foreign key column on the "many" side
• ORDER BY — to avoid expensive sorts
• GROUP BY — to speed up aggregation

Composite Indexes

A composite (multi-column) index is often more effective than multiple single-column indexes. Column order matters: the index can be used for queries that filter on the leftmost columns.

-- Query pattern: filter by status, then sort by created_at
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

-- Index that covers both the filter and the sort
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

This single index lets the database filter by status = 'pending' and return results already sorted by created_at DESC, eliminating both the table scan and the sort operation.

Covering Indexes

A covering index includes all the columns a query needs, so the database can answer the query entirely from the index without touching the table. In PostgreSQL, this shows as "Index Only Scan" in the EXPLAIN output.

-- Query: only needs id and email
SELECT id, email FROM users WHERE status = 'active';

-- Covering index: includes all columns the query reads
CREATE INDEX idx_users_status_covering
ON users (status) INCLUDE (id, email);

Optimizing JOINs

JOINs are the most expensive operation in most queries. Here are the key optimization strategies.

1. Index Both Sides of the JOIN

-- Ensure indexes exist on both sides
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
-- customers.id is already indexed (primary key)

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'US';

2. Filter Before Joining

Reduce the number of rows before the JOIN executes. The optimizer often does this automatically, but explicit subqueries or CTEs can help with complex queries.

-- Instead of joining all orders then filtering
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > '2025-01-01'
  AND c.country = 'US';

-- With CTEs for clarity (same performance in most cases)
WITH recent_orders AS (
  SELECT customer_id, total
  FROM orders
  WHERE created_at > '2025-01-01'
),
us_customers AS (
  SELECT id, name
  FROM customers
  WHERE country = 'US'
)
SELECT uc.name, ro.total
FROM us_customers uc
JOIN recent_orders ro ON ro.customer_id = uc.id;

3. Use EXISTS Instead of JOIN for Existence Checks

-- Slower: JOIN fetches all matching rows, then deduplicates
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id;

-- Faster: EXISTS stops after finding the first match
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

The N+1 Query Problem

The N+1 problem is the most common performance issue in web applications that use ORMs. It happens when code fetches a list of N items, then runs a separate query for each item's related data.

// 1 query: fetch all orders
orders = db.query("SELECT * FROM orders LIMIT 100")

// 100 queries: fetch customer for each order (BAD)
for order in orders:
    customer = db.query("SELECT * FROM customers WHERE id = ?", order.customer_id)
    print(order.id, customer.name)

// Total: 101 queries for 100 orders

Fix 1: Use a JOIN

SELECT o.id, o.total, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
LIMIT 100;
-- 1 query instead of 101

Fix 2: Batch with IN

-- Query 1: fetch orders
SELECT * FROM orders LIMIT 100;

-- Query 2: fetch all related customers at once
SELECT * FROM customers WHERE id IN (1, 2, 3, ... 100);
-- 2 queries instead of 101

Fix 3: ORM Eager Loading

// Prisma (JavaScript/TypeScript)
const orders = await prisma.order.findMany({
  include: { customer: true },
  take: 100,
});

// SQLAlchemy (Python)
orders = session.query(Order).options(joinedload(Order.customer)).limit(100).all()

// ActiveRecord (Ruby)
orders = Order.includes(:customer).limit(100)

If you are converting query results between formats, the JSON to CSV Converter can help when exporting database data for analysis in spreadsheets.

SELECT Only What You Need

SELECT * is the lazy option that costs real performance. It fetches every column, including large TEXT/BLOB fields you might not need, wastes network bandwidth between the database and your application, and prevents the use of covering indexes.

-- Bad: fetches all 30 columns including the large bio TEXT field
SELECT * FROM users WHERE status = 'active';

-- Good: fetches only the 3 columns you actually display
SELECT id, name, email FROM users WHERE status = 'active';

This matters most for queries that return many rows (lists, feeds, exports) and tables with wide schemas or large column values.

Efficient Pagination

OFFSET Pagination (Simple but Slow)

-- Page 1
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- Page 500 (slow: database scans and discards 9,980 rows)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 9980;

The problem: the database must read and discard all rows before the offset. At OFFSET 100,000, it reads 100,000 rows just to throw them away.

Cursor Pagination (Fast at Any Depth)

-- First page
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: use the last item's values as cursor
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-02-10 14:30:00', 4582)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The WHERE clause jumps directly to the right position using the index. Whether you are on page 2 or page 5,000, the query is equally fast. Use a composite index on (created_at DESC, id DESC) to support this pattern.

Subqueries vs JOINs vs CTEs

Three ways to combine data from multiple tables. Here is when to use each.

Approach	Best For	Watch Out
JOIN	Combining rows from related tables	Can produce duplicates with one-to-many relationships
Correlated Subquery	EXISTS checks, scalar aggregates per row	Runs once per outer row — can be slow on large result sets
CTE (WITH)	Breaking complex queries into readable steps	In MySQL <8.0, CTEs are materialized (no optimization through them)
Derived Table	Pre-aggregating data before a JOIN	Optimizer can usually push predicates into derived tables

WITH monthly_totals AS (
  SELECT customer_id,
         DATE_TRUNC('month', created_at) AS month,
         SUM(total) AS monthly_spend
  FROM orders
  WHERE created_at >= '2025-01-01'
  GROUP BY customer_id, DATE_TRUNC('month', created_at)
),
high_spenders AS (
  SELECT customer_id
  FROM monthly_totals
  WHERE monthly_spend > 1000
  GROUP BY customer_id
  HAVING COUNT(*) >= 3  -- At least 3 months over $1000
)
SELECT c.name, c.email
FROM customers c
JOIN high_spenders hs ON hs.customer_id = c.id;

When migrating data between SQL databases and NoSQL systems, the SQL to MongoDB Query Converter translates your SQL queries into MongoDB's query syntax.

When to Cache

Caching is not a substitute for query optimization. A cached slow query is still a slow query — it just fails less often. Optimize the query first, then add caching for frequently-accessed data that changes infrequently.

Good Candidates for Caching

• Reference data — country lists, categories, config values (change rarely, read constantly)
• Aggregations — dashboard metrics, leaderboards, report summaries (expensive to compute, tolerate slight staleness)
• User sessions — auth tokens, permissions (read on every request)

Bad Candidates for Caching

• User-specific data that changes frequently — inbox counts, real-time notifications (cache invalidation is harder than the original query)
• Search results — too many unique queries to cache effectively
• Anything that must be 100% consistent — financial balances, inventory counts

function getTopProducts() {
  // 1. Check cache
  cached = redis.get("top_products")
  if (cached) return JSON.parse(cached)

  // 2. Cache miss: run query
  result = db.query(`
    SELECT p.name, SUM(oi.quantity) AS total_sold
    FROM products p
    JOIN order_items oi ON oi.product_id = p.id
    WHERE oi.created_at > NOW() - INTERVAL '7 days'
    GROUP BY p.name
    ORDER BY total_sold DESC
    LIMIT 10
  `)

  // 3. Store in cache with TTL
  redis.setex("top_products", 300, JSON.stringify(result))  // 5 min TTL
  return result
}

To inspect and debug the JSON payloads returned by your cache, the JSON Viewer renders them in an expandable tree with syntax highlighting.

Optimization Checklist

Run through this checklist whenever you encounter a slow query.

1. Run EXPLAIN ANALYZE — understand the execution plan before changing anything
2. Check for Seq Scans — add indexes on filtered, joined, and sorted columns
3. Check for N+1 patterns — use JOINs or batch queries instead of loops
4. Select only needed columns — replace SELECT * with explicit column lists
5. Check statistics freshness — run ANALYZE on tables with outdated stats
6. Review JOIN order and conditions — ensure indexes exist on both sides
7. Use cursor pagination — replace OFFSET with WHERE-based cursors for large datasets
8. Consider covering indexes — INCLUDE columns to avoid table lookups
9. Evaluate caching — only after the query itself is optimized
10. Test with production-size data — queries that are fast on 100 rows may be slow on 10 million

SQL Developer Tools

---