05. How Do Query/ Query Plan Execute (Inside MySQL Server)

 

Query/ Query Plan Execution (Inside MySQL Server)

This process happens inside the MySQL server

When you run: UPDATE employees SET salary = 50000 WHERE id = 101;

 

MySQL does the following: Step-by-Step Inside MySQL Server

Step	What Happens	Where
1	Parses and optimizes the query	MySQL SQL layer
2	Finds the row using index (if available)	InnoDB engine
3	Loads the row into buffer pool (RAM)	InnoDB memory buffer
4	Changes the value in memory (not yet on disk)	Buffer pool
5	Writes undo log entry (for rollback)	InnoDB undo tablespace
6	Writes redo log entry (for crash recovery)	InnoDB log file
7	At COMMIT, redo log is flushed to disk	Log file
8	InnoDB marks changes as committed, visible to others	Buffer pool + Transaction system
9	Actual data file on disk is written later (lazily)	InnoDB table file

 

Main steps:

1. In-memory changes in the buffer pool (RAM) : hold the modifying row with modifiying value
2. Undo logs for rollback: Hold the original row (Before updated value)
3. Redo logs for recovery: Hold the updated row (with updated value)

 

Example With a Transaction

START TRANSACTION;
UPDATE employees SET salary = 50000 WHERE id = 101; -- Still not committed. Data updated in memory only.
COMMIT;  -- Now the change is marked durable and visible.

 

Whole mechanism happens inside the MySQL server, particularly handled by the InnoDB storage engine.

 

1. In-memory changes in the buffer pool (RAM)

Copy of the changing row is hold in RAM and all the changes done to that row. Once commit then this data permanently save in table and delete that row from RAM.

 

2. Undo Log – for Rollback & Consistency

It is unique for each transaction and holds the data before any changes are made.

Purpose: To undo changes if a transaction fails or is rolled back and also used to maintain isolation, so others don't see row updates.

For example: UPDATE employees SET salary = 50000 WHERE id = 101;

Before image: salary = 45000 (stored in undo log)

 

3. Redo Log (For Crash Recovery)

• It is common (or global) for all transactions and holds the changed data.
• Purpose: To redo changes after a crash and Ensures durability of committed transactions.

What’s stored:

• The intent of the change – for example: SET salary = 50000 WHERE id = 101
• The actual physical changes to data pages –
• This includes which rows were affected and what values were updated.
• Usage: If server crashes after commit but before disk flush → redo log is used on restart to replay changes.

In MongoDB:

No Undo Logs in MongoDB and it use only in memory (RAM) to hold the document which is updating.

Summary:

Concept	MySQL	MongoDB
Undo log	Yes (per transaction)	No
Redo log / Journal	Yes (global)	Yes (journal)
Recovery from crash	Redo log	Journal replay
Rollback mechanism	Undo log	In-memory (before commit)