That’s a good point. For my particular use case there will probably only be in the hundreds of records max so probably not that big a deal. Do you have any suggestions for an alternative? Two other thoughts I’ve had is:
SELECT CONCAT(CAST(MAX(modified) AS CHAR), COUNT(*)) FROM post
If a record is added the max modified will change (as well as the count), if a record is deleted the count will change, if a record is added and deleted at nearly the same time the count will stay the same but the max modified will change. Not sure how the performance of this would compare to using AVG.
the second is quite nice, but I would go with the first one: create table last_table_modyfications (table_name varchar primary key, last_modyfication datetime) and put in that table record for every table that should be tracked, like (‘post’, now()) and then update that table with db triggers on after insert,update,delete. this will be fast (it is simple hash table), relayable and as most db engines have at least sql triggers - should work everywhere.
In mysql you could create such table as HEAP table so it will be stored in memory (but you should then handle missing table record in triggers because on server restart all data will be gone)!
CAST(MAX(id) AS TEXT) || CAST(COUNT(*) AS TEXT) || CAST(MAX(created_on) AS TEXT) || CAST(MAX(modified_on) AS TEXT) AS "dependency"
CONCAT(MAX(id), COUNT(*), MAX(created_on), MAX(modified_on)) AS `dependency`