sql - MySQL: how to keep track of changes in a view

Question

In my MySQL database, I have a massive view built from a very complex query (+5 million rows, 118 columns), which changes part of its content daily. Every day, I have to query this view and insert its whole content into an even bigger table, this operation can take from 3.5 to 5 hours.

I tried to export the data to CSV files in chunks and then use LOAD DATA INFILE to load the records in a faster fashion, but the export stage is painfully slow.

Is there a way to detect what changed in the view and only update those records in the bigger table? Any recommendations to deal with that amount of records stored in a view?

Thanks

question from:https://stackoverflow.com/questions/65924538/mysql-how-to-keep-track-of-changes-in-a-view

Answer 1

Strictly speaking, a view contains no records. Rather it presents a, well, view of the underlying tables.

Can you tell which rows changed in the underlying tables? Do any of those tables have columns defined like this?

changetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

If so, you may be able to generate a divergent view, containing the same columns as your existing view, hat only has the rows that changed since a certain time.