Does a TRIGGER improve the performance?

Question

I understand that TRIGGERs are useful, as they take care of subsequent queries, but do they have an advantage from performance point of view too?

Replacing two or three queries as

INSERT INTO table1 ...
UPDATE table2 ...
UPDATE table3 ...

with a trigger-based query as

INSERT INTO table 1

with trigger

CREATE TRIGGER test
AFTER INSERT ON table1
FOR EACH ROW BEGIN
UPDATE table2 ... WHERE id=NEW.id;
UPDATE table3 ... WHERE id=NEW.id;
END

1. Do we still have the same three queries with identical performance? I mean does it matter if we perform the subsequent queries or the TRIGGER does?

2. What if we use an API (like conducting queries through PHP). The subsequent queries are internal with no need to connector/driver. Does the TRIGGER improve the performance?

Additional Information: The database is mysql 5.5 with innoDB.

Answer 1

I assume table1, table2 and table3 are also InnoDB tables.

Because of the autocommit every insert / update will be written to disk (data / index tree update), what will be slow and you can't use BEGIN TRANSACTION and COMMIT to speed it up.

If table1, table2 and table3 are not InnoDB, but MyISAM (table locking) or Memory (table locking), this could result in an unwanted table locking "feature", what will slow down.

Edited because off comments only an VERY small part off the source code behind the trigger code is analysed, I will analyse the source code behind triggers more exhaustive to see if there are any performance implications and what the relative performance impact this may have.

Answer 2

Using a trigger will not affect the execution speed significantly -- in the end, the database system is executing the same operations.

But if you are sure that every insert mandates the updates then triggers are a great way to ensure database integrity, so by all means do it.