Time Complexity: Soft delete pattern concept
O(n)
0
0
Soft delete pattern concept in SQL - Time & Space Complexity
Understanding Time Complexity
We want to understand how the time to find active records changes as the data grows when using soft delete.
How does marking records as deleted affect query speed?
Scenario Under Consideration
Analyze the time complexity of the following SQL query using soft delete.
SELECT *
FROM users
WHERE deleted_at IS NULL;
This query selects all users who are not marked as deleted by checking if the deleted_at column is null.
Identify Repeating Operations
Look at what repeats when the query runs.
- Primary operation: Scanning the users table rows to check the deleted_at column.
- How many times: Once for each row in the users table.
How Execution Grows With Input
As the number of users grows, the query checks more rows.
| Input Size (n) | Approx. Operations |
|---|---|
| 10 | 10 checks of deleted_at |
| 100 | 100 checks of deleted_at |
| 1000 | 1000 checks of deleted_at |
Pattern observation: The number of checks grows directly with the number of rows.
Final Time Complexity
Time Complexity: O(n)
This means the query time grows in a straight line as the table gets bigger.
Common Mistake
[X] Wrong: "Soft delete makes queries faster because deleted rows are ignored."
[OK] Correct: The query still checks every row to see if it is deleted or not, so it does not skip work automatically.
Interview Connect
Understanding how soft delete affects query time helps you explain real database design choices clearly and confidently.
Self-Check
"What if we add an index on the deleted_at column? How would the time complexity change?"