3

What are the advantages/disadvantages of using MySQL triggers to keep data synced between one table and another and even update aggregate/report tables as data changes?

By aggregate/report tables I mean tables that summarise data that exists in other tables such as:

tbl_user_location:
1 Mike  New York
2 John  London
3 Emily Paris
4 Jack  New York

tbl_summary:
New York 2
London 1
Paris 1

As data changes real-time in tbl_user_location the summary is updated.

What are alternatives to this approach?

Emanuel Lainas
  • 152
  • 2
  • 11

1 Answers1

6

In advantage: SQL triggers provide an alternative way to run scheduled tasks. triggers are invoked automatically before or after a change is made to the data in tables.

In disadvantage: However, SQL triggers are invoked and executed invisibly from client-applications therefore it is difficult to figure out what happen in the database layer. This may increase the overhead of the database server.

If you are talking about high velocity data don't even think about triggers. I suggest you to go with stream processing technique. You can you mysql bin-logs to capture events and add to the stream and then process. Simply triggers is not a good idea for real time data aggregation.

Try something like below, you might need to do some searching on these Ex: (mysql binlogs) maxwell->kafka->spark (processed data)

Malinga
  • 505
  • 3
  • 14
  • Thanks! Apparently Maxwell or something similar is what I was looking for. It seems there's a bunch of [MySQL changes data stream tools](https://github.com/wushujames/mysql-cdc-projects/wiki) out there. – Emanuel Lainas Jul 02 '16 at 22:41
  • By "If you are talking about high velocity data don't even think about triggers" are you referring to the fact that triggers are synchronous, in the sense that their processing "blocks" the initial query? – Emanuel Lainas Jul 02 '16 at 22:43
  • 1
    Hey, high velocity mean, when number of events per second is high, triggers get triggered all the time putting high load on server. I said that because I have used it and once the velocity got high it failed – Malinga Jul 03 '16 at 03:55