6/27/25, 10:56 PM Query Processing Overview
Experiment 3: Join Order Optimization and Join
Algorithms
Objective:
To understand how the join order and join algorithm selection impact query performance.
Schema Used:
CREATE TABLE Customers (
cust_id INT PRIMARY KEY,
cust_name VARCHAR(100)
);
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
order_date DATE,
cust_id INT,
FOREIGN KEY (cust_id) REFERENCES Customers(cust_id)
);
CREATE TABLE OrderItems (
item_id INT PRIMARY KEY,
order_id INT,
product_name VARCHAR(100),
quantity INT,
FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);
Sample Data:
INSERT INTO Customers VALUES
(1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO Orders VALUES
(101, '2024-06-01', 1),
(102, '2024-06-02', 2),
(103, '2024-06-03', 1);
INSERT INTO OrderItems VALUES
(1001, 101, 'Pen', 10),
(1002, 101, 'Notebook', 5),
https://chatgpt.com/c/685eb5d0-f640-8012-9a65-c885ff229e7b 1/3
6/27/25, 10:56 PM Query Processing Overview
(1003, 102, 'Eraser', 3),
(1004, 103, 'Pencil', 7);
Query to Analyze Join Order:
SELECT C.cust_name, O.order_date, I.product_name
FROM Customers C
JOIN Orders O ON C.cust_id = O.cust_id
JOIN OrderItems I ON O.order_id = I.order_id;
Step-by-Step Implementation in MySQL:
Step 1: Create Database and Tables
CREATE DATABASE JoinLab;
USE JoinLab;
-- Execute the schema for Customers, Orders, and OrderItems
Step 2: Insert Sample Data
-- Use the INSERT statements shown above
Step 3: Run the Join Query
SELECT C.cust_name, O.order_date, I.product_name
FROM Customers C
JOIN Orders O ON C.cust_id = O.cust_id
JOIN OrderItems I ON O.order_id = I.order_id;
Expected Output:
https://chatgpt.com/c/685eb5d0-f640-8012-9a65-c885ff229e7b 2/3
6/27/25, 10:56 PM Query Processing Overview
+-----------+------------+--------------+
| cust_name | order_date | product_name |
+-----------+------------+--------------+
| Alice | 2024-06-01 | Pen |
| Alice | 2024-06-01 | Notebook |
| Bob | 2024-06-02 | Eraser |
| Alice | 2024-06-03 | Pencil |
+-----------+------------+--------------+
Step 4: Analyze Query Plan with EXPLAIN
EXPLAIN SELECT C.cust_name, O.order_date, I.product_name
FROM Customers C
JOIN Orders O ON C.cust_id = O.cust_id
JOIN OrderItems I ON O.order_id = I.order_id;
Sample Output Interpretation:
The plan may show join order: OrderItems -> Orders -> Customers
Join type: Nested Loop
Index usage: May depend on foreign key/index presence
Step 5: Modify Join Order and Observe Performance Although SQL doesn't enforce order of joins,
you can influence it via derived tables or optimizer hints (where supported).
Step 6: Add Indexes and Compare
CREATE INDEX idx_order_cust ON Orders(cust_id);
CREATE INDEX idx_item_order ON OrderItems(order_id);
-- Re-run EXPLAIN and observe changes
Step 7: Create Large Dataset and Compare Join Algorithms
Populate tables with thousands of rows
Observe if the optimizer switches to hash joins or merge joins (in PostgreSQL or advanced
engines)
Conclusion:
Join order and algorithm selection can significantly affect query performance.
Proper indexing helps the optimizer choose efficient plans.
Use EXPLAIN to understand and improve query execution.
https://chatgpt.com/c/685eb5d0-f640-8012-9a65-c885ff229e7b 3/3
