DELETE with WHERE clause in MySQL - Time & Space Complexity

Time Complexity: DELETE with WHERE clause

O(n)

Understanding Time Complexity

When deleting rows from a database table using a condition, it's important to understand how the time taken grows as the table gets bigger.

We want to know how the number of rows affects the work the database does to delete matching rows.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

DELETE FROM employees
WHERE department = 'Sales';
    

This code deletes all rows from the employees table where the department is 'Sales'.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning rows to check the department value.
• How many times: Once for each row in the employees table.

How Execution Grows With Input

As the number of rows in the table grows, the database must check more rows to find matches.

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

Pattern observation: The work grows directly with the number of rows; double the rows means double the checks.

Final Time Complexity

Time Complexity: O(n)

This means the time to delete rows grows in a straight line with the number of rows in the table.

Common Mistake

[X] Wrong: "Deleting rows with a WHERE clause always takes the same time no matter how big the table is."

[OK] Correct: The database must check each row to see if it matches the condition, so more rows mean more work.

Interview Connect

Understanding how deleting rows scales helps you explain database performance clearly and shows you know how data size affects operations.

Self-Check

"What if there was an index on the department column? How would the time complexity change?"