View limitations in MySQL - Time & Space Complexity

Time Complexity: View limitations

O(n)

Understanding Time Complexity

When using views in MySQL, it's important to understand how their performance changes as data grows.

We want to see how the time to get results from a view changes when the underlying data gets bigger.

Scenario Under Consideration

Analyze the time complexity of the following view query.

CREATE VIEW recent_orders AS
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

SELECT * FROM recent_orders;
    

This view selects orders from the last 30 days and then retrieves all those recent orders.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning the orders table to find rows matching the date condition.
• How many times: Once per query execution, scanning all rows or using an index to filter.

How Execution Grows With Input

As the orders table grows, the time to filter recent orders grows roughly in proportion to the number of rows scanned.

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

Pattern observation: The work grows roughly in a straight line as the table gets bigger.

Final Time Complexity

Time Complexity: O(n)

This means the time to get results grows directly with the number of rows in the orders table.

Common Mistake

[X] Wrong: "Views store data separately, so querying a view is always fast regardless of table size."

[OK] Correct: Views do not store data themselves; they run the underlying query each time, so performance depends on the base tables.

Interview Connect

Understanding how views work and their time cost helps you explain database design choices clearly and confidently.

Self-Check

"What if the view included a join with another large table? How would that affect the time complexity?"