Variables and control flow in MySQL - Time & Space Complexity

Time Complexity: Variables and control flow

O(n)

Understanding Time Complexity

When using variables and control flow in MySQL, it's important to see how the steps grow as data grows.

We want to know how the number of operations changes when the input size changes.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

DECLARE done INT DEFAULT FALSE;
DECLARE counter INT DEFAULT 0;
DECLARE id_val INT;
DECLARE cur CURSOR FOR SELECT id FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
  FETCH cur INTO id_val;
  IF done THEN
    LEAVE read_loop;
  END IF;
  SET counter = counter + 1;
END LOOP;
CLOSE cur;
    

This code uses a cursor to loop through all user IDs, incrementing a counter for each row, thereby counting how many rows it processes.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Looping through each row returned by the cursor.
• How many times: Once for each row in the users table.

How Execution Grows With Input

As the number of rows in the users table grows, the loop runs more times.

Input Size (n)	Approx. Operations
10	About 10 loop steps
100	About 100 loop steps
1000	About 1000 loop steps

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

Final Time Complexity

Time Complexity: O(n)

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

Common Mistake

[X] Wrong: "Using variables and loops in MySQL always runs in constant time regardless of data size."

[OK] Correct: The loop runs once per row, so more rows mean more steps, not a fixed number.

Interview Connect

Understanding how loops and variables affect query time helps you explain how your code scales with data size.

Self-Check

"What if we replaced the cursor loop with a single SQL aggregate function? How would the time complexity change?"