0

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?

2
  • 1-2 billion rows from a query? Do you really need to do that? Sep 3, 2020 at 16:39
  • yes those data go to edw and we cannot renormalize the original tables
    – Brend
    Sep 3, 2020 at 18:15

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.