Mysql – Do triggers affect DB performance

MySQLmysql-5.1performancetrigger

I have a database with 250 small and medium sized tables. These tables are related with both MyISAM and InnoDB storage engines. There are multiple triggers that have been created on different tables. These triggers are getting executed on Insert, Update and Delete events. My system has started to sometimes perform bad. On check, I found that there are a huge number of DB connection threads running.

Now my question is:

  • Do these triggers affect my system performance? If yes, Is there
    any better alternative for these triggers?

Best Answer

A trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.
There are three different strategies to ensure execution of data integrity code when a window on data application (WoD) attempts to execute a transaction:

  • Declarative : Table constraint implementation, using primary & unique keys, check constraints, foreign keys.
  • Triggered procedural: Using triggers.
  • Embedded procedural: Implementing data integrity logic in applications business layer.
    This is probably by far the most used procedural strategy for implementing DI code in WoD applications.

You can state only two types of table constraints declaratively:
uniquely identifying attributes (keys) and subset requirements referencing back to the same table, in which case a subset requirement is a table constraint (foreign key to the same table).
Implementing all other types of table constraints (like a multi-tuple constraints or transition constraints) requires you to develop procedural DI code.

So the answer is No, not without a heavy cost to implement the logic in application level.

As I know, triggers will affect performance in insert, update and delete operations. but doesn't affect read performance.
To tune your RDBMS first see what are the DB connection threads doing, and what are the most costly waits.