Scribd
Upload
3 views4 pages

MySQL Example For Query Processor

The document outlines the process of how a query is processed in MySQL, detailing each step from creating a database and table to inserting data and executing a query. It explains the roles of the DDL Interpreter, DML Compiler, Query Optimizer, and Query Evaluation Engine in handling SQL commands. Additionally, it highlights the importance of query optimization and provides an example of how to demonstrate the optimizer's function in MySQL.

Uploaded by

Eren Albayrak
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views4 pages

MySQL Example For Query Processor

The document outlines the process of how a query is processed in MySQL, detailing each step from creating a database and table to inserting data and executing a query. It explains the roles of the DDL Interpreter, DML Compiler, Query Optimizer, and Query Evaluation Engine in handling SQL commands. Additionally, it highlights the importance of query optimization and provides an example of how to demonstrate the optimizer's function in MySQL.

Uploaded by

Eren Albayrak
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

MySQL Example for Query Processor

This example shows how a query passes through the DDL Interpreter, DML Compiler,
Query Optimizer, and Query Evaluation Engine.

1. Step 1 – Create a Database (DDL)

This command is processed by the DDL Interpreter.

CREATE DATABASE university;

Select the database:

USE university;

What happens inside the DBMS:

1. The command is analyzed.

2. The system catalog (data dictionary) is updated.

3. The database structure is created.

2. Step 2 – Create a Table (DDL)

CREATE TABLE Students (


StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Department VARCHAR(30)
);

Internal process:

• The DDL Interpreter checks the syntax.

• The metadata is stored in the system catalog.

• Storage structures are created on disk.

3. Step 3 – Insert Data (DML)

Now we add records.

INSERT INTO Students VALUES


(1,'Ayse',20,'Computer'),
(2,'Ali',22,'Electrical'),
(3,'Mehmet',23,'Computer'),
(4,'Zeynep',19,'Mathematics');

This command is handled by the DML Compiler.

The DBMS:

1. Parses the query.

2. Translates it into an internal representation.

3. Stores the data in the database files.

4. Step 4 – Execute a Query

User query:

SELECT Name
FROM Students
WHERE Age > 21;

This query is sent to the Query Processor.

5. What Happens Inside the Query Processor

1. Parsing

The DBMS checks:

• SQL syntax

• table names

• column names

If something is wrong, the DBMS returns an error.

2. Query Translation

The SQL query is converted into a relational algebra expression.

Example:

π_Name (σ_Age > 21 (Students))

Meaning:

• Select rows where Age > 21


• Project the Name column

3. Query Optimization

The Query Optimizer chooses the most efficient execution plan.

Possible methods:

Method 1

Full Table Scan

Method 2

Index Scan

The optimizer estimates the cost of each method and selects the fastest one.

6. Showing the Optimizer in MySQL

You can demonstrate this in class using:

EXPLAIN SELECT Name


FROM Students
WHERE Age > 21;

This command shows:

• which table is accessed

• whether an index is used

• how many rows are scanned

This is the query execution plan.

7. Query Execution (Query Evaluation Engine)

The Query Evaluation Engine executes the chosen plan.

Steps:

1. Scan the Students table


2. Apply condition Age > 21
3. Retrieve the Name column
4. Return the result

Result:
Name

Ali

Mehmet

8. Query Processing Flow

User Query
↓
Parser
↓
DML Compiler
↓
Query Optimizer
↓
Execution Plan
↓
Query Evaluation Engine
↓
Result

You might also like