Trigger best practices and limitations in MySQL - Time & Space Complexity

Time Complexity: Trigger best practices and limitations

O(n)

Understanding Time Complexity

When using triggers in MySQL, it's important to understand how their execution time grows as the data changes.

We want to know how triggers affect the speed of database operations as more rows are involved.

Scenario Under Consideration

Analyze the time complexity of this trigger example.

CREATE TRIGGER update_stock AFTER INSERT ON sales
FOR EACH ROW
BEGIN
  UPDATE products
  SET stock = stock - NEW.quantity
  WHERE product_id = NEW.product_id;
END;
    

This trigger updates the stock count in the products table every time a new sale is inserted.

Identify Repeating Operations

Look at what repeats when the trigger runs.

• Primary operation: The UPDATE statement runs once for each inserted sale row.
• How many times: Once per inserted row, so if multiple rows are inserted, it runs that many times.

How Execution Grows With Input

As the number of inserted rows grows, the trigger runs more times.

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

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

Final Time Complexity

Time Complexity: O(n)

This means the time to complete grows in a straight line as more rows are inserted.

Common Mistake

[X] Wrong: "Triggers run once no matter how many rows are inserted."

[OK] Correct: In MySQL, triggers run once per row, so inserting many rows means the trigger runs many times, increasing total work.

Interview Connect

Understanding how triggers scale helps you design efficient databases and avoid slowdowns as data grows.

Self-Check

What if the trigger was changed to run AFTER UPDATE instead of AFTER INSERT? How would that affect the time complexity?