IF function in MySQL - Time & Space Complexity

Time Complexity: IF function

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run a query with the IF function changes as the amount of data grows.

Specifically, does using IF slow down the query when there are more rows?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT id, name,
       IF(score >= 60, 'Pass', 'Fail') AS result
FROM students;
    

This query checks each student's score and labels them as 'Pass' or 'Fail'.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The IF function is applied once for each row in the students table.
• How many times: Exactly once per row, so if there are n rows, the IF runs n times.

How Execution Grows With Input

As the number of rows increases, the total IF checks increase at the same rate.

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

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

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: "The IF function runs only once regardless of rows."

[OK] Correct: The IF function is evaluated for each row separately, so it runs as many times as there are rows.

Interview Connect

Understanding how simple functions like IF scale with data size helps you write efficient queries and explain their performance clearly.

Self-Check

What if we replaced the IF function with a CASE statement that has multiple conditions? How would the time complexity change?