MIN and MAX functions in MySQL - Time & Space Complexity

Time Complexity: MIN and MAX functions

O(n)

Understanding Time Complexity

When using MIN and MAX functions in SQL, it's important to know how the time to find these values changes as the data grows.

We want to understand how the work done increases when the number of rows gets bigger.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT MIN(price) AS LowestPrice, MAX(price) AS HighestPrice
FROM products;
    

This query finds the smallest and largest price values from the products table.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Scanning each row in the products table once to compare prices.
• How many times: Once per row, so as many times as there are rows.

How Execution Grows With Input

As the number of rows grows, the database checks each price once to find the minimum and maximum.

Input Size (n)	Approx. Operations
10	10 comparisons
100	100 comparisons
1000	1000 comparisons

Pattern observation: The work grows directly with the number of rows; double the rows means double the checks.

Final Time Complexity

Time Complexity: O(n)

This means the time to find MIN and MAX grows in a straight line with the number of rows.

Common Mistake

[X] Wrong: "MIN and MAX run instantly no matter how big the table is because they just pick one value."

[OK] Correct: The database must look at every row to be sure which value is smallest or largest, so more rows mean more work.

Interview Connect

Understanding how MIN and MAX scale helps you explain query performance clearly and shows you know how databases handle data behind the scenes.

Self-Check

"What if the products table had an index on the price column? How would the time complexity change?"