CODEWITHSUNDEEP
mysqldatetimequery-performance
Ashish
Ashish

Reputation: 769

Performance Difference between BEFORE Trigger And ON UPDATE CURRENT_TIMESTAMP - MySQL

So, my friend and I are having an argument over using BEFORE Trigger or ON UPDATE CURRENT_TIMESTAMP for a table to update the value of an updated_at column. The purpose of the column, as the name says is just to store the last time when a row was updated.

He has setup a trigger for the same - Event - BEFORE

  BEGIN
       set NEW.updated_at := current_timestamp();
  END

And I am of the opinion that we should use the attribute ON UPDATE CURRENT_TIMESTAMP as that's a default trigger provided by MySQL for the same but on the AFTER Event.

I tried searching the performance difference in the documentation but came across nothing. Any help?

Upvotes: 7

Views: 879

Answers (1)

O. Jones
O. Jones

Reputation: 108792

Undoubtedly the ON UPDATE CURRENT_TIMESTAMP approach is faster than a trigger (especially under heavy load) because it's simpler. You can certainly think of it as a default trigger, but it's actually built into the database's UPDATE code. The execution path in the DBMS doesn't need any special logic to handle transactions, whereas triggers do.

The timestamp update happens simultaneously with any other update; neither before nor after it. If the update is rolled back (transactionally) the timestamp change is rolled back along with the rest of the changed columns

It's also slightly easier for future programmers to understand your table, because they can just look at the column definitions, and don't have to be aware of the trigger. (But that is a matter of opinion.)

@AS7K ran a benchmark, and results are shown here. MySQL timestamp auto-update performance

But, there are more important design decisions to argue about. (Also a matter of opinion.)

Upvotes: 8

Related Questions