Mysql – Executing delayed inserts with MySQL event scheduler

MySQLmysql-eventtrigger

A table that I'm working on, has two triggers; one is after insert and the other is before delete. These triggers carry out calculations and copy over some rows to a backup table. This backup table is to be used in the application for showing data, so data can be delayed, but this table should be available in the db for JOINs etc.

Now if a large quantity of insert's/delete's are carried out on the primary table, the triggers slow the entire application down. I've thought about creating a sql-queue table, and using the triggers to dump any SQL query that needs to run, in the backup table.

Then, I'd create an MySQL event that checks this sql-queue table every 'x' seconds for queries to execute. At least in this manner, the user using the application is not blocked waiting for the triggers to execute and insert data into the backup table.

Is this approach recommended?

Best Answer

I think you need to start over in the processing of your data. I don't think TRIGGERs are an efficient way to achieve the goals.

Here's a thought:

Put information to be INSERTED/DELETED into a couple of tables. Periodically, batch process the entire tables to do the calculations and do the inserts and deletes. Batch processing is a lot faster than one-row-at-a-time.

Furthermore, I would set up pairs of such tables. Various clients would be inserting into one set, while another job is doing the processing described above. When finished, the processed tables would be cleared, then swapped with the tables being inserted into.

This discusses the details for doing the "inserts", together with associated extra work ('normalization', in the blog). Deletes would work similarly.

Related Question