Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
On what storage engines? –  Raymond Nijland Aug 27 '13 at 20:53
    
@RaymondNijland innoDB –  All Aug 28 '13 at 7:28

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
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. –  Michael - sqlbot Oct 31 '13 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? –  Raymond Nijland Oct 31 '13 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 –  Raymond Nijland Dec 2 '13 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.

share|improve this answer
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.) –  ypercube Sep 5 '13 at 17:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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