6/27/25, 10:50 PM Query Processing Overview
Experiment 1: Analyzing Query Execution Plans using
EXPLAIN in SQL
Objective:
To understand and analyze the internal steps of query processing and optimization by using
the EXPLAIN and EXPLAIN ANALYZE features of a relational DBMS (e.g., MySQL or PostgreSQL).
Software/Tools Required:
MySQL Workbench / PostgreSQL with pgAdmin
Sample database with Employee and Department tables
Theory Background:
Query processing in DBMS involves:
1. Parsing and Translation
2. Optimization
3. Evaluation Plan Generation
4. Execution
Query Optimization aims to find the most efficient way to execute a query by using indexes, join
strategies, and access paths.
Schema Used:
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
name VARCHAR(100),
salary INT,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id),
INDEX (salary),
INDEX (dept_id)
);
https://chatgpt.com/c/685eb5d0-f640-8012-9a65-c885ff229e7b 1/4
6/27/25, 10:50 PM Query Processing Overview
Sample Data:
INSERT INTO Department VALUES
(1, 'HR'), (2, 'IT'), (3, 'Finance');
INSERT INTO Employee VALUES
(1, 'John', 60000, 2),
(2, 'Priya', 75000, 1),
(3, 'Amit', 45000, 3),
(4, 'Sara', 55000, 2),
(5, 'Neha', 35000, 1);
Query to be Analyzed:
SELECT E.name, D.dept_name
FROM Employee E, Department D
WHERE E.dept_id = D.dept_id AND E.salary > 50000;
Step-by-Step Implementation in MySQL:
Step 1: Create Database and Tables
CREATE DATABASE QueryLab;
USE QueryLab;
-- Then execute the schema code provided above
Step 2: Insert Sample Data
-- Use the insert statements as shown in the "Sample Data" section above
Step 3: Run the Query Normally
SELECT E.name, D.dept_name
FROM Employee E, Department D
WHERE E.dept_id = D.dept_id AND E.salary > 50000;
Expected Output:
+-------+------------+
| name | dept_name |
https://chatgpt.com/c/685eb5d0-f640-8012-9a65-c885ff229e7b 2/4
6/27/25, 10:50 PM Query Processing Overview
+-------+------------+
| John | IT |
| Priya | HR |
| Sara | IT |
+-------+------------+
Step 4: Use EXPLAIN to Analyze the Plan
EXPLAIN SELECT E.name, D.dept_name
FROM Employee E, Department D
WHERE E.dept_id = D.dept_id AND E.salary > 50000;
Sample Output:
id select_type table type possible_keys key rows Extra
1 SIMPLE E range salary,dept_id salary 3 Using
where;
Using
index
1 SIMPLE D eq_ref PRIMARY PRIMARY 1 Using
index
Step 5: Drop an Index and Re-run EXPLAIN
ALTER TABLE Employee DROP INDEX salary;
EXPLAIN SELECT E.name, D.dept_name
FROM Employee E, Department D
WHERE E.dept_id = D.dept_id AND E.salary > 50000;
Observe Changes: The type may change to ALL and performance may degrade due to full table
scan.
Step 6: Recreate the Index and Compare
CREATE INDEX salary ON Employee(salary);
-- Rerun EXPLAIN to confirm improvement
Conclusion:
Using EXPLAIN helps understand the query execution strategy.
Indexes improve performance by reducing row scans.
https://chatgpt.com/c/685eb5d0-f640-8012-9a65-c885ff229e7b 3/4
6/27/25, 10:50 PM Query Processing Overview
Query plans change based on available indexes and query structure.
https://chatgpt.com/c/685eb5d0-f640-8012-9a65-c885ff229e7b 4/4
