SQL query optimization transforms slow queries into fast ones. The same data retrieval can take milliseconds or minutes depending on how the query is written and what indexes exist. Understanding how databases execute queries helps you write efficient SQL and design appropriate indexes.
Query optimization isn't guesswork. Databases provide execution plans that show exactly how queries run. Reading these plans reveals why queries are slow and what changes would help.
Understanding Execution Plans
Execution plans show the operations a database performs to execute a query. Each operation reads input, performs work, and produces output. The plan forms a tree where leaf nodes access data and internal nodes combine results.
The EXPLAIN ANALYZE command in PostgreSQL shows both the estimated plan and actual execution statistics. This lets you compare what the planner predicted versus what actually happened, which is invaluable for identifying planning issues.
-- PostgreSQL: Get execution plan
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending';
-- Example output:
-- Index Scan using idx_orders_customer_status on orders
-- (cost=0.43..8.45 rows=1 width=120) (actual time=0.023..0.025 rows=2 loops=1)
-- Index Cond: ((customer_id = 123) AND (status = 'pending'::text))
-- Planning Time: 0.085 ms
-- Execution Time: 0.042 ms
Key metrics in execution plans include estimated versus actual rows (large discrepancies indicate stale statistics), scan types (index scan good, sequential scan often bad for large tables), and cost estimates (higher means slower).
Sequential scans read every row in a table. For small tables, this is fine. For large tables, it's slow. Index scans read only relevant rows by traversing an index.
Without an index, even a simple equality lookup must scan the entire table. Adding an index transforms the same query from a linear scan to a logarithmic lookup, often reducing execution time by orders of magnitude.
-- This forces a sequential scan if no index exists on email
SELECT * FROM users WHERE email = 'user@example.com';
-- With an index on email, this uses an index scan
CREATE INDEX idx_users_email ON users(email);
-- Now the same query is much faster
Index Design
Indexes speed up reads but slow down writes. Each insert, update, or delete must maintain all relevant indexes. The art is creating indexes that help common queries without creating too many.
Single-column indexes handle equality lookups well. The most selective column (fewest matching rows) should have an index.
Start with the columns that appear most frequently in WHERE clauses, especially those with high selectivity. An index on email is typically more useful than an index on status because email values are unique while status might only have a handful of distinct values.
-- Index for looking up users by email
CREATE INDEX idx_users_email ON users(email);
-- Query uses the index
SELECT * FROM users WHERE email = 'user@example.com';
Composite indexes cover multiple columns. Column order matters; the index is useful for queries filtering on leading columns but not trailing-only columns.
When designing composite indexes, consider the queries that will use them. The following example shows how the same index can be fully utilized, partially utilized, or not used at all depending on which columns appear in your WHERE clause.
-- Composite index on (customer_id, status, created_at)
CREATE INDEX idx_orders_customer_status_date ON orders(customer_id, status, created_at);
-- Uses the index (filters on leading columns)
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';
-- Uses the index (filters on all columns)
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending' AND created_at > '2024-01-01';
-- Cannot use the index effectively (skips leading column)
SELECT * FROM orders WHERE status = 'pending';
The leftmost prefix rule is key here. Think of a composite index like a phone book sorted by last name, then first name. You can look up "Smith" efficiently, and "Smith, John" even more efficiently, but you cannot efficiently find all people named "John" regardless of last name.
Covering indexes include all columns needed by a query, eliminating the need to access the table at all.
When your query only needs columns that are all present in an index, the database can satisfy the entire query from the index without touching the table data. This is called an index-only scan and can dramatically improve performance for frequently-run queries.
-- Query needs customer_id, status, and total
SELECT customer_id, status, total FROM orders WHERE customer_id = 123;
-- Covering index includes the queried columns
CREATE INDEX idx_orders_covering ON orders(customer_id, status, total);
-- Now the query reads only from the index
Query Rewriting
Sometimes the same result can be achieved with different query structures. Some structures are more optimizer-friendly than others.
Avoid functions on indexed columns. When you apply a function to a column, the index becomes unusable for that expression.
This is one of the most common query optimization mistakes. The database cannot use an index on email to find LOWER(email) because it would need to apply the function to every row first. You can either normalize your data or create an expression index.
-- Bad: Function on indexed column prevents index use
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Good: Store normalized data or use expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Or store emails in lowercase and query directly
Use EXISTS instead of IN for correlated subqueries. EXISTS can short-circuit when finding the first match.
The EXISTS clause returns true as soon as it finds one matching row, while IN with a subquery may need to evaluate all matches. For large subquery results, this difference can be significant.
-- Potentially slow: IN with subquery
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);
-- Often faster: EXISTS
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000);
Avoid SELECT * when you don't need all columns. Retrieving fewer columns reduces I/O and can enable covering index use.
Every column you select adds to the data that must be read from disk and transferred across the network. Selecting only the columns you actually need reduces this overhead and may allow index-only scans.
-- Retrieves all columns, even unused ones
SELECT * FROM orders WHERE customer_id = 123;
-- Retrieves only needed columns
SELECT id, status, total FROM orders WHERE customer_id = 123;
Join Optimization
Joins combine data from multiple tables. The join algorithm significantly impacts performance.
Nested loop joins iterate through one table, looking up matching rows in the other. They work well when the inner table has an index on the join column.
Hash joins build a hash table from one table, then probe it with the other. They work well for large tables without indexes on join columns.
Merge joins sort both tables and merge them. They work well when both inputs are already sorted.
The most important optimization for joins is ensuring indexes exist on the join columns. Without indexes, the database may resort to hash joins or nested loops without index lookups, both of which can be slow for large tables.
-- Ensure index exists on join columns
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
-- Join uses the index for efficient lookups
SELECT o.id, o.total, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.customer_id = 123;
Join order matters. The optimizer chooses join order, but hints can override it when the optimizer chooses poorly.
Pagination Optimization
Offset-based pagination degrades as offset increases. The database must scan and discard all rows before the offset.
Offset pagination seems intuitive, but it becomes progressively slower as users navigate deeper into result sets. The database must read and discard all the rows before your offset, even though you only want the rows after it.
-- Slow for large offsets: scans 10000 rows to return 20
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000;
-- Keyset pagination: consistently fast regardless of "page"
SELECT * FROM orders
WHERE created_at < '2024-01-15T10:30:00'
ORDER BY created_at DESC
LIMIT 20;
Keyset pagination uses the last seen value to find the next page. It's consistently fast but requires knowing the previous page's boundary.
Avoiding N+1 Queries
N+1 queries occur when code fetches a collection, then makes a separate query for each item's related data. This pattern is common in ORMs and devastating for performance.
The N+1 problem is easy to introduce and sometimes hard to spot. In this example, fetching 100 orders would trigger 101 queries: one for the orders and one for each order's items. Eager loading collapses this into just 2 queries regardless of how many orders you fetch.
// N+1 problem: 1 query for orders + N queries for items
$orders = Order::where('customer_id', 123)->get();
foreach ($orders as $order) {
// Each iteration triggers a query!
$items = $order->items;
}
// Solution: Eager loading with single query
$orders = Order::where('customer_id', 123)
->with('items') // Loads all items in one query
->get();
Query Analysis Tools
Beyond execution plans, databases provide tools for query analysis.
Each database has its own tooling for query profiling. PostgreSQL's EXPLAIN with BUFFERS shows actual I/O statistics. MySQL's profiling shows time spent in each execution phase. Both databases can log slow queries for later analysis.
-- PostgreSQL: Detailed statistics
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;
-- MySQL: Query profiling
SET profiling = 1;
SELECT ...;
SHOW PROFILES;
-- Find slow queries in logs
-- PostgreSQL: log_min_duration_statement = 1000
-- MySQL: slow_query_log = 1, long_query_time = 1
Identify slow queries through logging. Configure the database to log queries exceeding a duration threshold. Review these logs regularly to find optimization opportunities.
Statistics and Maintenance
Query optimizers rely on table statistics to estimate row counts and choose plans. Stale statistics lead to poor plan choices.
After significant data changes, update your table statistics so the query planner has accurate information. The planner uses these statistics to estimate how many rows will match various conditions, which directly affects its choice of execution plan.
-- PostgreSQL: Update statistics
ANALYZE orders;
-- MySQL: Update statistics
ANALYZE TABLE orders;
-- PostgreSQL: Maintenance (reclaims space, updates statistics)
VACUUM ANALYZE orders;
Regular maintenance keeps databases performing well. Schedule VACUUM (PostgreSQL) or OPTIMIZE TABLE (MySQL) during low-traffic periods.
Conclusion
SQL query optimization combines understanding execution plans, designing appropriate indexes, writing efficient queries, and maintaining statistics. The database's execution plan reveals exactly what it's doing; use this information rather than guessing.
Start with the slowest queries. Use execution plans to understand why they're slow. Add indexes for frequently filtered or joined columns. Rewrite queries to be more optimizer-friendly. Monitor query performance continuously and address regressions quickly.
