General Best Practices

SQL Query Optimization

Query optimization is fundamental to database performance. A well-optimized query can execute thousands of times faster than an inefficient one. This comprehensive guide covers practical techniques, real-world examples, and common pitfalls to avoid.

Tip

Use the EXPLAIN command to analyze query execution plans before optimizing. Understanding how your database executes queries is the first step to optimization.

Understanding Query Execution Plans

Using EXPLAIN

The EXPLAIN command shows how your database will execute a query. This is your most powerful diagnostic tool.

Basic EXPLAIN Usage:

sql

EXPLAIN SELECT * FROM users WHERE user_id = 42;

PostgreSQL EXPLAIN with Analysis:

sql

EXPLAIN ANALYZE SELECT * FROM orders
WHERE created_at > '2024-01-01'
ORDER BY total DESC;

Interpreting Output:

Look for these performance indicators:

Seq Scan: Full table scan (slow for large tables)
Index Scan: Using an index (usually fast)
Filter: Rows being eliminated during scan
Sort: Sorting operation (can be expensive)
Hash Join: Hash-based join (efficient)
Nested Loop: Loop-based join (slower with large datasets)

Warning

A Seq Scan on a large table without a WHERE clause is almost always a problem. Add appropriate indexes or improve query selectivity.

Indexing Strategies

Creating Effective Indexes

Single Column Index (Most Common):

sql

CREATE INDEX idx_users_email ON users(email);

Composite Index (Multiple Columns):

sql

CREATE INDEX idx_orders_customer_date
ON orders(customer_id, created_at DESC);

Unique Index:

sql

CREATE UNIQUE INDEX idx_users_username
ON users(username);

Partial Index (Index subset of data):

sql

CREATE INDEX idx_active_orders
ON orders(customer_id)
WHERE status = 'active';

Tip

Composite indexes should order columns: equality conditions first, then range conditions, then sort conditions. This maximizes index effectiveness.

Index Column Order Matters

sql

-- GOOD: customer_id (equality), created_at (range)
CREATE INDEX idx_orders_cust_date
ON orders(customer_id, created_at);

-- Will use index for queries like:
SELECT * FROM orders
WHERE customer_id = 123
AND created_at > '2024-01-01';

-- SLOW: Wrong order wastes index potential
CREATE INDEX idx_orders_date_cust
ON orders(created_at, customer_id);

Query Pattern Optimization

Pattern 1: Avoid SELECT *

Inefficient:

sql

SELECT * FROM users
WHERE status = 'active'
LIMIT 10;

Optimized:

sql

SELECT user_id, email, name, status
FROM users
WHERE status = 'active'
LIMIT 10;

The optimized version reduces data transfer and allows index-only scans on the selected columns.

Pattern 2: Use WHERE Before HAVING

Inefficient:

sql

SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Optimized:

sql

SELECT customer_id, COUNT(*) as order_count
FROM orders
WHERE created_at > '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) > 5;

Filter rows before grouping to reduce the dataset processed by the aggregation.

Pattern 3: Use IN for Multiple Values

Inefficient:

sql

SELECT * FROM users
WHERE status = 'active'
OR status = 'pending'
OR status = 'review';

Optimized:

sql

SELECT * FROM users
WHERE status IN ('active', 'pending', 'review');

The IN operator is more efficient and often uses better execution plans.

Pattern 4: BETWEEN for Range Queries

Inefficient:

sql

SELECT * FROM transactions
WHERE amount >= 100
AND amount <= 500;

Optimized:

sql

SELECT * FROM transactions
WHERE amount BETWEEN 100 AND 500;

BETWEEN often generates better index utilization for range queries.

Pattern 5: Use UNION Instead of OR for Complex Conditions

Potentially Inefficient:

sql

SELECT * FROM orders
WHERE customer_id = 123
OR product_id = 456
OR status = 'high-priority';

Potentially Faster:

sql

SELECT * FROM orders WHERE customer_id = 123
UNION
SELECT * FROM orders WHERE product_id = 456
UNION
SELECT * FROM orders WHERE status = 'high-priority';

UNION allows each part to use different indexes. Use UNION ALL if duplicates are acceptable (faster).

Join Optimization

Pattern 6: Join with Indexed Foreign Keys

Inefficient (No Index):

sql

SELECT o.order_id, c.customer_name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at > '2024-01-01';

Optimized (With Index):

sql

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_customers_id ON customers(customer_id);

SELECT o.order_id, c.customer_name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at > '2024-01-01';

Always index foreign key columns used in joins.

Pattern 7: Join Order Matters

Inefficient Order:

sql

SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE c.status = 'vip';

Optimized Order:

sql

SELECT o.order_id, c.customer_name, p.product_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN products p ON o.product_id = p.product_id
WHERE c.status = 'vip';

Start with the most filtered table first. This reduces the number of rows in subsequent joins.

Pattern 8: LEFT JOIN with NULL Filter

Inefficient (Still uses LEFT JOIN):

sql

SELECT u.user_id, u.email, l.login_count
FROM users u
LEFT JOIN login_logs l ON u.user_id = l.user_id
WHERE l.login_id IS NOT NULL;

Optimized (Switch to INNER JOIN):

sql

SELECT u.user_id, u.email, l.login_count
FROM users u
INNER JOIN login_logs l ON u.user_id = l.user_id;

If you're filtering out NULL values, use INNER JOIN instead.

Aggregation Optimization

Pattern 9: Aggregate with GROUP BY Efficiently

Inefficient:

sql

SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id;

Optimized (Add Index):

sql

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id;

Pattern 10: Subquery Optimization with Common Table Expressions

Inefficient (Correlated Subquery):

sql

SELECT u.user_id, u.email,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = u.user_id) as order_count
FROM users u
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = u.user_id) > 3;

Optimized (CTE):

sql

WITH user_orders AS (
  SELECT customer_id, COUNT(*) as order_count
  FROM orders
  GROUP BY customer_id
)
SELECT u.user_id, u.email, uo.order_count
FROM users u
JOIN user_orders uo ON u.user_id = uo.customer_id
WHERE uo.order_count > 3;

CTEs make the query clearer and prevent repetitive subquery execution.

Avoiding Common Performance Mistakes

Mistake 1: Functions in WHERE Clauses

Slow (Cannot use index):

sql

SELECT * FROM users
WHERE UPPER(email) = 'USER@EXAMPLE.COM';

Fast (Can use index):

sql

SELECT * FROM users
WHERE email = 'user@example.com';

Functions on indexed columns prevent index usage. Process data application-side when possible.

Mistake 2: Implicit Type Conversion

Slow (String compared to number):

sql

SELECT * FROM users
WHERE user_id = '123';

Fast (Proper type matching):

sql

SELECT * FROM users
WHERE user_id = 123;

Type mismatches force conversions that bypass indexes.

Mistake 3: LIKE with Leading Wildcard

Very Slow (No index use):

sql

SELECT * FROM products
WHERE product_name LIKE '%laptop%';

Faster (Prefix search):

sql

SELECT * FROM products
WHERE product_name LIKE 'laptop%';

Fastest (Exact/Index search):

sql

SELECT * FROM products
WHERE product_name = 'laptop';

Leading wildcards prevent index usage. Consider full-text search for text matching.

Mistake 4: NOT IN with NULL Values

Problematic:

sql

SELECT * FROM orders
WHERE customer_id NOT IN (
  SELECT customer_id FROM vip_customers WHERE vip_customers.status IS NULL
);

Fixed:

sql

SELECT * FROM orders
WHERE customer_id NOT IN (
  SELECT customer_id FROM vip_customers WHERE status IS NOT NULL
);

NOT IN returns NULL if any subquery value is NULL, causing the entire condition to be NULL.

Mistake 5: Unnecessary DISTINCT

Inefficient (Extra processing):

sql

SELECT DISTINCT customer_id
FROM orders
WHERE status = 'completed';

Optimized (If duplicates aren't possible):

sql

SELECT customer_id
FROM orders
WHERE status = 'completed';

Only use DISTINCT when necessary. It requires sorting or hashing.

Advanced Optimization Techniques

Performance Testing Workflow

Establish Baseline

Run the current query and note execution time and resource usage.

sql

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

Identify Bottleneck

Look at the EXPLAIN output for Seq Scan, high costs, or poor index usage.

Create Hypothesis

"Adding an index on status column will improve performance"

Implement Change

sql

CREATE INDEX idx_orders_status ON orders(status);

Test Impact

sql

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

Compare to baseline. Measure execution time, plan cost, and rows examined.

Document Results

Keep records of what worked and what didn't for future reference.

Optimization Checklist

Performance Optimization

Overall database performance techniques

PostgreSQL Best Practices

PostgreSQL-specific optimization tips

MySQL Best Practices

MySQL-specific optimization tips

Writing Queries

Learn query writing in WhoDB

Summary

SQL query optimization combines art and science. Use EXPLAIN to understand execution plans, create strategic indexes, avoid common pitfalls, and test changes methodically. Even small optimizations compound when queries run thousands of times daily. Start with the highest-impact changes and work systematically through the optimization checklist.

Check

You now have a comprehensive understanding of SQL query optimization techniques and patterns. Apply these principles to transform slow queries into fast, efficient ones.

Data Management Best Practices

AI Chat Assistant Best Practices

SQL Query Optimization

Understanding Query Execution Plans

Using EXPLAIN

Indexing Strategies

Creating Effective Indexes

Index Column Order Matters

Query Pattern Optimization

Pattern 1: Avoid SELECT *

Pattern 2: Use WHERE Before HAVING

Pattern 3: Use IN for Multiple Values

Pattern 4: BETWEEN for Range Queries

Pattern 5: Use UNION Instead of OR for Complex Conditions

Join Optimization

Pattern 6: Join with Indexed Foreign Keys

Pattern 7: Join Order Matters

Pattern 8: LEFT JOIN with NULL Filter

Aggregation Optimization

Pattern 9: Aggregate with GROUP BY Efficiently

Pattern 10: Subquery Optimization with Common Table Expressions

Avoiding Common Performance Mistakes

Mistake 1: Functions in WHERE Clauses

Mistake 2: Implicit Type Conversion

Mistake 3: LIKE with Leading Wildcard

Mistake 4: NOT IN with NULL Values

Mistake 5: Unnecessary DISTINCT

Advanced Optimization Techniques

Query Caching Strategies

Partitioning Large Tables

Denormalization for Read Performance

Connection Pooling

Performance Testing Workflow

Optimization Checklist

Before Optimizing

Common Optimizations to Try

After Implementing Changes

Related Topics

Summary