Why triggers automate responses in MySQL - Performance Analysis

Time Complexity: Why triggers automate responses

O(n)

Understanding Time Complexity

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

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

Scenario Under Consideration

Analyze the time complexity of this trigger that runs after an insert.

CREATE TRIGGER after_insert_example
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(order_id, action_time)
  VALUES (NEW.id, NOW());
END;
    

This trigger adds a record to an audit table every time a new order is inserted.

Identify Repeating Operations

Look at what repeats when this trigger runs.

• Primary operation: The trigger runs once for each inserted row.
• How many times: Exactly once per new order inserted.

How Execution Grows With Input

As you insert more rows, the trigger runs that many times.

Input Size (n)	Approx. Operations
10	10 trigger executions
100	100 trigger executions
1000	1000 trigger executions

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

Final Time Complexity

Time Complexity: O(n)

This means the trigger's work increases in a straight line as you add more rows.

Common Mistake

[X] Wrong: "The trigger runs only once no matter how many rows are inserted."

[OK] Correct: Actually, triggers run once per row, so inserting many rows means many trigger executions.

Interview Connect

Understanding how triggers scale helps you design efficient databases and shows you think about real-world data growth.

Self-Check

"What if the trigger was defined FOR EACH STATEMENT instead of FOR EACH ROW? How would the time complexity change?"