CODEWITHSUNDEEP
mysqltriggers
shadyyx
shadyyx

Reputation: 16055

Is it better to call two INSERT/UPDATE queries or to call one with trigger?

I have a question regarding MySQL triggers as Google gave me no usable results.

Let's imagine situation I have to INSERT or UPDATE a row into MySQL table while this procedure is also logged/audited into different table, e.g. with two queries for INSERT I would do:

INSERT INTO `cars` (`brand`, `type`, `year`) VALUES ('Audi', 'A6', 2013);

then receive the new row's ID and perform insert into the log table:

INSERT INTO `cars_log` (`car_id`, `brand`, `type`, `year`, `action`, `date`) VALUES (1954, 'Audi', 'A6', 2013, 'insert', NOW());

Instead of this solution I could also go with only one INSERT/UPDATE query (into cars) and define triggers listening for AFTER INSERT and AFTER UPDATE that would insert my log data automatically.

The question is: From the performance point of view what is better solution?

Additional questions:

Upvotes: 1

Views: 961

Answers (1)

Bud Damyanov
Bud Damyanov

Reputation: 31889

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.

Put in other words - triggers are the way to go for implementing any kind of tracking of database changes. However, you need to be aware of what happens under the hood when you use triggers.

According to MySQL Stored Procedure Programming, page 256 under the head "Trigger Overhead" says the following:

It is important to remember that, by necessity, triggers add overhead to the DML statement to which they apply. The actual amount of overhead will depend upon the nature of the trigger, but --- as all MySQL triggers execute FOR EACH ROW --- the overhead can rapidly accumulate for statements that process large numbers of rows. You should therefore avoid placing any expensive SQL statements or procedural code in triggers.

An expanded explanation of trigger overhead is given on pages 529-531. The concluding point from that section states the following:

Since the trigger code will execute once for every row affected by a DML statement, the trigger can easily become the most significant factor in DML performance. Code inside the trigger body needs to be as lightweight as possible and -- in particular -- any SQL statements in the trigger should be supported by indexes whenever possible.

Another thing when using triggers - when it comes to audit logging, please be aware of what you log data into. I say this because should you choose to log to a MyISAM table, each INSERT into a MyISAM table produces a full table lock during the INSERT.

This can become a serious bottleneck in a high-traffic, high-transaction environment. Additionally, if the trigger is against an InnoDB table and you log changes in MyISAM from within the trigger, this will secretly disabled ACID compliance (i.e., reduce block transactions to auto-commit behavior), which cannot be rolled back.

Upvotes: 1

Related Questions