Why DELETE needs caution in SQL - Performance Analysis

Time Complexity: Why DELETE needs caution

O(n)

Understanding Time Complexity

When we delete data from a database, it can take different amounts of time depending on how much data we remove.

We want to understand how the time to delete grows as the amount of data increases.

Scenario Under Consideration

Analyze the time complexity of the following SQL DELETE statement.

DELETE FROM Orders
WHERE OrderDate < '2023-01-01';

This code deletes all orders made before January 1, 2023.

Identify Repeating Operations

Look at what repeats when this DELETE runs.

• Primary operation: Checking each row to see if it matches the date condition.
• How many times: Once for every row in the Orders table.

How Execution Grows With Input

As the number of rows grows, the time to check and delete grows too.

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

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 delete grows linearly with the number of rows checked.

Common Mistake

[X] Wrong: "Deleting a few rows is always very fast no matter the table size."

[OK] Correct: Even if few rows are deleted, the database often checks many rows to find them, so time depends on table size.

Interview Connect

Understanding how delete operations scale helps you write better queries and avoid surprises in real projects.

Self-Check

"What if we add an index on OrderDate? How would that change the time complexity of the DELETE?"