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 have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I update this column by an extra query:

UPDATE user_table SET comments = comments + 1 WHERE user_id='x'

Now I want to introduce Triggers to my database instead of running the second query. Two Triggers for each column: one DELETE and one INSERT. Then, I will have 30 - 40 Triggers!!!

Benchmarks show that using Triggers is about 50% faster than executing a second query. Apparently, it is reasonable to use the modern features of mysql. However, the reason that I am asking this question is that many programmers are strongly against Triggers, as it makes coding vague. Moreover, adding lots of Triggers may make Information_Schema messy.

Am I on the right track?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

The major problems with triggers are:

a) They are difficult to debug

b) Trigger cascades can be fatal to your database - take for example if you have an after update trigger in one table which updates another table, which also has an after update trigger, then you will run into lots of issues

A few questions:

a) How often are the "computed" summaries used within your application?

b) How often are the "computed" summaries required to be updated?

c) Would you consider creating a "reporting" table which is refreshed on a regular basis with the major "computations" that you need?

share|improve this answer
    
Thanks for explicit answer. (a) they will be displayed as statistics, e.g. for a user profile (regularly then); (b) this varies from case to case: consider many votes (very often), and posting comment (not many times). (c) Do you mean to keep all columns which will be updated in ONE table? You try to keep the database far from messy action, by limiting trigger actions to one single table only? –  All Apr 6 '12 at 13:58
    
You are welcome. Why I asked that is other than using triggers you could consider using SQL statements to compute the statistics on the fly (this can be very fast where the tables are normalized), then computing the more expensive statistics in a single table at regular intervals say, the end of each day, or maybe every 4-6 hours if required more often. I would start with this approach then go for triggers when the performance becomes a problem. –  ssmusoke Apr 6 '12 at 14:08
add comment

The reason many are against the use of triggers really has to do with the lack of control introduced by the need for a second query.

The second query needed for recording information is subjected to the MySQL environment without you having the ability to control storage engine specific aspects.

For example, I once tried to help troubleshoot a MySQL event whose root cause simply boiled down to the storage engine of the table being recorded. From my empirical analysis of that problem, I have sworn off recording audit trail info into InnoDB tables from within a trigger.

I have written earlier posts about the good, the bad, and the ugly of Triggers:

If you must have many triggers, my strong recommendation would be for you to use a combination of the BLACKHOLE storage engine and MySQL Replication.

EXAMPLE

Let's say you have a database that you want to record comments for a user. Have a table called audit_user with the userid of the user and a comments column:

CREATE DATABASE auditinfo;
USE auditinfo
CREATE TABLE audit_user
(
    userid INT NOT NULL,
    comments INT NOT NULL DEFAULT 1,
    PRIMARY KEY (userid)
) ENGINE=BLACKHOLE;

You next setup a trigger that performs this query

INSERT INTO auditinfo.audit_user (userid) VALUES (myuserid)
ON DUPLICATE KEY UPDATE comments = comments + 1;

OK great, but why have the audit_user table use the BLACKHOLE storage engine when nothing is stored in the table? Here is where MySQL Replication comes into play.

Setup a Slave whose sole purpose in life to to catch all auto trail data. Have the Slave replicate from the Master with the replicate-do-db=auditinfo option. Create the auditinfo database and audit_user table in the Slave as follows:

CREATE DATABASE auditinfo;
USE auditinfo
CREATE TABLE audit_user
(
    userid INT NOT NULL,
    comments INT NOT NULL DEFAULT 1,
    PRIMARY KEY (userid)
) ENGINE=MyISAM;

The binary logs of the Master has the command INSERT INTO auditinfo.audit_user (userid) VALUES (myuserid) ON DUPLICATE KEY UPDATE comments = comments + 1; recorded. That INSERT is transmitted to the Slave's relay logs. Replication causes that INSERt query to be executed.

So the question remains: What is the benefit of setting this up?

When it comes strictly to recording audit info, the Master does not have any heavy write I/O to slow it down. It simply records the SQL need to do the audit. That SQL is sent over to another machine (the Slave) for actual recording. Naturally, the Master is able to handle a lot more triggers when you setup auditing in this manner. If you do not use this combination of BLACHOLE/MySQL Replication, every query doing an INSERT will slow itself down and seeing hundreds of such queries with unveil significantly poor DB performance.

share|improve this answer
    
very interesting play with Blackhole engine! I've never thought of this Master/Slave combination. –  All Apr 6 '12 at 17:20
add comment

There are several options for summary fields such as you have.

  • Calculate when needed. This is the preferred starting point until usage indicates you need to de-normalize the value.
  • Use triggers to maintain the de-normalized value in the appropriate table. This trades off the overhead of maintaining the value versus the cost of retrieving it as needed.
  • Use a batch process to update de-normalized values on a periodic basis (when system load is low). This trades off accuracy against resource usage during peak times.

You will note I didn't include your option of a second update to maintain the value. This is because that is prone to being missed in some cases causing the data to be permanently inaccurate. The solution for this is a batch audit/update to detect and correct inaccuracies. This is needed for the initial value setting when using triggers, and should be kept around in case the triggers are disabled at any time.

As has been noted, you don't want the de-normalized values to have triggers on them. These are the cases I use triggers:

  • Maintain audit fields or tables. (Create/Update dates and users. Before image snapshots in an audit table.)
  • Perform cross field table validations.
  • Validate state transformations on state fields.
  • Maintain de-normalized fields. (Added after validation that they will increase performance. Most commonly when the value changes far less often than it is retrieved.)
share|improve this answer
    
I've batch process strategy for another case, but here it is not good at all. Consider 10 users have posted in the past 24 hours. I need to perform COUNT for total users (say 1000) to update summary column for 10 user activity. –  All Apr 6 '12 at 15:39
    
Like the other solutions the batch strategy has trade offs. One of these is recalculating unchanged values. However, this should be scheduled for low utilization hours, so the impact should be minimal. It may be possible to utilize audit data to limit the number of recalulations. –  BillThor Apr 6 '12 at 15:58
add comment

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.