I have a table. Lets call it table tableA. There's another table which keeps data about the history of tableA. Let's call that tableA_records.
On tableA, there's a trigger on update which looks similar to this:
CREATE TRIGGER upd_tableA AFTER UPDATE ON tableA
FOR EACH ROW
if @disable_triggers is null then
BEGIN
SET @now = unix_timestamp(now());
UPDATE tableA_records
SET valid_to = @now
WHERE valid_to IS NULL AND tableA_id = old.id;
INSERT INTO tableA_records (
tableA_id, column2, column3, ...., column27,
valid_from, valid_to
) VALUES (
new.id, new.column2, new.column3, ...., new.column27,
@now, NULL
);
END;
end if
The issue I'm having is that the trigger slows down updates a lot. Without the trigger, an update on 100 rows of tableA takes 0.2 seconds. Inserting those 100 rows on tableA_records takes 0.25 seconds. The combined actions of the trigger (update tableA_records, insert into tableA_records) and the triggering action (update tableA) should take 0.65 seconds if we sum the times for each. With the trigger, it takes 35 seconds.
Anyone have an idea of what's causing the slowdown?
(tableA_id, valid_to)
indexed?