Updatable views in MySQL - Time & Space Complexity

Time Complexity: Updatable views

O(n)

Understanding Time Complexity

When we update data through a view in MySQL, it is important to understand how the database processes these updates.

We want to know how the time to update grows as the data behind the view grows.

Scenario Under Consideration

Analyze the time complexity of updating data through an updatable view.

CREATE VIEW employee_view AS
SELECT id, name, salary
FROM employees
WHERE department = 'Sales';

UPDATE employee_view
SET salary = salary * 1.1
WHERE id = 101;
    

This code creates a view showing sales employees and then updates the salary of one employee through the view.

Identify Repeating Operations

Look for repeated steps when the update runs.

• Primary operation: Searching the base table for the matching row to update.
• How many times: The update targets one row, so the search depends on how the database finds that row.

How Execution Grows With Input

As the number of employees grows, the time to find the row may change.

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

Pattern observation: Without an index, the search grows linearly with the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to update through the view grows roughly in direct proportion to the number of rows in the base table.

Common Mistake

[X] Wrong: "Updating through a view is always faster because it looks simpler."

[OK] Correct: The view is just a window to the base table; the database still needs to find and update the actual data, which can take time depending on the table size.

Interview Connect

Understanding how updates through views work helps you explain database behavior clearly and shows you know how data access affects performance.

Self-Check

"What if the base table had an index on the 'id' column? How would that change the time complexity of the update through the view?"