SQL Query Execution Order - FROM to LIMIT

▶

Steps

setup

Start Execution: FROM Clause

The query execution begins by scanning the 'employees' table to load all rows into the pipeline.

💡 FROM is always the first step because it defines the data source for the query.

Line:

from_clause.execute()

💡 Understanding that FROM loads the base data is fundamental to grasping SQL execution.

filter

Apply WHERE Filter

Filter rows to keep only those where department equals 'Sales'.

💡 WHERE reduces the data early to improve efficiency of later steps.

Line:

where_clause.execute()

💡 Filtering early avoids unnecessary processing on irrelevant rows.

fill_row

Group Rows by Name

Group the filtered rows by the 'name' column and count the number of rows per group.

💡 GROUP BY aggregates data, enabling summary calculations like COUNT.

Line:

group_by_clause.execute()

💡 Grouping organizes data for aggregate functions.

filter

Apply HAVING Filter on Groups

Filter groups to keep only those with COUNT(*) greater than 1.

💡 HAVING filters aggregated groups, unlike WHERE which filters rows.

Line:

having_clause.execute()

💡 HAVING applies conditions after aggregation.

insert

Project Selected Columns

Select only the 'name' and the count columns to form the final output columns.

💡 SELECT chooses which columns to output after filtering and grouping.

Line:

select_clause.execute()

💡 Projection shapes the final result structure.

compare

Start Sorting Results by Name

Begin sorting the projected rows alphabetically by the 'name' column.

💡 ORDER BY arranges the output rows in the requested order.

Line:

order_by_clause.execute()

💡 Sorting is done after projection to optimize performance.

compare

Complete Sorting Results by Name

Sorting of the projected rows by 'name' is completed.

💡 ORDER BY ensures the output rows are in the correct order before limiting.

Line:

order_by_clause.execute() completes

💡 Sorting must finish before applying LIMIT to ensure correct rows are returned.

shrink

Start Applying LIMIT to Output Rows

Begin limiting the output to the first 5 rows after sorting.

💡 LIMIT restricts the number of rows returned to the user.

Line:

limit_clause.execute()

💡 LIMIT is applied last to the fully processed and ordered data.

shrink

Complete Applying LIMIT to Output Rows

Limiting of the output rows to 5 is completed.

💡 LIMIT finalizes the number of rows returned to the user.

Line:

limit_clause.execute() completes

💡 LIMIT ensures the query returns only the requested number of rows.

reconstruct

Complete Execution

All clauses have executed in order, producing the final limited, sorted, grouped, and filtered result set.

💡 The query plan execution is complete and the final output is ready to be returned.

Line:

from_clause.execute() completes

💡 The sequential execution of clauses respects SQL logical order.

class QueryPlanNode:
    def __init__(self, name):
        self.name = name
        self.next = None
    def set_next(self, node):
        self.next = node
    def execute(self):
        print(f"Executing {self.name} clause")  # STEP N
        if self.next:
            self.next.execute()

from_clause = QueryPlanNode('FROM')  # STEP 1
where_clause = QueryPlanNode('WHERE')  # STEP 2
group_by_clause = QueryPlanNode('GROUP BY')  # STEP 3
having_clause = QueryPlanNode('HAVING')  # STEP 4
select_clause = QueryPlanNode('SELECT')  # STEP 5
order_by_clause = QueryPlanNode('ORDER BY')  # STEP 6
limit_clause = QueryPlanNode('LIMIT')  # STEP 7

from_clause.set_next(where_clause)  # STEP 1
where_clause.set_next(group_by_clause)  # STEP 2
group_by_clause.set_next(having_clause)  # STEP 3
having_clause.set_next(select_clause)  # STEP 4
select_clause.set_next(order_by_clause)  # STEP 5
order_by_clause.set_next(limit_clause)  # STEP 6

from_clause.execute()  # STEP 1 starts execution

📊

SQL Query Execution Order - FROM to LIMIT - Watch the Algorithm Execute, Step by Step

Watching the query plan execute step-by-step reveals the true order of operations in SQL, which is often misunderstood when reading the query text.

Scanning 'employees' table to load all rows (FROM clause).

SeqScanemployeesrows≈1000

Filterdepartment = 'Sales'

AggregateGROUP BY name

FilterHAVING COUNT(*) > 1

ProjectionSELECT name, COUNT(*)

SortORDER BY name

LimitLIMIT 5

Rows processed: 1000

SeqScan chosen as no index is specified.

Filtering rows where department = 'Sales' (WHERE clause).

SeqScanemployeesrows≈1000

Filterdepartment = 'Sales'rows≈200

AggregateGROUP BY name

FilterHAVING COUNT(*) > 1

ProjectionSELECT name, COUNT(*)

SortORDER BY name

LimitLIMIT 5

Rows processed: 200

Filtering early reduces rows for aggregation.

Grouping rows by 'name' and counting (GROUP BY clause).

SeqScanemployeesrows≈1000

Filterdepartment = 'Sales'rows≈200

AggregateGROUP BY namerows≈50

FilterHAVING COUNT(*) > 1

ProjectionSELECT name, COUNT(*)

SortORDER BY name

LimitLIMIT 5

Rows processed: 50

Aggregation reduces rows to groups.

Filtering groups with COUNT(*) > 1 (HAVING clause).

SeqScanemployeesrows≈1000

Filterdepartment = 'Sales'rows≈200

AggregateGROUP BY namerows≈50

FilterHAVING COUNT(*) > 1rows≈20

ProjectionSELECT name, COUNT(*)

SortORDER BY name

LimitLIMIT 5

Rows processed: 20

Filtering groups reduces result size before projection.

Selecting columns 'name' and COUNT(*) (SELECT clause).

SeqScanemployeesrows≈1000

Filterdepartment = 'Sales'rows≈200

AggregateGROUP BY namerows≈50

FilterHAVING COUNT(*) > 1rows≈20

ProjectionSELECT name, COUNT(*)rows≈20

SortORDER BY name

LimitLIMIT 5

Rows processed: 20

Projection reduces data width for sorting.

Starting to sort rows by 'name' (ORDER BY clause).

SeqScanemployeesrows≈1000

Filterdepartment = 'Sales'rows≈200

AggregateGROUP BY namerows≈50

FilterHAVING COUNT(*) > 1rows≈20

ProjectionSELECT name, COUNT(*)rows≈20

SortORDER BY namerows≈20

LimitLIMIT 5

Rows processed: 20

Sorting after projection reduces data size to sort.

Sorting rows by 'name' completed (ORDER BY clause).

SeqScanemployeesrows≈1000

Filterdepartment = 'Sales'rows≈200

AggregateGROUP BY namerows≈50

FilterHAVING COUNT(*) > 1rows≈20

ProjectionSELECT name, COUNT(*)rows≈20

SortORDER BY namerows≈20

LimitLIMIT 5

Rows processed: 20

Sorting after projection reduces data size to sort.

Starting to limit output to 5 rows (LIMIT clause).

SeqScanemployeesrows≈1000

Filterdepartment = 'Sales'rows≈200

AggregateGROUP BY namerows≈50

FilterHAVING COUNT(*) > 1rows≈20

ProjectionSELECT name, COUNT(*)rows≈20

SortORDER BY namerows≈20

LimitLIMIT 5rows≈5

Rows processed: 5

LIMIT applied last to minimize output size.

Limiting output to 5 rows completed (LIMIT clause).

SeqScanemployeesrows≈1000

Filterdepartment = 'Sales'rows≈200

AggregateGROUP BY namerows≈50

FilterHAVING COUNT(*) > 1rows≈20

ProjectionSELECT name, COUNT(*)rows≈20

SortORDER BY namerows≈20

LimitLIMIT 5rows≈5

Rows processed: 5

LIMIT applied last to minimize output size.

Query execution complete; final result ready.

SeqScanemployeesrows≈1000

Filterdepartment = 'Sales'rows≈200

AggregateGROUP BY namerows≈50

FilterHAVING COUNT(*) > 1rows≈20

ProjectionSELECT name, COUNT(*)rows≈20

SortORDER BY namerows≈20

LimitLIMIT 5rows≈5

Rows processed: 5

Execution order ensures correctness and efficiency.

Key Takeaways

✓ SQL query execution order differs from the written query order; it always starts with FROM.

Reading SQL text can mislead about execution order; visualization clarifies the actual flow.

✓ Filtering early with WHERE reduces data volume, improving efficiency for later aggregation and sorting.

Seeing the filter operator active early shows why WHERE is crucial for performance.

✓ HAVING filters groups after aggregation, a concept distinct from WHERE filtering rows.

Visualizing HAVING after GROUP BY helps distinguish these two filtering stages.

Practice

1. When executing a range query on a B+ tree index, what sequence of steps occurs internally to retrieve all matching records?

easy

A. Perform a full table scan because B+ trees do not support range queries efficiently.

B. Hash the range boundaries to find corresponding buckets, then scan all buckets between them.

C. Traverse the tree to the root, then scan all leaf nodes in order regardless of the range.

D. Locate the first key in the range via tree traversal, then follow leaf node pointers sequentially until the range end is reached.

Solution

1. Step 1: Locate start key

   B+ trees use a balanced tree traversal to find the leaf node containing the first key in the range.

2. Step 2: Sequential leaf traversal

   Leaf nodes are linked, allowing sequential scanning of keys in order until the range end is reached.

3. Step 3: Evaluate distractors

   Hash the range boundaries to find corresponding buckets, then scan all buckets between them. incorrectly applies hashing to range queries. Traverse the tree to the root, then scan all leaf nodes in order regardless of the range. suggests scanning all leaves, which is inefficient. Perform a full table scan because B+ trees do not support range queries efficiently. falsely claims B+ trees do not support range queries.

4. Final Answer:

   Option D -> Option D

5. Quick Check:

   Range query -> locate start + sequential leaf scan [OK]

Hint: B+ tree range query = find start + linked leaf scan

Common Mistakes:

• Thinking hash indexes can handle range queries by scanning buckets
• Assuming full leaf scan is needed for range queries
• Believing B+ trees do not support range queries

2. In which scenario is the Two-Phase Locking (2PL) protocol most appropriate to ensure serializability in a database system?

easy

A. When transactions require only read operations and no writes

B. When transactions involve both reads and writes and strict serializability is required

C. When transactions are short-lived and deadlocks are impossible

D. When optimistic concurrency control is preferred over locking

Solution

1. Step 1: Understand 2PL's purpose

   2PL ensures serializability by acquiring locks in a growing phase and releasing them in a shrinking phase, which is essential when transactions perform both reads and writes.

2. Step 2: Analyze each option

   A is incorrect because 2PL is not necessary if only reads occur; shared locks suffice without strict 2PL. B is unrelated as optimistic concurrency control avoids locking. C is wrong since deadlocks can occur regardless of transaction length. D correctly identifies the scenario where 2PL is most appropriate.

3. Final Answer:

   Option B -> Option B

4. Quick Check:

   2PL is designed for mixed read-write transactions requiring strict serializability.

Hint: 2PL is for mixed read-write transactions needing serializability [OK]

Common Mistakes:

• Assuming 2PL is needed for read-only transactions
• Believing short transactions cannot deadlock
• Confusing locking with optimistic concurrency

3. Why might using stored procedures for all business logic in a database be a poor architectural choice?

medium

A. Stored procedures always improve performance regardless of complexity.

B. Stored procedures can lead to tight coupling between database and application logic, reducing flexibility.

C. Stored procedures cannot be version controlled.

D. Stored procedures do not support transaction management.

Solution

1. Step 1: Analyze architectural implications

   Embedding all business logic in stored procedures couples application logic tightly to the database schema and vendor, reducing portability and flexibility.

2. Step 2: Evaluate other options

   Stored procedures can be version controlled and support transactions; performance gains depend on use case.

3. Final Answer:

   Option B -> Option B

4. Quick Check:

   Recognize trade-offs between maintainability and performance.

Hint: Stored procedures can hurt flexibility by mixing logic with data storage.

Common Mistakes:

• Assuming stored procedures always improve performance
• Believing stored procedures cannot be version controlled
• Thinking stored procedures lack transaction support

4. In a system using MVCC with snapshot isolation, what happens if two concurrent transactions T1 and T2 both update the same row and then try to commit? How does MVCC handle this conflict?

hard

A. Both transactions commit successfully, and the last commit overwrites the other

B. MVCC merges the changes from both transactions automatically

C. Both transactions are blocked until one finishes, then the other proceeds

D. One transaction commits, and the other aborts due to a write-write conflict detected at commit time

Solution

1. Step 1: Understand write-write conflicts in MVCC

   MVCC allows concurrent reads but detects write conflicts at commit time.

2. Step 2: Analyze last-commit-wins scenario

   Incorrect because MVCC prevents lost updates by aborting one conflicting transaction.

3. Step 3: Analyze conflict resolution

   Correct; one transaction commits, the other aborts to maintain consistency.

4. Step 4: Analyze blocking behavior

   MVCC avoids blocking; conflicts are detected at commit, not by blocking.

5. Step 5: Analyze automatic merging

   Automatic merging of conflicting writes does not occur.

6. Final Answer:

   Option D -> Option D

7. Quick Check:

   MVCC detects write conflicts at commit and aborts one transaction [OK]

Hint: MVCC aborts one writer on conflict, no blocking or merging

Common Mistakes:

• Assuming last commit wins silently
• Thinking MVCC blocks writers on conflict
• Believing MVCC merges conflicting writes

5. Your system uses sharding with a hash-based shard key. Suddenly, one shard becomes a hotspot due to a sudden surge in traffic for a specific key range. What advanced strategy can you apply to mitigate this issue without redesigning the entire shard key?

hard

A. Switch to vertical partitioning to isolate the hotspot columns.

B. Implement consistent hashing with virtual nodes to better distribute load across shards.

C. Convert asynchronous replication to synchronous replication to reduce latency.

D. Increase the replication factor of the hotspot shard to spread reads.

Solution

1. Step 1: Identify the hotspot cause

   A surge in traffic for a specific key range causes uneven load on one shard.

2. Step 2: Evaluate vertical partitioning

   Vertical partitioning splits columns, not rows, so it won't address row-based hotspots.

3. Step 3: Consider consistent hashing with virtual nodes

   This technique allows finer-grained distribution of keys across shards, reducing hotspots without changing the shard key.

4. Step 4: Assess replication changes

   Increasing replication factor helps read scalability but does not reduce write hotspots; synchronous replication affects consistency, not load distribution.

5. Final Answer:

   Option B -> Option B

6. Quick Check:

   Consistent hashing with virtual nodes mitigates hotspots by spreading keys more evenly.

Hint: Virtual nodes in consistent hashing smooth out hotspots [OK]

Common Mistakes:

• Confusing vertical partitioning with horizontal load balancing
• Thinking replication reduces write hotspots
• Assuming replication mode affects load distribution