Back to ADVANCED ANALYTICS - PIVOT/UNPIVOT, INDEXING, ORDER OF EXECUTION

Order of Execution - Optimizing Queries

6 minutesVideo LessonPDF notes

Watch Order of Execution - Optimizing Queries on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

Introduction and Sample Query — The instructor outlines the lesson objectives and presents the sample customer spending query.
Query Execution Plans — The role of execution plans in revealing costs, join algorithms, and physical operation order is introduced.
FROM and JOIN Execution — Table assembly and the performance benefits of indexing join keys are detailed.
WHERE and Sargable Queries — The concept of sargability is explained with examples of direct comparisons versus function-wrapped columns.
GROUP BY and HAVING — Aggregation mechanics and post-aggregation filtering with the HAVING clause are examined.
SELECT and Covering Indexes — Column projection order and using covering indexes to eliminate table lookups are discussed.
ORDER BY, LIMIT, and Summary — Sorting optimization techniques are reviewed alongside the final recap of the full execution sequence.

PDF notes

Bottom line: You will master the logical execution order of SQL queries from FROM to LIMIT rather than their written syntax. You will learn to diagnose bottlenecks using execution plans, write sargable predicates, design covering indexes, and eliminate memory-intensive sort operations.

This lesson on Order of Execution - Optimizing Queries is hands-on and example-driven. You will master the logical execution order of SQL queries from FROM to LIMIT rather than their written syntax. You will learn to diagnose bottlenecks using execution plans, write sargable predicates, design covering indexes, and eliminate memory-intensive sort operations.

What You'll Be Able To Do

Trace the eight-phase logical execution pipeline of a standard SQL statement.
Refactor non-sargable WHERE predicates to restore index seek capabilities.
Design covering indexes that eliminate secondary table heap lookups.
Optimize GROUP BY and HAVING clauses by filtering rows before aggregation.
Eliminate sorting overhead on large datasets using indexed ORDER BY and LIMIT clauses.

Detailed Concept Walkthrough

1. Logical Query Execution Pipeline

SQL is a declarative language where the physical execution sequence differs entirely from written syntax. The engine determines data sources and filters before projecting columns or sorting results.

Execution Flow: The database begins with FROM and JOIN to assemble the base dataset, applies WHERE conditions to filter rows, groups the remaining rows with GROUP BY, and evaluates HAVING conditions on aggregate values.
Under the Hood: SELECT is processed late in the pipeline to project required columns and compute expressions, which prevents column aliases created in SELECT from being recognized in WHERE or GROUP BY.
Best Practice: Always design queries around the processing order (FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT) to avoid logical reference errors and redundant row evaluations.

-- Logical pipeline order: FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
SELECT 
    c.id AS customer_id,
    COUNT(o.id) AS total_orders,
    SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= '2023-01-01'
GROUP BY c.id
HAVING SUM(o.amount) >= 1000
ORDER BY total_spent DESC
LIMIT 10;

Key Takeaway: SQL engines process queries starting with data retrieval (FROM/JOIN) and finish with output formatting and pagination (SELECT/ORDER BY/LIMIT).

2. Sargable Filter Predicates

Sargable (Search Argument Able) queries are structured to allow the database engine to traverse B-tree indexes directly rather than scanning entire tables row by row.

Mechanism: Direct comparisons between unmanipulated columns and literals enable the query optimizer to perform fast index seeks along the index tree structure.
Under the Hood: Wrapping an indexed column in a function forces the engine to execute that function on every row in the table, rendering B-tree index seeks impossible.
Best Practice: Isolate raw column references on one side of comparison operators, and use function-based indexes or computed columns when transformations are mandatory.

-- NON-SARGABLE: Forces full table scan to evaluate function per row
SELECT id, amount 
FROM orders 
WHERE YEAR(order_date) >= 2023;

-- SARGABLE: Enables direct B-tree index seek on order_date
SELECT id, amount 
FROM orders 
WHERE order_date >= '2023-01-01';

Key Takeaway: Never wrap indexed columns in functions or calculations inside the WHERE clause.

3. Join and Covering Index Optimization

Indexes on join keys and projected attributes allow the database engine to resolve relationships and extract data directly from memory without secondary disk reads.

Mechanism: Creating indexes on primary and foreign key columns speeds up join algorithms such as nested loops and merge joins by eliminating unindexed full table scans.
Under the Hood: A covering index includes every column needed across the SELECT, JOIN, and WHERE clauses, allowing the engine to satisfy the entire query from the index pages alone.
Best Practice: Build composite covering indexes on high-frequency queries to reduce table I/O operations and avoid random heap lookups.

-- Create a covering index for JOIN, WHERE, and SELECT columns
CREATE INDEX idx_orders_covering 
ON orders (customer_id, order_date, amount);

-- Query executes entirely within the index leaf pages
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY customer_id;

Key Takeaway: Covering indexes eliminate physical table access by providing every requested query column directly from index pages.

4. Aggregation and Sort Optimization

Sorting and grouping require substantial CPU and memory buffers, which can be minimized by pre-filtering and aligning ORDER BY with existing index keys.

Mechanism: WHERE filters rows prior to GROUP BY, shrinking the dataset entering memory before aggregate functions like SUM and COUNT execute.
Under the Hood: ORDER BY without index support requires an explicit sorting operation in working memory, which spills to temporary disk storage on large datasets.
Best Practice: Pair ORDER BY with LIMIT and matching indexes to allow early query termination and eliminate full dataset sorting.

-- Optimal sorting with pre-filtering, aggregation, and indexed limit
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2023-01-01' -- Pre-filter before GROUP BY
GROUP BY customer_id
HAVING SUM(amount) >= 1000       -- Filter aggregated groups
ORDER BY total_spent DESC        -- Order aggregated output
LIMIT 10;                        -- Terminate after top 10 matches

Key Takeaway: Filter aggressively before aggregation and utilize indexes to prevent expensive in-memory sort spills.

Topics Covered in Order of Execution - Optimizing Queries

Introduction and Sample Query (0:00 - 0:30) — The instructor outlines the lesson objectives and presents the sample customer spending query.
Query Execution Plans (0:30 - 0:55) — The role of execution plans in revealing costs, join algorithms, and physical operation order is introduced.
FROM and JOIN Execution (0:55 - 1:25) — Table assembly and the performance benefits of indexing join keys are detailed.
WHERE and Sargable Queries (1:25 - 2:10) — The concept of sargability is explained with examples of direct comparisons versus function-wrapped columns.
GROUP BY and HAVING (2:10 - 2:40) — Aggregation mechanics and post-aggregation filtering with the HAVING clause are examined.
SELECT and Covering Indexes (2:40 - 3:10) — Column projection order and using covering indexes to eliminate table lookups are discussed.
ORDER BY, LIMIT, and Summary (3:10 - 3:50) — Sorting optimization techniques are reviewed alongside the final recap of the full execution sequence.

SQL Cheat Sheet

Execution Order — Defines the exact logical sequence of SQL query execution

-- FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

Sargable Date Filter — Enables index seek using direct literal boundary comparison
```
SELECT id FROM orders WHERE order_date >= '2023-01-01';
```
Non-Sargable Date Filter — Prevents index seek by wrapping column in scalar function
```
SELECT id FROM orders WHERE YEAR(order_date) = 2023;
```

Covering Index — Stores all required query columns in an index structure

CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date, amount);

HAVING Clause — Filters grouped records after aggregate calculations execute
```
GROUP BY customer_id HAVING SUM(amount) >= 1000;
```
Top-N Pagination — Limits sorted result set size to reduce memory footprint
```
ORDER BY total_spent DESC LIMIT 10;
```

Comparison Table

Feature	Sargable Query	Non-Sargable Query
Index Usage	Performs fast index range seeks	Triggers full table or index scans
Column Expression	Direct column to value comparison	Function wraps the column attribute
Row Processing	Evaluates only matching index entries	Calculates function for every row
Resource Impact	Low CPU and minimal I/O	High CPU and heavy memory usage

Common Pitfalls

Mistake: Wrapping indexed columns with functions like YEAR() in WHERE clauses. Avoid: Compare bare column names directly against literal date values or ranges.
Mistake: Using HAVING to filter individual row attributes before aggregation. Avoid: Place row-level filter conditions in the WHERE clause to reduce aggregation work.
Mistake: Sorting unindexed large tables before applying LIMIT clauses. Avoid: Index sort columns to permit early termination and eliminate temporary disk spills.
Mistake: Referencing SELECT column aliases inside WHERE or GROUP BY clauses. Avoid: Repeat the original expression or nest the query within a CTE.

FAQs

Why does a function in the WHERE clause break index usage? B-tree indexes store raw column values, not calculated results. Applying a function forces the engine to compute the result for every row before comparing.
Why can I use column aliases in ORDER BY but not in WHERE? WHERE executes before SELECT when aliases do not yet exist, whereas ORDER BY executes after SELECT has assigned aliases.
What is the primary benefit of a covering index? A covering index provides all columns needed by the query, satisfying requests entirely from index pages without reading base table rows.
What information does a query execution plan provide? Execution plans display estimated resource costs, chosen join algorithms, index usage, and the physical sequence of execution steps.

Common pitfalls

Mistake: Wrapping indexed columns with functions like YEAR() in WHERE clauses. Avoid: Compare bare column names directly against literal date values or ranges.
Mistake: Using HAVING to filter individual row attributes before aggregation. Avoid: Place row-level filter conditions in the WHERE clause to reduce aggregation work.
Mistake: Sorting unindexed large tables before applying LIMIT clauses. Avoid: Index sort columns to permit early termination and eliminate temporary disk spills.
Mistake: Referencing SELECT column aliases inside WHERE or GROUP BY clauses. Avoid: Repeat the original expression or nest the query within a CTE.

In this video, we're going to deep dive into SQL query execution. We'll cover query processing, optimization techniques, and best practices. Let's get started. Here's an example query we'll use throughout this video. In this query, we find the top 10 customers with at least $1,000 spent on orders since January 1st, 2023, by joining the customers and orders table and grouping by customer ID. We display their total orders and amount spent, sorted in descending order by the total spent. Now, before we dive into the query, let's discuss query execution plans. Database systems create these plans to optimize queries and minimize resource usage. Understanding the plan can help us optimize our query for better performance. Execution plans provide information like estimated costs, chosen join algorithms, and a sequence of operations. Now first, let's start with the FROM and JOIN clauses. This is where we choose the tables we want to work with and specify how to join them. In our query, we're using the customers table and joining it with the orders table using the common id and customer_id columns. Now, using indexes on joined columns can significantly improve the performance of the join operation. Make sure we have appropriate indexes in place for faster query execution. Index types, such as B-tree and bitmap indexes, can impact performance based on data distribution and query types. Now, we move to the WHERE clause. This filters the combined data by applying a condition. In our case, we are considering orders placed on or after January 1st, 2023. Now, it's important to write sargable queries to leverage indexes effectively. Now, sargable means Search Argument Able, and it refers to queries that can use indexes for faster execution. Now, let's dive deeper into the concept of sargable queries. When we say a query is sargable, it means that the query can efficiently use indexes to speed up the execution process. Writing sargable queries is essential for optimizing database performance. Now, here's an example to help illustrate sargable versus non-sargable queries. In the sargable query, we directly compare the order_date column to a specific date. This allows the database engine to use an index on the order_date column to quickly filter out the records that meet the condition. In contrast, the non-sargable query uses the YEAR function on the order_date column. This prevents the database engine from using an index on order_date because the function must be applied to every row in the table, even if the index exists. The non-sargable query will be slower because there are a lot more records to scan. Now, to write sargable queries: One: Avoid using functions or calculations on indexed columns in the WHERE clause. Two: Use direct comparisons when possible. Do not wrap column in a function. Three: If we need to use a function on a column, create a computed column or a function-based index if the database system supports it. Next up are the GROUP BY and HAVING clauses. In our query, we're grouping the records by customer_id and filtering the groups based on the condition total_spent greater than or equal to 1,000. This query finds those who have spent a significant amount on orders. Now, the SELECT clause comes next. It defines which columns we want in our result set. In this case, we're selecting customer_id, total_orders, and total_spent. Even though SELECT comes first in a SQL query, it is pretty far down in the query processing order. Now, when optimizing SELECT clauses, consider using covering indexes that include all the columns needed for the query, especially in the SELECT, WHERE, and JOIN clauses. This enables the database engine to retrieve data directly from the index. This speeds up query execution. Covering indexes can reduce the need for additional I/O operations and minimize performance overhead. Finally, we have ORDER BY and LIMIT. To optimize the ORDER BY and LIMIT clauses, consider using a smaller result set with filtering and pagination. For large datasets, avoid sorting the entire dataset. It can lead to high memory usage and slow response times. Use appropriate indexes to speed up sorting and reduce the amount of data to be sorted in memory. And that's it! We've visualized a SQL query's execution order. We discussed optimization techniques like indexes, sargable queries, and covering indexes. Remember, the order is: FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT. Now, armed with this knowledge, we're equipped to tackle more complex SQL queries and optimize them for better performance. If you like our videos, you may like our system design newsletter as well. It covers topics and trends in large-scale system design, trusted by 300,000 readers. Subscribe at blog.bytebytego.com.

Frequently asked questions

Why does a function in the WHERE clause break index usage?

B-tree indexes store raw column values, not calculated results. Applying a function forces the engine to compute the result for every row before comparing.

Why can I use column aliases in ORDER BY but not in WHERE?

WHERE executes before SELECT when aliases do not yet exist, whereas ORDER BY executes after SELECT has assigned aliases.

What is the primary benefit of a covering index?

A covering index provides all columns needed by the query, satisfying requests entirely from index pages without reading base table rows.

What information does a query execution plan provide?

Execution plans display estimated resource costs, chosen join algorithms, index usage, and the physical sequence of execution steps.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.