How do you optimize slow SQL queries?

Question

Accepted Answer

Optimizing slow queries starts with **analyzing the execution plan** (`EXPLAIN`) to find the bottleneck, then applying targeted fixes — most commonly **adding indexes**, rewriting the query, and avoiding common anti-patterns. The biggest win is usually turning full table scans into index lookups.

## Step 1: analyze with EXPLAIN

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- shows the EXECUTION PLAN: scan type (Seq Scan = full scan, Index Scan = using an index),
-- estimated/actual rows, cost, and timing → reveals WHERE the time goes
```

`EXPLAIN` (or `EXPLAIN ANALYZE`) shows how the database executes the query — crucially, whether it's doing a **full table scan** (slow) or using an **index** (fast). Always profile before optimizing.

## The biggest win: add indexes

```sql
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
```

The most common and impactful fix: **add indexes** on columns used in `WHERE`, `JOIN`, and `ORDER BY`. This turns full table scans into fast lookups.

## Common anti-patterns to fix

```sql
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = 'X@Y.COM'        -- can't use an index on email
WHERE email = 'x@y.com'               -- ✅ can use the index

-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (prefix) can use an index

-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns  -- less data, may use a covering index

-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
```

## Other optimization techniques

```text
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
```

## Why it matters

Query optimization is critical knowledge for database performance — slow queries are a leading cause of poor application performance, and understanding how to find and fix them is essential senior-level skill for anyone working with databases at scale.

The foundational discipline is **analyzing with `EXPLAIN`** (examining the execution plan to find the actual bottleneck — crucially whether the query does a slow full table scan or a fast index lookup) before optimizing, rather than guessing — this measurement-driven approach is what makes optimization effective.

The **single biggest and most common win is adding indexes** on filtered, joined, and sorted columns (turning full table scans into fast lookups — often improving query time by orders of magnitude), making index knowledge central to optimization.

Understanding **common anti-patterns** is equally important: functions on columns and leading-wildcard `LIKE` prevent index use, `SELECT *` fetches unnecessary data, and especially **N+1 queries** (querying in a loop instead of a single JOIN — a frequent, severe performance killer in application code) — recognizing and fixing these addresses the most common causes of slow queries.

Other techniques (composite indexes, reducing data scanned, rewriting subqueries as JOINs, keeping statistics updated, materialized views for expensive queries) round out the toolkit.

Since query performance directly affects application responsiveness and scalability, and since the systematic approach (analyze with EXPLAIN, then fix — usually by adding indexes and eliminating anti-patterns like N+1) is what keeps databases fast under load, mastering query optimization is important, high-value senior knowledge that distinguishes developers who can build performant, scalable database-backed applications — a frequently-relevant, critical skill and common interview topic for database performance.