Why query patterns matter in SQL - Performance Analysis

Time Complexity: Why query patterns matter

O(n)

Understanding Time Complexity

When we write SQL queries, the way we write them affects how long they take to run.

We want to understand how the time to get results changes as the data grows.

Scenario Under Consideration

Analyze the time complexity of the following SQL query pattern.

SELECT *
FROM orders
WHERE customer_id = 123;

SELECT *
FROM orders
WHERE order_date > '2023-01-01';

SELECT *
FROM orders
WHERE customer_id = 123 AND order_date > '2023-01-01';
    

This shows different ways to filter data from the orders table using conditions.

Identify Repeating Operations

Look at what the database does repeatedly to find matching rows.

• Primary operation: Scanning rows to check if they meet the conditions.
• How many times: Once for each row in the orders table.

How Execution Grows With Input

As the number of orders grows, the database checks more rows.

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

Pattern observation: The work grows directly with the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows in a straight line with the number of rows.

Common Mistake

[X] Wrong: "Adding more conditions always makes the query faster."

[OK] Correct: Sometimes more conditions still require checking every row, so time grows the same.

Interview Connect

Understanding how query patterns affect time helps you write better queries and explain your choices clearly.

Self-Check

"What if we add an index on customer_id? How would the time complexity change?"