Why is my SQL query slow? Seven things to check first

1. Read the plan before guessing

Run EXPLAIN (PostgreSQL/MySQL) or check the execution plan in SQL Server. You are looking for a sequential/full table scan on a big table, and for a row estimate that is wildly different from the rows actually returned. PostgreSQL's EXPLAIN ANALYZE shows both.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

2. Index the columns you filter and join on

Any column that regularly appears in WHERE, JOIN ... ON, or ORDER BY is a candidate. A composite index helps when its leading columns match your filter, so put the equality column first and the range column second.

CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at);

3. Don't wrap the filtered column in a function

YEAR(created_at) = 2026 or LOWER(email) = 'a@b.com' forces the database to compute the function for every row, so the index goes unused. Rewrite as a range, or create an index on the expression.

-- slow
WHERE YEAR(created_at) = 2026

-- index-friendly
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01'

4. Stop selecting columns you don't use

SELECT * drags large text and blob columns across the network and prevents index-only scans. Listing the four columns you actually render is often the single easiest win.

5. Leading wildcards and OR conditions

LIKE '%term%' cannot use a normal index — use a full-text index for real search. A long OR chain across different columns often plans badly; splitting it into two queries joined by UNION ALL can let each half use its own index.

6. Aggregate before you join

Joining a big child table and then grouping makes the database carry millions of duplicated rows through the join. Summarize the child table in a CTE first, then join one row per parent.

WITH order_totals AS (
  SELECT customer_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, t.total_spent
FROM customers c
JOIN order_totals t ON t.customer_id = c.id;

7. Check it isn't the application, not the query

One query per row in a loop (the N+1 pattern) looks fast in the database and slow to the user. Fetch the whole set in one query with an IN list or a join. Also check pagination: OFFSET 100000 still walks those 100,000 rows — page by a key value instead (WHERE id > last_seen_id).