ajax1515 ajax1515 - 4 months ago 24
MySQL Question

MySQL Trigger Performance Degredation (To use or not to use)

So I've created this trigger:

DELIMITER $$
CREATE TRIGGER `increment_daily_called_count` BEFORE UPDATE ON `list`
FOR EACH ROW begin
if (NEW.called_count != OLD.called_count) then
set NEW.daily_called_count = OLD.daily_called_count(NEW.called_count-OLD.called_count);
set NEW.modify_date = OLD.modify_date;
end if;
end
$$
DELIMITER ;


The database table this runs on is accessed and used by 100's of different scripts in the larger system and the reason for the trigger is so I don't have to hunt down every little place in these scripts where the called_count might get updated...

My concern is that, because this particular table gets modified constantly (I'm talking dozens of times per second), is this going to put undue strain on the database? Am I better off in the long run hunting down all the called_count update queries in the myriad scripts and adding daily_called_count = daily_called_count+1 ?

Some specifics I'd like to know the answer to here:


  • Does use of this trigger essentially make this 3 separate update queries where it was once a single query, or is mysql smart enough to bundle these queries?

  • Is there a performance argument for hunting down and modifying the originating queries over using the trigger?

  • Could this trigger cause any unforeseen weirdness that I'm not anticipating?



Thanks in advance for your help.

Answer

First, thanks to @Brandon for his response. I built my own script and test database to benchmark and solve my question... While I don't have a good answer to points 1 and 3, I do have an answer on the performance question...

To note I am using 10.0.24-MariaDB on our development server which didn't have anything else running on it at the time.

Here are my results...

Updating 100000 rows:

TRIGGER QUERY TIME: 6.85960197 SECONDS
STANDARD QUERY TIME: 5.90444183 SECONDS

Updating 200000 rows:

TRIGGER QUERY TIME: 13.19935203 SECONDS
STANDARD QUERY TIME: 11.88235188 SECONDS

You folks can decide for yourselves which way to go.