Creating stored procedures in MySQL - Performance & Efficiency

Time Complexity: Creating stored procedures

O(n)

Understanding Time Complexity

When we create stored procedures in MySQL, we want to know how the time it takes to run them changes as the data grows.

We ask: How does the work inside the procedure grow when there are more rows or calls?

Scenario Under Consideration

Analyze the time complexity of the following stored procedure.

CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
  SELECT * FROM orders WHERE customer_id = userId;
END;
    

This procedure fetches all orders for a given user from the orders table.

Identify Repeating Operations

Look for loops or repeated work inside the procedure.

• Primary operation: Scanning the orders table to find matching rows.
• How many times: The database checks each order row to see if it belongs to the user.

How Execution Grows With Input

As the number of orders grows, the work to find matching orders grows too.

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

Pattern observation: The work grows roughly in direct proportion to the number of orders.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the procedure grows linearly with the number of orders in the table.

Common Mistake

[X] Wrong: "Stored procedures always run instantly no matter how much data there is."

[OK] Correct: The procedure still does work on the data, so if the data grows, the time to run grows too.

Interview Connect

Understanding how stored procedures scale helps you write efficient database code and explain your reasoning clearly in interviews.

Self-Check

What if the procedure included a loop inside that called another query for each order? How would the time complexity change?