We've all been there: you run a query, go grab a coffee, come back, and it's still running. Slow SQL queries can kill application performance and frustrate users.
Optimizing SQL is both an art and a science. In this comprehensive guide, we'll explore the techniques that database experts use to transform sluggish queries into lightning-fast operations.
The Performance Mindset
Before diving into techniques, understand this: the database doesn't know your business logic. It follows rules and statistics. Your job is to write queries that align with how the database engine wants to work.
1. Select Only What You Need
The dreaded SELECT * is a common performance killer. It forces the database to fetch every column, increasing I/O and network overhead.
Bad Practice:
SELECT * FROM orders;
Optimized:
SELECT order_id, customer_id, total_amount FROM orders;
Let's see the difference in a simulated environment.
2. Use SARGable Queries
SARGable stands for Search ARGument able. It means writing queries that can take advantage of indexes.
Using functions on columns in your WHERE clause often prevents the database from using an index.
Non-SARGable (Slow):
-- The database has to calculate YEAR() for every row
SELECT * FROM users WHERE YEAR(created_at) = 2023;
SARGable (Fast):
-- This can use an index on the created_at column
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';
3. Avoid Leading Wildcards
Using LIKE '%term' prevents index usage because the database has to scan every single string to see if it ends with the term.
4. Understanding Execution Plans
The most powerful tool for optimization is the EXPLAIN keyword (or EXPLAIN ANALYZE in PostgreSQL). It tells you exactly how the database engine intends to execute your query—whether it will use a full table scan or an index seek.
5. Index Strategy: The Game Changer
Indexes are like a book's index—they help you find data without reading every page. But poorly designed indexes can hurt more than help.
Composite Indexes and Column Order
The order of columns in a composite index matters. The "leftmost prefix" rule means the index can only be used if your query filters on the leftmost columns first.
Example:
-- Index on (last_name, first_name, age)
CREATE INDEX idx_user_name ON users(last_name, first_name, age);
-- ✅ Can use the index
SELECT * FROM users WHERE last_name = 'Smith';
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';
-- ❌ Cannot use the index efficiently
SELECT * FROM users WHERE first_name = 'John';
SELECT * FROM users WHERE age = 30;
Covering Indexes
A "covering index" includes all columns needed by the query, so the database never has to touch the actual table.
6. JOIN Optimization
JOINs can be expensive. The database has to match rows from multiple tables, and the order matters.
Reduce Data Before JOINing
Filter as early as possible. Let the database eliminate rows before the JOIN, not after.
Bad:
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date > '2023-01-01';
Better (using subquery or CTE):
WITH RecentOrders AS (
SELECT * FROM orders WHERE order_date > '2023-01-01'
)
SELECT o.*, c.name
FROM RecentOrders o
JOIN customers c ON o.customer_id = c.customer_id;
In practice, modern query optimizers often rewrite the first version into something like the second. But explicit filtering helps in complex scenarios.
JOIN vs Subquery
Sometimes a correlated subquery is clearer to write but performs terribly because it runs once per row.
The correlated subquery equivalent would run the inner query for every customer row:
-- Correlated subquery (potentially slow)
SELECT customer_name
FROM customers
WHERE EXISTS (
SELECT 1 FROM orders
WHERE orders.customer_id = customers.customer_id
AND total_amount > 100
);
7. EXISTS vs IN
For checking membership, EXISTS often outperforms IN, especially when the subquery returns many rows.
Reason: EXISTS stops as soon as it finds a match (short-circuit), while IN may need to evaluate all values.
8. Batch Operations
Inserting one row at a time is incredibly slow due to transaction overhead. Batch your operations.
Slow (N round-trips):
INSERT INTO logs VALUES (1, 'Event A');
INSERT INTO logs VALUES (2, 'Event B');
-- ... repeat 10,000 times
Fast (1 round-trip):
INSERT INTO logs VALUES
(1, 'Event A'),
(2, 'Event B'),
(3, 'Event C')
-- ... up to thousands of rows
9. Pagination Done Right
The classic OFFSET pagination becomes slow as you go to later pages because the database still has to scan all the skipped rows.
Naive (slow for page 1000):
SELECT * FROM products
ORDER BY product_id
LIMIT 20 OFFSET 20000; -- Page 1001
Keyset Pagination (fast):
-- Remember the last product_id from the previous page
SELECT * FROM products
WHERE product_id > 54320 -- Last ID from previous page
ORDER BY product_id
LIMIT 20;
10. Avoid Implicit Type Conversions
When you compare a string column to a number, the database may need to convert every row, preventing index usage.
Problem:
-- If user_id is VARCHAR, this forces a conversion on every row
SELECT * FROM users WHERE user_id = 12345;
Solution:
-- Use the correct type
SELECT * FROM users WHERE user_id = '12345';
Summary: The Optimization Checklist
When you encounter a slow query, work through this checklist:
- Run EXPLAIN to see the execution plan.
- Check for table scans where indexes should be used.
- Avoid SELECT * — fetch only needed columns.
- Make filters SARGable — no functions on indexed columns in WHERE.
- Use composite indexes wisely — respect the leftmost prefix rule.
- Filter before JOIN — reduce row counts early.
- Prefer EXISTS over IN for large subqueries.
- Batch operations instead of row-by-row processing.
- Use keyset pagination for deep pages.
- Match data types to avoid conversions.
Optimization is iterative. Measure, identify bottlenecks, fix, and repeat. With these techniques, you can turn queries that take minutes into queries that return in milliseconds.