Why DML operations modify data in MySQL - Performance Analysis

Time Complexity: Why DML operations modify data

O(n)

Understanding Time Complexity

When we use commands that change data in a database, like adding or updating rows, it takes some time to do that work.

We want to understand how the time needed grows as we change more data.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

INSERT INTO employees (name, position) VALUES ('Alice', 'Manager');
UPDATE employees SET position = 'Senior Manager' WHERE name = 'Alice';
DELETE FROM employees WHERE name = 'Alice';
    

This code adds a new employee, updates their position, then removes them from the table.

Identify Repeating Operations

• Primary operation: Searching rows to find where to insert, update, or delete.
• How many times: Depends on how many rows the database checks to find the right place.

How Execution Grows With Input

As the table gets bigger, the database may need to check more rows to find where to change data.

Input Size (n)	Approx. Operations
10	About 10 checks
100	About 100 checks
1000	About 1000 checks

Pattern observation: The work grows roughly in direct proportion to the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to modify data grows roughly in step with the number of rows in the table.

Common Mistake

[X] Wrong: "Modifying one row always takes the same time no matter how big the table is."

[OK] Correct: The database often needs to look through many rows to find the right one, so bigger tables usually take more time.

Interview Connect

Understanding how data changes take longer with bigger tables helps you explain how databases work efficiently in real projects.

Self-Check

"What if we add an index on the column used in the WHERE clause? How would the time complexity change?"