We have one problem with two tables. Here is a example ER diagram of the both
We have a parent row TableA and many child rows in TableB. Both tables are bigger than this, but to resume the example, I think this is a good example.
TableB has two fields field1 and field2. When we insert in TableB, there is a before insert trigger which runs this query
UPDATE TableA SET updated = NOW() WHERE id = NEW.id_b;
Then a before update trigger on TableA is triggered, which amongst other things it calculates the sum of the field1 and field2 of the child rows of TableA on the columns calc_field1 and calc_field2 respectively. Because this trigger perfoms lots of things is very slow.
In a process where we sequentially insert rows in TableB we added column notrigger that when set to 1,so it doesnt't runs the update on TableA because we are inserting a bunch of them. We change the TableB before insert trigger to this:
IF NEW.notrigger = 0 THEN
UPDATE TableA SET updated = NOW() WHERE id = NEW.id_b;
END IF;
Then when we finish the process we manually make the update to perform the calculations.
UPDATE TableA SET updated = NOW() WHERE id = 'X';
The problem here is that when we insert with notrigger = 1 it takes the same amount of time as if the TableA trigger is run, but that trigger isn't triggered because the condition isn't met. If we remove the if, the TableB trigger runs fast,as it should. If we change the if to something like this:
IF NEW.notrigger = 0 THEN
SET var = 10;
END IF;
It also runs fast, but as we add an update to TableA, the times ramps up a lot.
We are working on combining the INSERT into one massive INSERT, but we would like to know why it takes the same amount of time when performing the update inside an if.
