I have about 100 MySQL tables and many SQL queries which take about 1-3 hours to run each and they return about 1 to 2 billions of records each.
However, every couple of minutes some records are inserted and deleted from any of the 100 tables.
Is there a way to not execute these SQL queries on the whole database and instead know the entries that were deleted? E.g. suppose one entry was deleted
from one of the 100 tables, I should be able to remove the corresponding entry from my previous query result.
My idea:
For inserts I can look at the time the new record was inserted and insert the latest.
For deletes I should be able to find out which entry was deleted from the logs (debezium). But how do I delete the record from my resulting query?
Best Answer
Create for every table an AFTER DELETE TRIGGER, so you are sure you catch every deleted entry and can remove them from your result set.
The same goes of course for the inserts, then you can also use a TRIGGER to add rows to your result set.
Your query is probably slow and you hope that you don't need to run it again, when you simply add and remove all new entries since the query last ran.