SQL Query Optimization: 10 Techniques That Actually Work

SQL Query Optimization: Techniques That Actually Work

SQL performance problems follow predictable patterns. Before reaching for a caching layer or database upgrade, try these techniques — most slow queries can be fixed in minutes once you know what to look for.

1. Run EXPLAIN ANALYZE First

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

-- MySQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Look for: Seq Scan (full table scan on large tables), high "Rows Removed by Filter", actual vs estimated row mismatches.

2. Add Missing Indexes

-- Single column index
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite (most selective column first)
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- Partial index (only relevant rows)
CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';

Composite index column order matters: (status, created_at) helps queries filtering by status alone or both, but NOT by created_at alone.

3. Use Covering Indexes

Include all columns the query needs — the database never touches the main table rows:

-- Query: SELECT email, name FROM users WHERE active = true ORDER BY created_at
CREATE INDEX idx_users_active ON users(active, created_at) INCLUDE (email, name);

4. Avoid SELECT *

-- Slow: fetches all 20+ columns including large TEXT/JSON fields
SELECT * FROM articles WHERE author_id = 42;

-- Fast: only needed columns, can hit covering index
SELECT id, title, published_at FROM articles WHERE author_id = 42;

5. Fix the N+1 Problem

1 query to get N rows, then N queries for related data = N+1 round trips.

-- Instead of N+1: single JOIN
SELECT o.id, o.amount, c.name, c.email
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending';

// ORM eager loading (Sequelize)
const orders = await Order.findAll({
  where: { status: 'pending' },
  include: [{ model: Customer }],  // 1 query instead of N+1
});

6. Use EXISTS Instead of IN for Subqueries

-- Slower: IN scans entire subquery result
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);

-- Faster: EXISTS stops at first match
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.amount > 1000);

7. Avoid Functions on Indexed Columns

-- Slow: function prevents index use
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- Fast: range condition uses index
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

8. Use Keyset Pagination Instead of OFFSET

-- Slow: OFFSET 10000 scans and discards 10000 rows
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- Fast: keyset (cursor-based) pagination
SELECT * FROM orders WHERE id > 10020 ORDER BY id LIMIT 20;

9. Batch Inserts

-- Slow: 1000 separate INSERT statements (1000 round trips)
-- Fast: single batch INSERT
INSERT INTO logs (user_id, action) VALUES
  (1, 'login'), (2, 'purchase'), (3, 'view');
-- ... up to 1000-5000 rows per batch

10. Enable Slow Query Log

-- PostgreSQL: log queries > 100ms
SET log_min_duration_statement = 100;

-- MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;

Quick Diagnostics Checklist

1. Seq Scan on large table? → Add index on WHERE/JOIN columns
2. Multiple queries per request? → Fix N+1 with JOIN or eager loading
3. Using SELECT *? → Specify only needed columns
4. Function on WHERE column? → Rewrite as range condition
5. Large OFFSET? → Switch to keyset pagination
6. Bulk writes? → Use batch INSERT/UPDATE

→ Format and prettify SQL queries with the SQL Formatter.