How to track the creation/modification of rows in the database?

Question

MySQL

Good afternoon.

There is a large dump in CSV - about 1.5 million lines. Once a day, it is parsed and written to a table in the database.
There are 10-15 users who follow the lines according to a simple type criterion FieldXXX = valueYYY(each user has his own value, there can be several filters per user).

It is written line by line, for each line it is done mysqli_affected_rows() > 0, if yes, then a notification is sent to users who follow such a line.

The script works for about two hours, which is very bad. It is clear that the very first optimization would be to insert not row by row, but using multiple-row INSERT, but in this case the affected rows will not work.

What other options exist to optimize this process of inserting large dumps?

Answer 1

The easiest way to keep track of changes is a field that records the date the record was last modified. the engine knows such fields automatically.
the script that checks for changes compares this value with the time it was last run.

Answer 2

triggers for this