SQL Performance Pitfalls: Why Your Queries Are Slow and How to Fix Them

SQL performance problems rarely announce themselves with a clear error message. More often, they creep in as a gradual slowdown: a page that used to load in 200ms now takes 2 seconds; a nightly batch job that finished in 10 minutes now runs for an hour. The root cause is almost never "the database is too slow"—it's almost always "the SQL is doing far more work than it needs to."

This article covers the most common SQL performance pitfalls, explains why they hurt, and shows how to fix them. We'll focus on practical, database-agnostic advice (with MySQL and PostgreSQL examples) and walk through a full end-to-end optimization. Try it in our SQL formatter to keep your queries readable while you optimize.

1. The Cardinal Sin: SELECT * in Production

SELECT * is convenient for ad-hoc queries, but in production code it's almost always harmful. It forces the database to read every column from disk, even when only a few are needed. This increases I/O, memory usage, network transfer, and often prevents index-only scans.

Bad:

SELECT * FROM orders WHERE user_id = 1001;

Good:

SELECT order_id, order_amount, create_time
FROM orders
WHERE user_id = 1001;

If the table has 50 columns but you only need 3, you're wasting 94% of the data transfer. Over thousands of queries, that adds up fast.

2. Missing or Misplaced Indexes

Indexes are the single most impactful optimization tool. The most common mistake is not having an index on columns used in WHERE, JOIN, or ORDER BY. But more indexes aren't always better—each index adds write overhead.

Composite Index Design

For a query like:

SELECT *
FROM tasks
WHERE region_code = '330101'
  AND biz_status = 'PENDING'
  AND risk_level = 'HIGH'
  AND insurance_type = 'PENSION'
  AND cert_deadline >= '2026-06-01'
  AND cert_deadline < '2026-06-08'
ORDER BY cert_deadline ASC, updated_at DESC
LIMIT 20;

A composite index that covers the filter and sort columns can eliminate table scans entirely:

CREATE INDEX idx_tasks_filter_sort
ON tasks (region_code, biz_status, risk_level, insurance_type, cert_deadline, updated_at DESC);

Place equality conditions first, then range conditions, then sort columns. If you need to include extra columns to avoid table lookups, use INCLUDE (PostgreSQL) or a covering index (MySQL).

3. The IN vs EXISTS Trap

IN is fine for small, static lists:

SELECT * FROM orders WHERE status IN ('PAID', 'SHIPPED', 'FINISHED');

But for subqueries that return many rows, EXISTS is often better because it can stop early after finding the first match:

-- Slow with large subquery
SELECT *
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);

-- Faster with EXISTS
SELECT c.*
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.amount > 1000
);

The NOT IN NULL Trap

NOT IN with a subquery can silently return zero rows if the subquery result contains any NULL:

-- May return no rows if orders.customer_id has NULLs
SELECT *
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Always prefer NOT EXISTS or LEFT JOIN ... IS NULL:

SELECT c.*
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);

-- Or:
SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;

4. Functions on Indexed Columns

Wrapping a column in a function usually makes the index unusable:

-- Index on create_time is useless here
SELECT * FROM orders WHERE DATE(create_time) = '2026-06-28';

-- Better: range query
SELECT * FROM orders
WHERE create_time >= '2026-06-28 00:00:00'
  AND create_time < '2026-06-29 00:00:00';

5. Implicit Type Conversion

When a column's type differs from the value's type, the database may convert the column, breaking index usage:

-- phone is VARCHAR, but value is integer
SELECT * FROM users WHERE phone = 13800001111;  -- index ignored

-- Correct: match the type
SELECT * FROM users WHERE phone = '13800001111';  -- uses index

6. Excessive JOINs and SELECT *

JOINing many tables isn't inherently slow—the real cost comes from processing too many rows. Always filter as early as possible using CTEs or subqueries to reduce the intermediate result set.

-- Instead of joining all tables then filtering:
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2026-01-01';

-- Filter first, then join:
WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date >= '2026-01-01'
)
SELECT r.*, c.name
FROM recent_orders r
JOIN customers c ON r.customer_id = c.id;

7. Deep Pagination with OFFSET

OFFSET + LIMIT becomes slower as the offset grows because the database still has to scan and discard all skipped rows. Use keyset pagination ("seek method") instead:

-- Slow for large offsets
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

-- Fast: keyset pagination
SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

Worked Example: Optimizing a Slow Query

Let's walk through a real-world optimization. We have a task table with 1.2 million rows, and we need to find the top 20 high-risk pension tasks for a region.

Original query:

SELECT *
FROM benefit_cert_task
WHERE region_code = '330101'
  AND biz_status = 'PENDING'
  AND risk_level = 'HIGH'
  AND insurance_type = 'PENSION'
  AND cert_deadline >= '2026-06-01'
  AND cert_deadline < '2026-06-08'
ORDER BY cert_deadline ASC, updated_at DESC
LIMIT 20;

Step 1: Check the execution plan

EXPLAIN ANALYZE ...

The plan shows a Parallel Seq Scan on benefit_cert_task, filtering 400,000 rows, with an execution time of 93 ms.

Step 2: Identify missing index The table only has a primary key on task_id. No index covers the filter or sort columns.

Step 3: Create a composite index

CREATE INDEX idx_benefit_cert_task_filter_sort
ON benefit_cert_task (region_code, biz_status, risk_level, insurance_type, cert_deadline, updated_at DESC)
INCLUDE (task_id, person_id, agency_id);

Step 4: Verify the improvement

EXPLAIN ANALYZE ...

Now the plan uses an Index Scan with execution time dropping to 8 ms—an 11x improvement. I/O also drops significantly.

Common Pitfalls

• Over-indexing: Every index slows down writes. Only add indexes that are actually used by queries.
• Ignoring statistics: The optimizer relies on table statistics. Run ANALYZE after bulk loads or significant data changes.
• Using OR on different indexed columns: OR can defeat index usage. Prefer UNION ALL when the branches are disjoint.
• Assuming LEFT JOIN is always needed: If you only need rows from the left table that have a match, use INNER JOIN or EXISTS.
• Not monitoring slow queries: Enable the slow query log (slow_query_log=ON, long_query_time=0.5) and review it regularly.

FAQ

Why is my query slow even though it uses an index?

The index might not be selective enough—if it returns a large percentage of the table, the optimizer may prefer a full scan. Also, if the index doesn't cover all columns in the query, the database has to do extra lookups ("bookmark lookup" in MySQL, "bitmap heap scan" in PostgreSQL).

Should I always use NOT EXISTS instead of NOT IN?

For subqueries, yes—NOT EXISTS avoids the NULL trap and can be more efficient. For static lists with no NULLs, NOT IN is fine.

How many indexes is too many?

There's no hard number, but a good rule of thumb: if an index is never used by any query, drop it. Monitor index usage with pg_stat_user_indexes (PostgreSQL) or sys.schema_unused_indexes (MySQL).

Does COUNT(*) always do a full table scan?

In MySQL with InnoDB, yes—unless you have a secondary index that is smaller than the primary key. In PostgreSQL, a COUNT(*) on a table with a primary key will use an index-only scan if the visibility map is up to date.

What's the fastest way to paginate?

Keyset pagination (using WHERE id > ? ORDER BY id LIMIT ?) is O(1) per page, while OFFSET is O(n). Use keyset pagination for large datasets.

Conclusion

SQL optimization isn't magic—it's about understanding what the database does with your query and giving it the right tools (indexes, statistics, clean syntax) to do it efficiently. Start by enabling the slow query log, examine execution plans, and fix the most egregious patterns first. A single well-designed composite index can turn a 100ms query into a 5ms one.