SQL Query Optimization: Performance Tips for Faster Queries

Why Query Performance Matters

A slow query does not just waste time — it cascades. One unoptimized query locks rows longer, consumes connection pool slots, and delays every other request hitting the same database. Under load, three poorly tuned queries can bring down an entire service.

The good news: most slow queries share the same small set of root causes. Learn to spot them, and you fix the majority of your performance problems.

Reading EXPLAIN Plans

Every database provides a way to see its execution plan before running the query. This is your primary diagnostic tool.

PostgreSQL

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01'
GROUP BY u.name;

Key things to look for:

• Seq Scan — the database reads the entire table. Acceptable for small tables, a problem for large ones.
• Index Scan — the database uses an index. This is what you want for large filtered queries.
• Nested Loop — fine for small result sets, expensive for large ones.
• Hash Join / Merge Join — better for joining large tables.

MySQL

EXPLAIN
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01'
GROUP BY u.name;

Check the type column: ALL means a full table scan. ref or range means index usage. Extra: Using filesort or Using temporary signals expensive operations.

SQL Server

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01'
GROUP BY u.name;

Focus on logical reads — the number of 8 KB pages read. Lower is better.

How Formatting Reveals Performance Issues

Well-formatted SQL makes performance problems visible. Consider this unformatted query:

select * from orders o join customers c on o.customer_id=c.id join order_items oi on o.id=oi.order_id join products p on oi.product_id=p.id where o.status='pending' and c.country='US' and p.category='Electronics';

Hard to read, harder to optimize. Now format it:

SELECT *
FROM orders o
JOIN customers c
    ON o.customer_id = c.id
JOIN order_items oi
    ON o.id = oi.order_id
JOIN products p
    ON oi.product_id = p.id
WHERE o.status = 'pending'
    AND c.country = 'US'
    AND p.category = 'Electronics';

Now the anti-patterns jump out:

• SELECT * — fetching every column from four tables
• Three JOINs with no index hints visible
• Multiple WHERE conditions that might each need an index

Formatting does not fix performance, but it reveals where to look.

Common Performance Anti-Patterns

SELECT *

Never use SELECT * in production queries. It pulls every column from every joined table, including ones you do not need.

-- Bad: fetches 20+ columns across 4 tables
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'shipped';

-- Good: fetches only what you need
SELECT
    o.id,
    o.order_date,
    c.name,
    c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'shipped';

SELECT * also breaks when the table schema changes — adding a column silently increases your result set size.

N+1 Queries

This is the most common ORM-generated performance problem. Instead of one query with a JOIN, the application runs one query to get a list, then one additional query per row.

# N+1 pattern — 1 + 1000 queries
users = db.query("SELECT id, name FROM users WHERE active = true")
for user in users:
    orders = db.query("SELECT COUNT(*) FROM orders WHERE user_id = ?", user.id)

Replace with a single aggregated query:

SELECT
    u.id,
    u.name,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o
    ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.name;

One query instead of a thousand. The difference is orders of magnitude.

Missing Indexes

Every column in a WHERE, JOIN, or ORDER BY clause is a candidate for an index. Without indexes, the database scans the entire table.

-- This query scans all 5 million rows without an index
SELECT id, name, email
FROM users
WHERE email = 'user@example.com';

-- Add an index
CREATE INDEX idx_users_email ON users(email);

Indexing rules:

• Index columns used in WHERE clauses with high selectivity
• Index foreign key columns — JOIN performance depends on them
• Composite indexes for multi-column filters — put the most selective column first
• Avoid over-indexing — each index slows writes (INSERT, UPDATE, DELETE)

Sargability Violations

A "sargable" predicate is one where the database can use an index. Wrapping a column in a function makes it non-sargable.

-- Non-sargable: function on indexed column
SELECT *
FROM orders
WHERE DATE(created_at) = '2024-01-15';

-- Sargable: range condition on raw column
SELECT *
FROM orders
WHERE created_at >= '2024-01-15'
    AND created_at < '2024-01-16';

Common non-sargable patterns to avoid:

Optimization Case Study

A reporting query took 45 seconds in production. Here is the original:

SELECT c.name, SUM(p.amount) as total
FROM customers c
JOIN payments p ON c.id = p.customer_id
WHERE LOWER(c.country) = 'us'
    AND YEAR(p.payment_date) = 2024
    AND p.status != 'failed'
GROUP BY c.name
HAVING SUM(p.amount) > 500
ORDER BY total DESC;

Problems identified:

1. LOWER(c.country) prevents index usage — country values are already consistent
2. YEAR(p.payment_date) prevents index usage — use a range instead
3. p.status != 'failed' has low selectivity — most payments succeed

Optimized version:

SELECT
    c.name,
    SUM(p.amount) AS total
FROM customers c
JOIN payments p
    ON c.id = p.customer_id
WHERE c.country = 'US'
    AND p.payment_date >= '2024-01-01'
    AND p.payment_date < '2025-01-01'
    AND p.status = 'completed'
GROUP BY c.name
HAVING SUM(p.amount) > 500
ORDER BY total DESC;

Added indexes:

CREATE INDEX idx_payments_date_status
    ON payments(payment_date, status, customer_id);

Result: 45 seconds down to 0.3 seconds — a 150x improvement.

Performance Checklist

• Replace SELECT * with explicit column lists
• Eliminate N+1 queries — use JOINs and aggregation instead
• Add indexes on filtered and joined columns
• Make WHERE predicates sargable — avoid functions on indexed columns
• Read the EXPLAIN plan before and after each optimization
• Test with production-like data volumes — small datasets hide slow queries

Key Takeaways

• EXPLAIN plans are your primary tool — learn to read them for your database
• Formatting makes performance anti-patterns visible at a glance
• SELECT * and N+1 queries are the two most common causes of slow SQL
• Wrapping indexed columns in functions kills index usage — always write sargable predicates
• One well-placed composite index can cut query time by 100x or more

Try It Yourself

Format and review your queries with our free SQL Formatter. Paste your SQL, check the structure, and spot potential performance issues before they hit production.

Related Guides

• SQL Formatting Guide: Writing Clean and Readable Queries — detailed formatting rules that make optimization easier
• SQL Best Practices: Industry Standards for Query Style — coding standards for consistent, reviewable SQL