Query optimization techniques in MySQL - Step-by-Step Execution

Concept Flow - Query optimization techniques

Write SQL Query

↓

Parse Query

↓

Generate Query Plan

↓

Apply Optimization Techniques

↓

Choose Best Execution Plan

↓

Execute Query

↓

Return Results

The database takes your SQL query, parses it, creates a plan, applies optimizations to make it faster, chooses the best plan, then runs the query and returns the results.

Execution Sample

MySQL

SELECT * FROM employees WHERE department_id = 5;
-- Using index on department_id
-- Optimized query plan chosen

This query fetches all employees in department 5. The optimizer uses an index on department_id to speed up the search.

Execution Table

Step	Action	Details	Effect
1	Parse Query	Read SQL and check syntax	Query is valid
2	Generate Query Plan	Create possible ways to run query	Multiple plans created
3	Apply Optimization	Use index on department_id	Faster data access
4	Choose Best Plan	Select plan with lowest cost	Plan with index chosen
5	Execute Query	Run chosen plan	Rows with department_id=5 fetched
6	Return Results	Send data to user	Results displayed

💡 Query executed with optimized plan using index on department_id

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Query Plan Options	None	Multiple plans generated	Plans optimized with indexes	Best plan selected	Best plan executed
Execution Cost	Unknown	Estimated for each plan	Reduced by optimization	Lowest cost chosen	Cost realized during execution

Key Moments - 3 Insights

Why does the optimizer choose to use an index instead of scanning the whole table?

Because using the index reduces the number of rows to check, making the query faster. This is shown in execution_table step 3 where optimization applies the index.

What happens if no index exists for the searched column?

The optimizer will choose a full table scan, which is slower. This is implied in execution_table step 3 where no index would mean no optimization applied.

How does the optimizer decide which plan is best?

It estimates the cost (time and resources) for each plan and picks the one with the lowest cost, as shown in execution_table step 4.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the optimizer apply the index?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Check the 'Action' and 'Details' columns in execution_table row for step 3.

According to variable_tracker, what happens to 'Execution Cost' after optimization?

A It increases

B It stays the same

C It is reduced

D It becomes unknown

💡 Hint

Look at the 'Execution Cost' row values from 'After Step 2' to 'After Step 3' in variable_tracker.

If the index on department_id was missing, which step in execution_table would change?

A Step 3

B Step 1

C Step 5

D Step 6

💡 Hint

Step 3 is where optimization applies indexes; without an index, this step would differ.

Concept Snapshot

Query Optimization Techniques:
- Database parses SQL query
- Generates multiple query plans
- Applies optimizations like indexes
- Chooses plan with lowest cost
- Executes optimized plan
- Returns results faster

Full Transcript

Query optimization is the process where the database takes your SQL query, checks it, and figures out the best way to run it quickly. It creates different plans to get the data, then uses tricks like indexes to speed up searching. The optimizer picks the fastest plan and runs it, returning your results efficiently. This process helps avoid slow full table scans and makes your queries faster.