Creating stored functions in MySQL - Performance & Efficiency

Time Complexity: Creating stored functions

O(n)

Understanding Time Complexity

When we create stored functions in MySQL, it's important to understand how their execution time changes as the input grows.

We want to know how the work inside the function scales when called with different input sizes.

Scenario Under Consideration

Analyze the time complexity of the following stored function.

CREATE FUNCTION sum_to_n(n INT) RETURNS INT
BEGIN
  DECLARE total INT DEFAULT 0;
  DECLARE i INT DEFAULT 1;
  WHILE i <= n DO
    SET total = total + i;
    SET i = i + 1;
  END WHILE;
  RETURN total;
END
    

This function adds all numbers from 1 up to n and returns the total sum.

Identify Repeating Operations

Look for loops or repeated steps inside the function.

• Primary operation: The WHILE loop that adds numbers from 1 to n.
• How many times: The loop runs exactly n times, once for each number.

How Execution Grows With Input

As n grows, the function does more additions, one for each number up to n.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the time to run the function grows in a straight line with the input size n.

Common Mistake

[X] Wrong: "The function runs in constant time because it just returns a sum."

[OK] Correct: The function actually adds each number one by one, so the time depends on how big n is.

Interview Connect

Understanding how loops inside stored functions affect performance helps you write efficient database code and answer questions about scaling.

Self-Check

"What if we changed the function to use a mathematical formula instead of a loop? How would the time complexity change?"