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


with trigger

UPDATE table2 ... WHERE id=NEW.id;
UPDATE table3 ... WHERE id=NEW.id;
  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.

  • On what storage engines? Aug 27, 2013 at 20:53
  • @RaymondNijland innoDB
    – Googlebot
    Aug 28, 2013 at 7:28

2 Answers 2


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.

  • 2
    This analysis is wholly incorrect. You talk about reading from memory -- well, this is the place where the trigger gets loaded into memory, to be used later. The only meaningful time check_n_load gets called is when the table is opened and added to the table definition cache. Triggers are invoked from sql_delete.cc, sql_insert.cc, sql_update.cc and the server absolutely is not opening and reading the file with each invocation. Obviously, always, and everywhere, queries without triggers are faster that with, that isn't the point; triggers are not problematic as you theorize. Oct 31, 2013 at 3:02
  • @Michael - sqlbot your are right indeed based on thoery.. i may need to analyse this better to make an statement on how this impacts the performance only an small part off the trigger code is analysed.. not sure if trigger will be added to the table cache can you proof within the source code off MySQL version5.5.32 to proof your thoery of trigger caching? Oct 31, 2013 at 7:18
  • @Michael - sqlbot ive analysed some more code you are completly right next time i need to analyse more parts off the code before i make an statement.. triggers are indeed cached within sp_cache (wierd namespace totally overlooked this one) from function add_tables_and_routines_for_triggers from sql_trigger.cc .. and check_n_load indeed is called from the function open_table in base.cc Dec 2, 2013 at 20:12

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.

  • 1
    Triggers in MySQL execute row by row though. And a single statement that affects many rows is not the same operation as many statements that affect one row at a time (they have the same result, yes, but different side effects, like in logging and execution plan.) Sep 5, 2013 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.