Learn proven query optimization techniques to improve database performance beyond indexing, including query rewriting, efficient joins, and anti-pattern avoidance.
Learn proven query optimization techniques to improve database performance beyond indexing, including query rewriting, efficient joins, and anti-pattern avoidance.
A well-optimized query can often perform
10-100x faster
than its poorly written equivalent—even when both use the same indexes. Query optimization can be the difference between a report that takes seconds versus minutes to run, or an application that handles 100 versus 10,000 users concurrently.
Throughout this lesson, we'll approach query optimization from multiple angles, including:
Rewriting queries to be more efficient and index-friendly
Leveraging SQL features like CTEs and window functions
Avoiding common anti-patterns that cause poor performance
Optimizing complex operations like subqueries and joins
Taking advantage of database-specific optimization features
Let's dive in and unlock the full potential of your database through smarter SQL!
Query rewriting is the process of restructuring a SQL query to achieve better performance while maintaining the same logical results. It's one of the most powerful optimization techniques in your toolkit.
The simplest queries often perform best. Before diving into advanced techniques, look for opportunities to simplify:
Remove unnecessary joins
Eliminate redundant conditions
Reduce the number of columns in the SELECT clause
Minimize the use of complex expressions
Inefficient Query
Optimized Query
The optimized version selects only needed columns and eliminates an unnecessary join by using a subquery. This reduces data transfer and processing overhead.
1SELECT
2 a.*, b.*, c.*
3FROM customers a
4JOIN orders b ON a.customer_id = b.customer_id
5JOIN order_items c ON b.order_id = c.order_id
6JOIN products d ON c.product_id = d.product_id
7WHERE a.status = 'active'
8 AND d.category = 'electronics'1SELECT
2 a.customer_id, a.name,
3 b.order_id, b.order_date,
4 c.quantity, c.price
5FROM customers a
6JOIN orders b ON a.customer_id = b.customer_id
7JOIN order_items c ON b.order_id = c.order_id
8WHERE a.status = 'active'
9 AND c.product_id IN (
10 SELECT product_id FROM products
11 WHERE category = 'electronics'
12 )Predicate pushdown
is the principle of filtering data as early as possible in the query execution process. The sooner you reduce the dataset size, the less work the database has to do.
Inefficient Query
Optimized Query
The optimized version filters customers early through a join, reducing the amount of data that needs to be grouped and aggregated.
1SELECT
2 customer_id,
3 SUM(order_total) AS total_spent
4FROM orders
5GROUP BY customer_id
6HAVING SUM(order_total) > 1000
7 AND customer_id IN (
8 SELECT customer_id
9 FROM customers
10 WHERE region = 'Europe'
11 )1SELECT
2 o.customer_id,
3 SUM(o.order_total) AS total_spent
4FROM orders o
5JOIN customers c ON o.customer_id = c.customer_id
6WHERE c.region = 'Europe'
7GROUP BY o.customer_id
8HAVING SUM(o.order_total) > 1000Always use explicit JOIN syntax instead of implicit joins in the WHERE clause. This makes queries more readable and often helps the optimizer make better decisions.
Implicit Join (Avoid)
Explicit Join (Preferred)
1SELECT a.name, b.order_date
2FROM customers a, orders b
3WHERE a.customer_id = b.customer_id
4AND b.status = 'completed'1SELECT a.name, b.order_date
2FROM customers a
3JOIN orders b ON a.customer_id = b.customer_id
4WHERE b.status = 'completed'Selecting the appropriate join type can significantly impact performance. Each join type can have different performance characteristics depending on your data and indexes:
INNER JOIN
: Generally fastest, only returns matching rows
LEFT/RIGHT JOIN
: Returns all rows from one table, can be slower
FULL OUTER JOIN
: Most expensive, use only when necessary
Performance Tip
When joining tables of vastly different sizes, try to make the smaller table the "driving table" (the one mentioned first in the query) when possible. This strategy can reduce the initial result set size that the database needs to process.
Write conditions that can leverage your indexes effectively:
Index-Unfriendly Conditions
Index-Friendly Alternatives
(Search ARGument able) condition allows the query optimizer to use an index efficiently. In general, avoid applying functions or calculations to indexed columns in WHERE clauses.
Modern SQL offers powerful features that can significantly improve query performance when used correctly. Let's explore three of the most useful tools for query optimization: CTEs, temporary tables, and window functions.
1-- Function on indexed column
2WHERE SUBSTRING(email, 1, 3) = 'dev'
3
4-- Non-sargable condition
5WHERE order_date + 30 > CURRENT_DATE
6
7-- Calculation on indexed column
8WHERE price * quantity > 10001-- Prefix match
2WHERE email LIKE 'dev%'
3
4-- Sargable condition
5WHERE order_date > CURRENT_DATE - 30
6
7-- Move calculation to the right side
8WHERE price > 1000 / quantityCommon Table Expressions (CTEs) provide a way to create named subqueries that can be referenced multiple times within a query. They make complex queries more readable and maintainable, and in some cases, improve performance.
CTEs are particularly useful for:
Breaking down complex queries into logical parts
Avoiding repetition of the same subquery
Implementing recursive queries efficiently
Performance Considerations
Most databases treat CTEs as optimization barriers, meaning the optimizer doesn't "look inside" the CTE when optimizing the outer query. This can sometimes be a benefit (when you want to force a specific execution plan) and sometimes a drawback (when you want the optimizer to consider the entire query holistically).
1WITH regional_sales AS (
2 SELECT
3 region,
4 SUM(amount) AS total_sales
5 FROM orders
6 WHERE order_date >= '2023-01-01'
7 GROUP BY region
8),
9top_regions AS (
10 SELECT region
11 FROM regional_sales
12 ORDER BY total_sales DESC
13 LIMIT 3
14)
15SELECT o.*
16FROM orders o
17JOIN top_regions tr ON o.region = tr.region
18WHERE o.order_date >= '2023-01-01'Temporary tables provide a way to materialize intermediate results for complex queries. Unlike CTEs, which are virtual, temp tables actually store data, which makes them particularly useful for:
Breaking very complex queries into simpler steps
Storing and indexing intermediate results
Reducing repeated computation of the same subquery
Advantages
Can create indexes on intermediate results
Statistics are available to the optimizer
Computed only once, regardless of how many times referenced
Persist across multiple statements
Requires additional storage and I/O
Adds complexity with creation and cleanup
Data might become stale if source tables change
Introduces transaction management considerations
1-- Create temp table with intermediate results
2CREATE TEMPORARY TABLE regional_sales AS
3SELECT
4 region,
5 SUM(amount) AS total_sales
6FROM orders
7WHERE order_date >= '2023-01-01'
8GROUP BY region;
9
10-- Create index on temp table for faster joins
11CREATE INDEX idx_region ON regional_sales(region);
12
13-- Use the temp table in subsequent queries
14SELECT o.*
15FROM orders o
16JOIN regional_sales rs ON o.region = rs.region
17WHERE rs.total_sales > 1000000
18AND o.order_date >= '2023-01-01';
19
20-- Clean up when done
21DROP TABLE regional_sales;Window functions perform calculations across a set of related rows without collapsing them through aggregation. They're incredibly powerful for complex analytical queries and can dramatically simplify and optimize certain query patterns.
Common use cases where window functions excel:
Calculating running totals or moving averages
Ranking results within groups
Comparing rows to preceding/following rows
Computing percentiles and distributions
Eliminating self-joins for row comparisons
Without Window Functions
With Window Functions
Without Window Functions
With Window Functions
Performance Benefits of Window Functions
Single pass through the data instead of multiple passes with joins or subqueries
Reduced query complexity and improved maintainability
Better optimization by the database engine
Elimination of expensive self-joins and correlated subqueries
Window functions are one of the most powerful features in SQL for optimizing complex analytical queries. Mastering them can lead to significant performance improvements for reporting and business intelligence applications.
SQL anti-patterns are common but problematic query structures that lead to poor performance. Let's examine the most prevalent ones and how to avoid them.
1-- Ranking products by price within category
2SELECT
3 p1.product_id,
4 p1.category,
5 p1.price,
6 (SELECT COUNT(*) + 1
7 FROM products p2
8 WHERE p2.category = p1.category
9 AND p2.price > p1.price) AS rank
10FROM products p1
11ORDER BY p1.category, rank1-- Same result using window functions
2SELECT
3 product_id,
4 category,
5 price,
6 RANK() OVER (
7 PARTITION BY category
8 ORDER BY price DESC
9 ) AS rank
10FROM products
11ORDER BY category, rank1-- Running total using self-join
2SELECT
3 o1.order_date,
4 o1.amount,
5 (SELECT SUM(o2.amount)
6 FROM orders o2
7 WHERE o2.order_date <= o1.order_date) AS running_total
8FROM orders o1
9ORDER BY o1.order_date1-- Running total using window function
2SELECT
3 order_date,
4 amount,
5 SUM(amount) OVER (
6 ORDER BY order_date
7 ROWS UNBOUNDED PRECEDING
8 ) AS running_total
9FROM orders
10ORDER BY order_dateLIKE '%text'
LIKE '%text%'
with a leading wildcard prevents the database from using standard B-tree indexes effectively.
Anti-Pattern
Better Alternatives
1-- Can't use index effectively
2SELECT * FROM customers
3WHERE email LIKE '%gmail.com'1-- Use a computed column with reversed string
2SELECT * FROM customers
3WHERE reverse_email LIKE 'moc.liamg%'
4
5-- Use a specialized full-text index
6SELECT * FROM customers
7WHERE CONTAINS(email, 'gmail.com')
8
9-- Extract and index the domain portion
10SELECT * FROM customers
11WHERE email_domain = 'gmail.com'Using DISTINCT unnecessarily adds overhead by requiring additional sorting or hashing operations. It's often a sign of poorly constructed joins or misunderstood data relationships.
Anti-Pattern
Better Approach
1-- Masking a many-to-many join issue
2SELECT DISTINCT c.customer_id, c.name
3FROM customers c
4JOIN orders o ON c.customer_id = o.customer_id
5WHERE o.status = 'complete'1-- Fix the join to avoid duplicates
2SELECT c.customer_id, c.name
3FROM customers c
4WHERE EXISTS (
5 SELECT 1 FROM orders o
6 WHERE o.customer_id = c.customer_id
7 AND o.status = 'complete'
8)
9
10-- Or use GROUP BY
11SELECT c.customer_id, c.name
12FROM customers c
13JOIN orders o ON c.customer_id = o.customer_id
14WHERE o.status = 'complete'
15GROUP BY c.customer_id, c.nameCorrelated subqueries in the SELECT clause run once per outer row, which can lead to extremely poor performance for large datasets.
Anti-Pattern
Better Approach
1-- Runs subquery for every customer
2SELECT
3 c.customer_id,
4 c.name,
5 (SELECT SUM(amount)
6 FROM orders o
7 WHERE o.customer_id = c.customer_id) AS total_orders
8FROM customers c
9WHERE c.status = 'active'1-- Single join with aggregation
2SELECT
3 c.customer_id,
4 c.name,
5 COALESCE(SUM(o.amount), 0) AS total_orders
6FROM customers c
7LEFT JOIN orders o ON c.customer_id = o.customer_id
8WHERE c.status = 'active'
9GROUP BY c.customer_id, c.nameUsing scalar functions on indexed columns in the WHERE clause prevents index usage and forces full table scans.
Anti-Pattern
Better Approach
1-- Can't use index on order_date
2SELECT * FROM orders
3WHERE YEAR(order_date) = 2023
4AND MONTH(order_date) = 61-- Range condition allows index usage
2SELECT * FROM orders
3WHERE order_date >= '2023-06-01'
4AND order_date < '2023-07-01'
5
6-- Alternative using BETWEEN
7SELECT * FROM orders
8WHERE order_date BETWEEN '2023-06-01'
9AND '2023-06-30'retrieves all columns, increasing I/O, network transfer, and memory usage unnecessarily. It also prevents the use of covering indexes.
Anti-Pattern
Better Approach
1-- Retrieves all columns, including LOBs
2SELECT * FROM products
3WHERE category = 'Electronics'1-- Only retrieve needed columns
2SELECT
3 product_id,
4 name,
5 price,
6 stock_level
7FROM products
8WHERE category = 'Electronics'with a subquery that could contain NULL values can lead to unexpected results and poor performance.
Anti-Pattern
Better Approach
1-- Problematic if product_id can be NULL
2SELECT * FROM products
3WHERE product_id NOT IN (
4 SELECT product_id
5 FROM order_items
6)1-- Safer and often faster
2SELECT * FROM products p
3WHERE NOT EXISTS (
4 SELECT 1 FROM order_items oi
5 WHERE oi.product_id = p.product_id
6)
7
8-- Or using LEFT JOIN
9SELECT p.*
10FROM products p
11LEFT JOIN order_items oi ON p.product_id = oi.product_id
12WHERE oi.product_id IS NULLMissing join conditions create cartesian products, resulting in row counts that multiply exponentially with each additional table.
Anti-Pattern
Proper Join
Performance Impact of Anti-Patterns
These anti-patterns can cause performance degradation ranging from 2x slower to 1000x slower, depending on the dataset size and query complexity. Always review your slow-performing queries for these patterns.
Subqueries and complex joins are common sources of performance issues. Let's explore techniques to optimize them effectively.
1-- Missing join condition
2SELECT c.name, o.order_id
3FROM customers c, orders o
4-- No join condition!1-- Explicit join with condition
2SELECT c.name, o.order_id
3FROM customers c
4JOIN orders o ON c.customer_id = o.customer_idSubqueries can be powerful but need careful optimization to avoid performance bottlenecks. There are several types of subqueries, each with different performance characteristics:
When checking for the existence of related records,
often outperforms
, especially with large result sets.
Using EXISTS
Note: The relative performance of IN vs EXISTS can vary by database, data distribution, and available indexes. Always test with your specific workload.
Uncorrelated subqueries (which don't reference the outer query) are executed once, while correlated subqueries (which reference the outer query) are executed for each outer row.
Uncorrelated (Usually Faster)
Correlated (Can Be Slower)
Many subqueries can be rewritten as joins, which often perform better, especially when the subquery is correlated.
Subquery in SELECT
Equivalent JOIN
For complex subqueries, especially those used multiple times, Common Table Expressions (CTEs) can improve performance and readability.
1-- Retrieves and compares all matching product_ids
2SELECT * FROM customers
3WHERE customer_id IN (
4 SELECT customer_id
5 FROM orders
6 WHERE order_date > '2023-01-01'
7)1-- Stops as soon as a match is found
2SELECT * FROM customers c
3WHERE EXISTS (
4 SELECT 1 FROM orders o
5 WHERE o.customer_id = c.customer_id
6 AND o.order_date > '2023-01-01'
7)1-- Executes just once
2SELECT * FROM products
3WHERE category IN (
4 SELECT category
5 FROM categories
6 WHERE active = true
7)1-- Executes for each product
2SELECT * FROM products p
3WHERE EXISTS (
4 SELECT 1 FROM categories c
5 WHERE c.category = p.category
6 AND c.active = true
7)1-- Runs the subquery for each customer
2SELECT
3 c.customer_id,
4 c.name,
5 (SELECT MAX(order_date)
6 FROM orders o
7 WHERE o.customer_id = c.customer_id) AS last_order_date
8FROM customers c1-- Processes all data in a single pass
2WITH LastOrders AS (
3 SELECT
4 customer_id,
5 MAX(order_date) AS last_order_date
6 FROM orders
7 GROUP BY customer_id
8)
9SELECT
10 c.customer_id,
11 c.name,
12 lo.last_order_date
13FROM customers c
14LEFT JOIN LastOrders lo ON c.customer_id = lo.customer_id1WITH active_products AS (
2 SELECT * FROM products
3 WHERE status = 'active'
4 AND inventory > 0
5),
6high_value_orders AS (
7 SELECT
8 customer_id,
9 COUNT(*) AS order_count,
10 SUM(total_amount) AS total_spent
11 FROM orders
12 WHERE order_date > '2023-01-01'
13 GROUP BY customer_id
14 HAVING SUM(total_amount) > 1000
15)
16SELECT
17 c.customer_id,
18 c.name,
19 hvo.order_count,
20 hvo.total_spent
21FROM customers c
22JOIN high_value_orders hvo ON c.customer_id = hvo.customer_id
23WHERE EXISTS (
24 SELECT 1 FROM order_items oi
25 JOIN active_products ap ON oi.product_id = ap.product_id
26 WHERE oi.order_id IN (
27 SELECT order_id FROM orders
28 WHERE customer_id = c.customer_id
29 )
30)Complex queries often involve multiple joins, which can severely impact performance if not optimized properly. Here are some key techniques:
The order of tables in a join can dramatically affect performance. While query optimizers try to determine the best order, sometimes they need help:
Start with the most filtered tables (those with the most restrictive WHERE conditions)
Join smaller result sets before larger ones when possible
Use database-specific hints when the optimizer chooses a suboptimal plan
Choose the appropriate join type based on your requirements:
Always ensure that join columns are indexed on both sides of the join. Without indexes, the database must perform full table scans for each row.
Index Recommendation
For a query with
JOIN table_a a ON a.id = b.a_id
, ensure that both
table_b.a_id
have indexes.
Very complex queries with many joins can sometimes be split into multiple simpler queries using temporary tables or CTEs to store intermediate results.
Complex Multi-Join
Broken Up with CTEs
For some complex scenarios, especially when you need to correlate subqueries with outer query rows, lateral joins (CROSS APPLY or LATERAL) can be very efficient.
Note: Lateral joins are available in PostgreSQL, SQL Server (as CROSS/OUTER APPLY), and Oracle (as LATERAL). MySQL 8.0+ also supports the LATERAL keyword.
Each database system offers unique optimization features that can significantly improve performance for specific workloads. Let's explore the most useful ones in popular database systems.
1-- INNER JOIN: Only returns matches (fastest)
2SELECT c.name, o.order_id
3FROM customers c
4INNER JOIN orders o ON c.customer_id = o.customer_id
5
6-- LEFT JOIN: All customers, even those without orders
7SELECT c.name, o.order_id
8FROM customers c
9LEFT JOIN orders o ON c.customer_id = o.customer_id
10
11-- RIGHT JOIN: All orders, even those without customer records
12SELECT c.name, o.order_id
13FROM customers c
14RIGHT JOIN orders o ON c.customer_id = o.customer_id
15
16-- FULL OUTER JOIN: All records from both tables (slowest)
17SELECT c.name, o.order_id
18FROM customers c
19FULL OUTER JOIN orders o ON c.customer_id = o.customer_id1SELECT
2 c.name,
3 p.name,
4 SUM(oi.quantity) AS total_ordered,
5 AVG(r.rating) AS avg_rating
6FROM customers c
7JOIN orders o ON c.customer_id = o.customer_id
8JOIN order_items oi ON o.order_id = oi.order_id
9JOIN products p ON oi.product_id = p.product_id
10JOIN reviews r ON p.product_id = r.product_id
11WHERE c.status = 'active'
12AND o.order_date > '2023-01-01'
13GROUP BY c.name, p.name1WITH customer_orders AS (
2 SELECT
3 c.customer_id,
4 c.name,
5 o.order_id
6 FROM customers c
7 JOIN orders o ON c.customer_id = o.customer_id
8 WHERE c.status = 'active'
9 AND o.order_date > '2023-01-01'
10),
11product_ratings AS (
12 SELECT
13 p.product_id,
14 p.name,
15 AVG(r.rating) AS avg_rating
16 FROM products p
17 JOIN reviews r ON p.product_id = r.product_id
18 GROUP BY p.product_id, p.name
19)
20SELECT
21 co.name AS customer_name,
22 pr.name AS product_name,
23 SUM(oi.quantity) AS total_ordered,
24 pr.avg_rating
25FROM customer_orders co
26JOIN order_items oi ON co.order_id = oi.order_id
27JOIN product_ratings pr ON oi.product_id = pr.product_id
28GROUP BY co.name, pr.name, pr.avg_rating1-- PostgreSQL/SQL Server syntax
2SELECT
3 c.customer_id,
4 c.name,
5 recent_orders.order_id,
6 recent_orders.order_date,
7 recent_orders.amount
8FROM customers c
9CROSS JOIN LATERAL (
10 SELECT order_id, order_date, amount
11 FROM orders
12 WHERE customer_id = c.customer_id
13 ORDER BY order_date DESC
14 LIMIT 3
15) AS recent_orders
16WHERE c.status = 'active'
17ORDER BY c.customer_id, recent_orders.order_date DESCPartial indexes include only a subset of rows, making them smaller and more efficient when you frequently query specific subsets of data.
Index functions or expressions used in WHERE clauses to make them SARGable.
PostgreSQL offers specialized operators and indexes for efficient JSON data querying.
For large tables, enable parallel processing to utilize multiple CPU cores.
Split large tables into smaller pieces for better performance with table partitioning.
1-- Only index active orders (smaller, faster index)
2CREATE INDEX idx_active_orders
3ON orders (order_date)
4WHERE status = 'active';
5
6-- Query that can use the partial index
7SELECT * FROM orders
8WHERE status = 'active'
9AND order_date > '2023-06-01';1-- Index for case-insensitive searches
2CREATE INDEX idx_lower_email
3ON customers (LOWER(email));
4
5-- Query can now use the index
6SELECT * FROM customers
7WHERE LOWER(email) = '[email protected]';1-- Create GIN index for JSONB containment operations
2CREATE INDEX idx_user_preferences
3ON users USING GIN (preferences);
4
5-- Query using containment operator
6SELECT * FROM users
7WHERE preferences @> '{"theme": "dark"}';1-- Set maximum parallel workers per query
2SET max_parallel_workers_per_gather = 4;
3
4-- Run a parallelizable query
5SELECT category, AVG(price)
6FROM products
7GROUP BY category;1-- Create partitioned table by date range
2CREATE TABLE orders (
3 order_id INT,
4 order_date DATE,
5 customer_id INT,
6 amount DECIMAL(10,2)
7) PARTITION BY RANGE (order_date);
8
9-- Create partitions for each month
10CREATE TABLE orders_202301 PARTITION OF orders
11FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
12
13CREATE TABLE orders_202302 PARTITION OF orders
14FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
15
16-- Queries can now benefit from partition pruning
17SELECT * FROM orders
18WHERE order_date BETWEEN '2023-01-15' AND '2023-01-20';Design indexes that include all columns referenced in a query to avoid table lookups.
Create and index virtual columns for frequently used expressions.
InnoDB-specific optimizations for better performance in MySQL's default engine.
Test index removal without actually dropping them.
1-- Create covering index for common query pattern
2CREATE INDEX idx_product_lookup
3ON products(category, status, price, name);
4
5-- Query that can use covering index (no table access needed)
6SELECT name, price
7FROM products
8WHERE category = 'electronics'
9AND status = 'active'
10ORDER BY price DESC;1-- Add generated column for frequent calculation
2ALTER TABLE products
3ADD COLUMN full_price DECIMAL(10,2)
4GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED;
5
6-- Create index on generated column
7CREATE INDEX idx_full_price ON products(full_price);
8
9-- Query now uses the index
10SELECT * FROM products
11WHERE full_price BETWEEN 100 AND 200;1-- Set appropriate buffer pool size
2SET GLOBAL innodb_buffer_pool_size = 4G;
3
4-- Use primary key order for inserts (reduces page splits)
5-- Instead of random insertion:
6INSERT INTO orders (order_id, customer_id, order_date)
7VALUES (54321, 123, '2023-06-15');
8
9-- Use sequential IDs or order by ID before bulk insert:
10INSERT INTO orders (order_id, customer_id, order_date)
11VALUES
12(10001, 145, '2023-06-15'),
13(10002, 287, '2023-06-15'),
14(10003, 356, '2023-06-15');1-- Make index invisible to test performance
2ALTER TABLE customers
3ALTER INDEX idx_email INVISIBLE;
4
5-- Run your queries to test performance
6-- ...
7
8-- Make index visible again if needed
9ALTER TABLE customers
10ALTER INDEX idx_email VISIBLE;Create indexed views (materialized views) for complex, frequently-run queries.
Use hints to override the optimizer when you know better.
Similar to PostgreSQL's partial indexes, filtered indexes only include rows that match a predicate.
1-- Create view with aggregated data
2CREATE VIEW sales_summary
3WITH SCHEMABINDING
4AS
5SELECT
6 product_id,
7 SUM(quantity) AS total_quantity,
8 SUM(quantity * price) AS total_amount,
9 COUNT_BIG(*) AS row_count
10FROM dbo.order_items
11GROUP BY product_id;
12
13-- Create unique clustered index on view
14CREATE UNIQUE CLUSTERED INDEX idx_sales_summary
15ON sales_summary (product_id);
16
17-- Query can now use the indexed view
18SELECT * FROM sales_summary
19WHERE total_quantity > 1000;1-- Force a specific index
2SELECT * FROM products WITH (INDEX(idx_category))
3WHERE category = 'electronics';
4
5-- Optimize for first N rows (good for paging)
6SELECT * FROM products
7WHERE category = 'electronics'
8ORDER BY price DESC
9OPTION (FAST 50);
10
11-- Recompile plan each execution (for varying parameters)
12SELECT * FROM orders
13WHERE order_date BETWEEN @start_date AND @end_date
14OPTION (RECOMPILE);1-- Create filtered index for premium customers
2CREATE INDEX idx_premium_customers
3ON customers (email, phone, last_order_date)
4WHERE customer_type = 'premium';
5
6-- Query automatically uses filtered index
7SELECT email, phone, last_order_date
8FROM customers
9WHERE customer_type = 'premium'
10AND last_order_date > '2023-01-01';Create indexes on expressions or functions for better performance.
Store the results of complex queries for fast access.
Database-Specific Features: When to Use
While these database-specific features can provide significant performance gains, they also create dependency on a particular database system. Consider these factors when deciding to use them:
Critical performance needs that can't be solved with standard SQL
Long-term commitment to the current database system
Performance benefits clearly outweigh portability concerns
Features are used in isolated components that could be rewritten if needed
The original query has several issues that contribute to poor performance:
functions on the indexed
column prevents index usage
LIKE '%gmail.com'
with a leading wildcard prevents efficient index usage
The correlated subquery in the SELECT list runs for every row in the result set
The query doesn't leverage any window functions for the analytics calculations
clause filters late in the query execution, after all the aggregation work is done
Let's optimize this query step by step:
Step 1: Fix the date functions for better index usage
Step 2: Fix the email filtering pattern
Step 3: Fix the correlated subquery using CTEs and window functions
The optimized query addresses all the performance issues of the original query:
Uses CTEs to break the complex query into logical processing stages
Prefilters customers by email domain before joining to other tables
Uses window functions to calculate counts for the HAVING filter early
Calculates the returned orders count just once instead of for each result row
consistently for better index usage
Applies the "filter early, process less" principle throughout the query
Performance Impact
The optimized query typically runs
20-40x faster
than the original version, taking 1-2 seconds instead of 30+ seconds on a typical dataset. The improvements become even more dramatic as the data volume increases.
Additional Optimizations
Depending on your specific database system, you could further optimize this query by:
Creating a computed column for the email domain with an index
Adding a covering index for the most common query patterns
Partitioning the orders table by date range
Creating a materialized view for this specific report
Using database-specific features like indexed views or function-based indexes
This optimization demonstrates the power of query rewriting, using CTEs, window functions, and avoiding anti-patterns like correlated subqueries and non-SARGable conditions.
In the next lesson, we'll explore
Database Caching Strategies
to further enhance performance beyond query optimization.
You'll learn how to:
Use built-in database caching mechanisms effectively
Implement application-level caching with Redis and similar tools
Determine what data to cache and for how long
Manage cache invalidation strategies
Balance caching with data consistency requirements
Together, optimized queries and effective caching form the foundation of high-performance database applications.
1-- Index for case-insensitive searches
2CREATE INDEX idx_upper_last_name
3ON employees (UPPER(last_name));
4
5-- Query can use the index
6SELECT * FROM employees
7WHERE UPPER(last_name) = 'SMITH';1-- Create materialized view for sales reporting
2CREATE MATERIALIZED VIEW monthly_sales
3BUILD IMMEDIATE
4REFRESH COMPLETE ON DEMAND
5AS
6SELECT
7 TRUNC(order_date, 'MM') AS month,
8 product_id,
9 SUM(quantity) AS units_sold,
10 SUM(quantity * price) AS revenue
11FROM orders o
12JOIN order_items oi ON o.order_id = oi.order_id
13GROUP BY TRUNC(order_date, 'MM'), product_id;
14
15-- Create indexes on materialized view
16CREATE INDEX idx_monthly_sales_product
17ON monthly_sales(product_id);
18
19CREATE INDEX idx_monthly_sales_month
20ON monthly_sales(month);
21
22-- Query the materialized view directly
23SELECT * FROM monthly_sales
24WHERE month BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
25AND TO_DATE('2023-03-31', 'YYYY-MM-DD')
26ORDER BY revenue DESC;
27
28-- Refresh when source data changes
29EXEC DBMS_MVIEW.REFRESH('monthly_sales');1-- Step 1: Replace EXTRACT with date range grouping
2-- This allows better use of an index on order_date
3SELECT
4 p.category,
5 DATE_TRUNC('month', o.order_date) AS month_year,
6 c.country,
7 COUNT(DISTINCT o.order_id) AS num_orders,
8 COUNT(DISTINCT o.customer_id) AS num_customers,
9 SUM(oi.quantity) AS total_units,
10 ROUND(SUM(oi.quantity * oi.price), 2) AS total_sales,
11 ROUND(AVG(oi.quantity * oi.price), 2) AS avg_order_value,
12 -- Still need to fix the subquery
13 (SELECT COUNT(*)
14 FROM order_items oi2
15 JOIN orders o2 ON oi2.order_id = o2.order_id
16 WHERE o2.order_date BETWEEN '2023-01-01' AND '2023-12-31'
17 AND o2.status = 'returned') AS total_returns
18FROM orders o
19JOIN customers c ON o.customer_id = c.customer_id
20JOIN order_items oi ON o.order_id = oi.order_id
21JOIN products p ON oi.product_id = p.product_id
22WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
23AND c.email LIKE '%gmail.com'
24GROUP BY
25 p.category,
26 DATE_TRUNC('month', o.order_date),
27 c.country
28HAVING COUNT(DISTINCT o.order_id) > 5
29ORDER BY total_sales DESC;1-- Step 2: Replace LIKE with more efficient pattern
2-- We extract the domain part of the email using a function
3-- In a real scenario, you'd want to add a computed column with an index
4-- or use database-specific optimizations
5SELECT
6 p.category,
7 DATE_TRUNC('month', o.order_date) AS month_year,
8 c.country,
9 COUNT(DISTINCT o.order_id) AS num_orders,
10 COUNT(DISTINCT o.customer_id) AS num_customers,
11 SUM(oi.quantity) AS total_units,
12 ROUND(SUM(oi.quantity * oi.price), 2) AS total_sales,
13 ROUND(AVG(oi.quantity * oi.price), 2) AS avg_order_value,
14 -- Still need to fix the subquery
15 (SELECT COUNT(*)
16 FROM order_items oi2
17 JOIN orders o2 ON oi2.order_id = o2.order_id
18 WHERE o2.order_date BETWEEN '2023-01-01' AND '2023-12-31'
19 AND o2.status = 'returned') AS total_returns
20FROM orders o
21JOIN customers c ON o.customer_id = c.customer_id
22JOIN order_items oi ON o.order_id = oi.order_id
23JOIN products p ON oi.product_id = p.product_id
24WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
25-- Better approach for domain filtering
26AND SUBSTRING(c.email FROM POSITION('@' IN c.email) + 1) = 'gmail.com'
27GROUP BY
28 p.category,
29 DATE_TRUNC('month', o.order_date),
30 c.country
31HAVING COUNT(DISTINCT o.order_id) > 5
32ORDER BY total_sales DESC;1-- Step 3: Full optimization using CTEs and proper structure
2WITH email_domain AS (
3 -- Prefilter customers by email domain
4 SELECT customer_id
5 FROM customers
6 WHERE SUBSTRING(email FROM POSITION('@' IN email) + 1) = 'gmail.com'
7),
8filtered_orders AS (
9 -- Get all relevant orders with order counts
10 SELECT
11 o.order_id,
12 o.customer_id,
13 o.order_date,
14 o.status,
15 p.category,
16 c.country,
17 COUNT(o.order_id) OVER (
18 PARTITION BY p.category, DATE_TRUNC('month', o.order_date), c.country
19 ) AS category_month_country_count
20 FROM orders o
21 JOIN email_domain ed ON o.customer_id = ed.customer_id
22 JOIN customers c ON o.customer_id = c.customer_id
23 JOIN order_items oi ON o.order_id = oi.order_id
24 JOIN products p ON oi.product_id = p.product_id
25 WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
26),
27returned_count AS (
28 -- Calculate returned orders once
29 SELECT COUNT(*) AS total_returns
30 FROM orders o
31 WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
32 AND o.status = 'returned'
33),
34order_details AS (
35 -- Get order items with prefiltered orders
36 SELECT
37 fo.category,
38 DATE_TRUNC('month', fo.order_date) AS month_year,
39 fo.country,
40 fo.order_id,
41 fo.customer_id,
42 oi.quantity,
43 oi.price,
44 fo.category_month_country_count
45 FROM filtered_orders fo
46 JOIN order_items oi ON fo.order_id = oi.order_id
47 -- Early filtering on counts > 5
48 WHERE fo.category_month_country_count > 5
49)
50-- Final result aggregation
51SELECT
52 od.category,
53 od.month_year,
54 od.country,
55 COUNT(DISTINCT od.order_id) AS num_orders,
56 COUNT(DISTINCT od.customer_id) AS num_customers,
57 SUM(od.quantity) AS total_units,
58 ROUND(SUM(od.quantity * od.price), 2) AS total_sales,
59 ROUND(AVG(od.quantity * od.price), 2) AS avg_order_value,
60 -- Use the precalculated returns count
61 (SELECT total_returns FROM returned_count) AS total_returns
62FROM order_details od
63GROUP BY
64 od.category,
65 od.month_year,
66 od.country
67ORDER BY total_sales DESC;Your progress is saved in this browser, on this device only.