GREATEST and LEAST in MySQL - Time & Space Complexity

Time Complexity: GREATEST and LEAST

O(n)

Understanding Time Complexity

We want to understand how the time it takes to find the greatest or least value changes as we compare more values.

How does the number of values affect the work done by GREATEST and LEAST functions?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT GREATEST(score1, score2, score3, score4, score5) AS max_score,
       LEAST(score1, score2, score3, score4, score5) AS min_score
FROM player_scores;
    

This code finds the highest and lowest scores among five columns for each player.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Comparing each value to find the greatest and least.
• How many times: Each value is compared once per function.

How Execution Grows With Input

As the number of values to compare grows, the number of comparisons grows roughly in a straight line.

Input Size (n)	Approx. Operations
5	About 4 comparisons
10	About 9 comparisons
100	About 99 comparisons

Pattern observation: The work grows steadily as you add more values to compare.

Final Time Complexity

Time Complexity: O(n)

This means the time to find the greatest or least value grows in direct proportion to how many values you compare.

Common Mistake

[X] Wrong: "GREATEST and LEAST instantly find the answer no matter how many values there are."

[OK] Correct: They must check each value to be sure which is biggest or smallest, so more values mean more work.

Interview Connect

Understanding how simple functions like GREATEST and LEAST scale helps you explain efficiency clearly and shows you think about how queries perform as data grows.

Self-Check

"What if we used GREATEST on values from a subquery that returns many rows? How would that affect the time complexity?"